Excel Macro?

[CRHamilton]

I am trying to setup a macro in Excel 2k that will initiate the SaveAs function, but will stop and prompt for a file name. Trying to use the "Record a Macro" in excel, then go into the VBS editor, but nothing seems to work. Can anyone point me in the right direction? Thnx in advance for any assistance!

 

[jsaliers]

Use the ShowSave Command.

object.ShowSave

This should open the SaveAs dialog box.

Hope this helps!

Jon

 

[CRHamilton]

Hmmm... guess I am not getting it... Here is the script from the recording:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 12/11/2003 by CRHamilton
'
' Keyboard Shortcut: Ctrl+z
'
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\crhamilton\Desktop\Book1.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub

Where would I put the object.ShowSave?

Thnx!

 

[jsaliers]

This is how it should look:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 12/11/2003 by CRHamilton
'
' Keyboard Shortcut: Ctrl+z
'
ActiveWorkbook.ShowSave

End Sub

Hope this helps!

Jon

 

[CRHamilton]

I get an Run-time error '438'

Object doesn't support this property or method.

Any idea what I'm doing wrong? (cut and pasted it into the editor, replacing what was already there.)

 

[jsaliers]

Yeah, my fault. ShowSave is a CommonDialog method. That is, you have to have CommonDialog controls. Then you would have the following line of code:

CommonDialog1.ShowSave

Sorry about the confusion.

Jon

 

[SkipVought]

Hi,

Use the GetOpenFilename method to get the name of the file to save as...

fileToOpen = Application _
    .GetOpenFilename("Excel Files (*.xls), *.xls")
If fileToOpen <> False Then
    MsgBox &quot;Open &quot; & fileToOpen
End If

Skip,

http://www.TheOfficeExperts.com

 

[CRHamilton]

Hmmm... this doesn't prompt me to save the file, but rather gives me the Open box to selct the file to open... am I missing something here?

 

[SkipVought]

Once you have the user-selected filename, you YOU saveas in your code

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com