I have a macro that reads in a file. The file's name
changes every day when the date is appended. How can I
change my macro so that instead of always calling
MyFile.xls it prompts the user to type in the filename? I
can't make it call in MyFile_with_today's_date.xls because
sometimes I have to run the macro on old files.
Thanks

Steve,

Have a look at the GetOpenFilename method. E.g.,

Dim FName As Variant
FName = Application.GetOpenFilename("Excel Files,*.xls")
If FName = False Then
MsgBox "You didn't choose a file"
Else
MsgBox "You chose file: " & FName
End If

Note that GetOpenFilename doesn't actually open the file. It displays a
standard File Open dialog and returns the name of the file selected by the
user.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com


"Steve" wrote in message
...
I have a macro that reads in a file. The file's name
changes every day when the date is appended. How can I
change my macro so that instead of always calling
MyFile.xls it prompts the user to type in the filename? I
can't make it call in MyFile_with_today's_date.xls because
sometimes I have to run the macro on old files.
Thanks

Steve,

Here's some code I got from this forum

Sub GetFile()
Dim FN As String ' for our selected file's name
Dim MyFilter As String ' for the window filter
Dim MyCaption As String ' for the window caption

MyCaption = "Please select a file..."
MyFilter = "Excel (*.xls),*.xls"
'get user to select a file
FN = Application.GetOpenFilename(MyFilter, , MyCaption)
'test for cancelled
If FN = "False" Then
MsgBox "Cancelled"
Else
' do something - for this demo
Dim wb As Workbook
Set wb = Workbooks.Open(FN)
End If
End Sub

--
sb
"Steve" wrote in message
...
I have a macro that reads in a file. The file's name
changes every day when the date is appended. How can I
change my macro so that instead of always calling
MyFile.xls it prompts the user to type in the filename? I
can't make it call in MyFile_with_today's_date.xls because
sometimes I have to run the macro on old files.
Thanks

Dim fName as String
Chdrive "C"
Chdir "C:\Myfolder"
fname = Application.GetOpenFileName()

If ucase(fname) < "FALSE" then
Workbooks.Open fname

End if

See excel vba help on GetOpenFileName
for information on arguments you may use.

--
Regards,
Tom Ogilvy


Steve wrote in message
...
I have a macro that reads in a file. The file's name
changes every day when the date is appended. How can I
change my macro so that instead of always calling
MyFile.xls it prompts the user to type in the filename? I
can't make it call in MyFile_with_today's_date.xls because
sometimes I have to run the macro on old files.
Thanks