Get File location
 

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!

Get File location
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jeff" wrote in message ...
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!

Get File location
 

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

Jeff wrote:

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!

--

Dave Peterson