Solved

Macro to Save xl file as a new file

Posted on 2003-10-23
9
371 Views
Last Modified: 2010-04-17
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
0
Comment
Question by:Shakhawat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
9 Comments
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 9609780
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

0
 
LVL 5

Accepted Solution

by:
jrocnuck earned 100 total points
ID: 9609960
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


0
 

Author Comment

by:Shakhawat
ID: 9610165
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 9610451
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

0
 
LVL 5

Expert Comment

by:jrocnuck
ID: 9610561
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.
0
 

Author Comment

by:Shakhawat
ID: 9621209
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
numbers ascending pyramid 101 264
Is online banking safe? 11 125
Mutiple Folder view (5 Folder view) 3 81
How do I buy BitCoin? 4 28
A short article about problems I had with the new location API and permissions in Marshmallow
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question