Solved

Macro to Save xl file as a new file

Posted on 2003-10-23
9
365 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
  • 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

821 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