Macro to Save xl file as a new file

Shakhawat
Shakhawat used Ask the Experts™
on
Hi,

I was trying to save my xl file using macro through opening save dialog box. I could not do it. Actually what I need?
If the macro is run then a save dialog box will be opened. A file name will be provided in the dialog box and so on..... If anybody can assist me that would be great.

With thanks

Shakhawat
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The below macro will open an InputBox and ask for the filename.
Sub Macro2()
    Dim NewFileName As String
    Dim DefaultName as string
    DefaultName = "SomeFileName.xls" '<set the default filename that it would be saved as
    NewFileName = InputBox("Enter File Name To Be Saved as", "Save As", DefaultName)'< ask for the information with captions and the defaults
    If Right$(NewFileName, 4) <> ".xls" Then '< make sure the extension is there
        NewFileName = "C:\FullyQualifiedPath\" & NewFileName & ".xls"
    Else
        NewFileName = "C:\FullyQualifiedPath\" & NewFileName
    End If
    ActiveWorkbook.SaveAs FileName:=NewFileName, FileFormat _
        :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False
End Sub

Let me know if more is required
dragontooth

Commented:
if you want to use the standard save as dialog box

use this function

strFileToSaveWithPath = Application.GetSaveAsFilename( fileFilter:="My Files (*.ext), *.ext")
  ActiveWorkbook.SaveAs FileName:=strFileToSaveWithPath , FileFormat _
        :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False


Author

Commented:
Hi dragontooth,
I tried with your solution. The macro also moves with the saved file. Bet I need to keep the macro with the initial workbook. Can you please assist me.

With thanks

Shakhawat

For Jrocnuck,

Would you please write total code. There may be some mistake in my function.

With thanks

Shakhawat
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

You could save it as a .csv file but that would only save the active worksheet. You should have the macro in the original workbook also, I could be wrong but I don't think that you can choose if the macros will be saved with the workbook or not (the saved as one).

dragontooth

Commented:
I would just open the file you want to have the macro in, then use the Tools -> Macro -> Record feature.

From there I would Go to the pull down menu and select File->SaveAs and then put in the name of the file and hit ok.

Then stop the macro recording..

Then from Tools->Macro you can edit the macro and add in the

strFileToSaveWithPath = Application.GetSaveAsFilename( fileFilter:="My Files (*.ext), *.ext" )  

part to have it bring up the common dialog for saving.
you can also delete any lines that may result such as ChDir ...

From the macro editor, you could double click on GetSaveAsFilename and select F1 to get the help on the syntax for that function, if you have Excel VBA help installed.

Author

Commented:
Hi,
I am sparing my 100 points for Jrocnunk. Althouh, that is not full code but that helped me to identify the solution. Thanks to Jrocnuck.

Shakhawat

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial