Get File location

J

Jeff

Hi,

I want to run a macro and then be able to choose files on my computer, then
save the chosen file location as a variable. Is there anyway to do this?

Sub Macro()
Prompt: Choose filename
Variable = Filename
End sub

Thanks for your help!
 
R

Ron de Bruin

Hi Jeff

You can use GetOpenFilename for this

Sub test()
Dim FName As Variant
Dim wb As Workbook
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir

MyPath = ThisWorkbook.Path
ChDrive MyPath
ChDir MyPath

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
If FName <> False Then
Set wb = Workbooks.Open(FName)
MsgBox "your code"
wb.Close
End If

ChDrive SaveDriveDir
ChDir SaveDriveDir

End Sub
 
D

Dave Peterson

So you can open the file later?

Option Explicit
sub testme()
dim myFileName as variant
dim wkbk as workbook

myfilename = application.getopenfilename("Excel Files, *.xls")
if myfilename = false then
exit sub 'user hit cancel
end if

msgbox myFileName 'just to prove that you got it

'to open the file now that you have it:
set wkbk = workbooks.open(filename:=myfilename)

end sub
 
Top