Solved

Macro to Save xl file as a new file

Posted on 2003-10-23
9
382 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Progress

626 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