Another simple VBA question
Posted by Adam S. on November 21, 2001 2:43 PM
Hi all,
I've been scraping around in the online help but I'm not making much progress.
In a current macro (at the last bit) I have something like the following:
ChDir "C:\"
Workbooks.Open Filename:="C:\Input_File.xls"
This opens the file: Input_File.xls off the the listed path (this is a simplified example). That was cool enough prior to today, but now things have changed a bit.
The filename will now change on a weekly basis (to something like "Input_File 11-10 - 11-17.xls").
I really have 2 related questions:
Is it possible to have the macro prompt me to choose the file from a directory (and then open it) instead of always attempting to open a hard-coded filename?
Better yet, would it be possible to have the macro search in the specified directory for any file that begins with "Input_File"? -(this would be the only file in that directory that would begin in that specific way)
Any help will be appreciated
Thanks all!
Adam
Posted by Jo on November 21, 2001 3:28 PM
Elaborately it more clearly with the full
macro...
Posted by Gary Hewitt-Long on November 21, 2001 3:50 PM
If you record the name of the file on a worksheet somewhere and keep this updated with the latest file name (this can be incorporated into a macro to update the name) then change the last part of the code to :
Input File = Range("[Workbook1.xls]Sheet1!A1").Value
ChDir "C:\"
Workbooks.Open Filename:=Input File
If you are using a macro to update cell A1 with the latest file name then the reference should always be up to date.
Regards,
Gary Hewitt-Long
Posted by Juan Pablo on November 21, 2001 4:16 PM
You CAN do that, just have to use the GetOpenFilename method, something like
Sub OpenIt()
FileToopen = Application _
.GetOpenFilename("Input Files, *.xls")
If FileToopen <> False Then
If InStr(1, FileToopen, "Input_File", 1) = 0 Then
MsgBox "File must be Input_File*.xls"
Else
MsgBox "Open " & FileToopen
End If
End If
End Sub
Unfortunatelly VB didn't accept what i thought was a valid filter (Input_File*.xls), which leads me to think that it only accepts filters in the form of "*.???" and nothing more... but this sub checks the name, and if it has Input_File in it, then it accepts it.
Juan Pablo
Posted by Ian on November 22, 2001 1:36 PM
Using one of the .Dialogs command (see below) in your macro
allows you to access Excel's File Open dialog
Sub xxxx()
Application.Dialogs(xlDialogOpen).Show
Target=ActiveWorkbook.Name
'Opens the your source worksheet
'and calls it "Target"
'Perform your actions
Windows(Target).Close
'Close the "Target" worksheet
End Sub
