Assign Path File into a variable in VBA

Question

I am almost done with this small part of my program. I am trying to assign a path file to a variable so I can use that variable in the second part, I have tried the following. I appreciate any help. Thank you very much in advanced

FIRST PART

 Public strPathAndFile As String  

 Sub CommandButton2_Click()

 Dim fd As FileDialog
 Dim strShortName As String
 Dim strInitialDir As String
 Dim wb As Workbook

 strInitialDir = CurDir   'Save current directory if required

 Set fd = Application.FileDialog(msoFileDialogFilePicker)

 With fd
     .InitialFileName = CurDir & "\"     'Startup folder (Optional line of code)
     .AllowMultiSelect = False
     .Filters.Clear
     .Filters.Add "All Excel Files", "*.xls;*.xlsm;*.xlsx"     'Display excel files only
     If .Show = False Then
         MsgBox "User cancelled without selecting. Processing terminated."
         ChDir (strInitialDir)  'Change back to Initial directory if user cancels
         Exit Sub
     End If
     'Next line strPathAndFile contains path and filename of selected file ' < ======= FROM HERE
     strPathAndFile = .SelectedItems(1)
 End With

 'The following populates a cell with just the filename (without the path)
 strShortName = Right(strPathAndFile, Len(strPathAndFile) - _
         InStrRev(strPathAndFile, "\"))

 ' Store the path file in this cell
 ThisWorkbook.Worksheets("Sheet1").Range("F12") = strShortName

End Sub

SECOND PART - Here I want to use the strPathAndFile variable.

 Sub CommandButton3_Click()
' Read a file and find the title name
' Variables
Dim WhatToFind As Variant
Dim wbFind As Workbook
Dim foundRange As Range

' Assign file path to a variable
Set wbFind = Workbooks.Open(strPathAndFile) ' <========  TO HERE

' Do not show actions on screen
Application.ScreenUpdating = False

' Array of valuse to be found
WhatToFind = Array("Dog", "House", "Table")

With wbFind.Sheets("general_report")
    Set foundRange = .Cells.Find(What:="status", After:=.Cells(1, 1), _
    LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
End With

If Not foundRange Is Nothing Then foundRange.Activate

' close the source workbook without saving any changes
wbFind.Close False

' free memory
Set wbFind = Nothing

' Show actions on screen
Application.ScreenUpdating = False
End Sub

msdn.microsoft.com/en-us/library/zb8fth98.aspx yields Public Const strPathAndFile As String = "C:\yourpath" or is the problem that you want to dynamically assign the path based on current workbook path? — xQbert
– xQbert, Commented Jun 16, 2014 at 17:29
Yes Sr. I am trying to select a file and then use that path file and save it into a variable to be used by another part of the program. But I do not want to hard code the path, I need to do it dynamically. Thank you very much, I appreciate your kind help. — Bart g
– Bart g, Commented Jun 16, 2014 at 17:30

Ravi Yenugu · Accepted Answer · 2014-06-16 17:35:44Z

2

You are already saving the path in the cell,Worksheets("Sheet1").Range("F12"), Cant you just do

 ' Store the path file in this cell
 ThisWorkbook.Worksheets("Sheet1").Range("F12") = strPathAndFile

then use

 Dim fpath As String
 fpath = Worksheets("Sheet1").Range("F12").Value
 Set oWB = Application.Workbooks.Open(fpath)

answered Jun 16, 2014 at 17:35

Ravi Yenugu

3,8885 gold badges43 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

xQbert · Accepted Answer · 2014-06-16 17:37:14Z

1

Public strFileAndPath As String

Sub getpath()
    Dim retvalue As String
    retvalue = ThisWorkbook.FullName
    strFileAndPath = retvalue
End Sub

you just need to set the strfileandpath global variable which can be done via strFileAndPath = thisworkbook.fullname

The other stuff above was me testing it.

answered Jun 16, 2014 at 17:37

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Collectives™ on Stack Overflow

Assign Path File into a variable in VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related