browse to open and save as in Excel vbs

S

seven

I am using Excel2003, now I have a question during my work. I want to use
excel vbs to solve a problem, so I used the record mode to get a macro, the
question is:
How can I change the vbs code so that when I run the marco, it popup some
window message to let me to browse and choose the xls (for I have to deal
with many different .xls in the same way) I need, at the end of code, I want
a same window popup to ask me to save as. Can you provide me with an
example? Thanks very much.
 
P

papou

Hello
See help in VBA on GetOpenFilename and GetSaveAsFilename methods.

Dim FileToOpen
FileToOpen = Application.GetOpenFilename("Excel Files, *.xls", , "File ?")
'Cancelled
If FileToOpen = False Then Exit Sub
MsgBox FileToOpen

Dim FileToSave
FileToSave = Application.GetSaveAsFilename(, "Excel Files, *.xls", , "Save
as:")
If FileToSave = False Then Exit Sub
MsgBox FileToSave

HTH
Cordially
Pascal
 
S

seven

Hello, papou,
I tried your code, the open function works, however save as seems can not
get the results, eg, i open a book1 and then just save it as book2 without
any other steps, then no error information, however i can not find book2, So
would you please help more? Thank very much for your help!
 
P

papou

seven

Dim FileToOpen, FileToSave
FileToOpen = Application.GetOpenFilename("Excel Files, *.xls", , "File ?")
'Cancelled
If FileToOpen = False Then Exit Sub
Workbooks.Open FileToOpen
FileToSave = Application.GetSaveAsFilename(, "Excel Files, *.xls", , "Save
as:")
'Cancelled
If FileToSave = False Then
Msgbox "You cancelled the save operation",VbInformation
Exit Sub
Else
ActiveWorkbook.SaveAs FileToSave
End If

HTH
Cordially
Pascal
 
Top