Solved

Macro to Save xl file as a new file

Posted on 2003-10-23
9
354 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

RIA (Rich Internet Application) tools are interactive internet applications which have many of the characteristics of desktop applications. The RIA tools typically deliver output either by the way of a site-specific browser or via browser plug-in. T…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

930 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now