#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 921
Default 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!


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,123
Default 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!




  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I email amacro? leo Excel Worksheet Functions 24 August 9th 06 02:47 PM
Location of XLB file? Doug Kanter Excel Discussion (Misc queries) 5 February 28th 06 12:40 AM
Links picking up values from an older version of linked file Cate Links and Linking in Excel 4 October 20th 05 01:53 PM
print file path and location in a footer be New Users to Excel 2 June 15th 05 03:56 PM
location of backup file Vikas Excel Discussion (Misc queries) 1 December 9th 04 07:27 AM


All times are GMT +1. The time now is 07:32 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"