Save as File Using Visual Basic Excel Macro

Your rating: None Average: 4 (40 votes)

I have written this macro a while back and thought it would be of some benefit for some of you.
I've created a button on an excel sheet that when clicked will fire an excel macro to open the well familiar windows "Browse Save as" box and allow the user to save the file to his/her desired location.
The macro limits the "Save as type" to a .txt file format.

save as macro example

Here is the code:

Private Sub CommandButton1_Click()

Application.DisplayAlerts = False

' Save file name and path into a variable
   template_file = ActiveWorkbook.FullName
 
' Default directory would be c:\temp.  Users however will have the ability to change where to save the file if need be.
' Notice that i'm only allowing the save as option to be of .txt format.
' I'm also attaching the current date to the file name.
   fileSaveName = Application.GetSaveAsFilename( _
    InitialFileName:="C:\temp\filename_" + VBA.Strings.Format(Now, "mmddyyyy") + ".txt", _
    fileFilter:="Text Files (*.txt), *.txt")
   
    If fileSaveName = False Then
        Exit Sub
    End If

' Save file as .txt TAB delimited
   ActiveWorkbook.SaveAs Filename:= _
        fileSaveName, FileFormat:=xlText, _
        CreateBackup:=False

   
    file_name_saved = ActiveWorkbook.FullName
    MsgBox "Your Acct Rec upload file has been successfully created at: " & vbCr & vbCr & file_name_saved
   
   
' Go back to excel format after TAB delimited file has been created and saved
   ActiveWorkbook.SaveAs Filename:= _
        template_file, FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False

     Application.DisplayAlerts = True

End Sub

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
savio's picture
savio (not verified)
Thank very much, But can you

Thank very much,

But can you please explain how to create a button for it?

Regards

CompShack's picture
User offline. Last seen 5 days 8 hours ago. Offline
Joined: 12/09/2007
Posts: 167
Here is how you do it

Sure Savio here is how your create the button.
Navigate to View > Toolbars and make sure your "Drawing" has a check mark beside it.

The drawing bar should be apparent at the bottom of your excel sheet. Choose the rectangulare shape and draw your box.

Right click on the rectangle and choose “Add Text”. If you would like to format the button to have a different color for example, then right click again and choose “Format AutoShape…”. Now, you need to assign the macro to your button so the code fires when it is clicked. To do that, right click again and choose “Assign Macro…”

That it you are all done, let me know how it goes :)

n/a
Guest's picture
Guest (not verified)
Number error

Thanks for your code.

But i had one big litle problem... the decimal numbers.
I need to format the number to "#.###,00", i already put the numbers like that, but when i hit the run they turn "#,###.00".
If i save it manual, in excel, it's all ok.

can anybody help me, please

CompShack's picture
User offline. Last seen 5 days 8 hours ago. Offline
Joined: 12/09/2007
Posts: 167
NumberFormat = "#.###,00"

I'm no VB expert, I just do it when I have to but you can try adding the code below to the beginning of your code.

Worksheets("your_sheet_name").Columns("your_column"). NumberFormat = "#.###,00"

Please let us know how you accomplish this if the above code doesn't work.

I have to ask though, why do you want the number to be formated as such "#.###,00"? I find it kind of strange to format a number as such.

Thanks!

n/a
luca's picture
luca (not verified)
Re: Save as File Using Visual Basic Excel Macro

hello.
i have a pretty complicate question (at least for me ;))
i'm working on a database in excel.
i'm trying to create a commandbutton that allow me to upload a file from a folder in my computer (using the window "Browse..") and to save it in a different location but with a different name (in the specific the name must be the text written in a specific excel cell).
somebody can help me?

thanks a lot
Luca

iris's picture
iris (not verified)
What if I want the enduser to save

the new workbook with a new name of his choice and as an xls document... exactly the same as the The Workbook I was saving from...

thank you

iris

Mike's picture
Mike (not verified)
Re: Save as File Using Visual Basic Excel Macro

Hello there,

How do I write to add another "text" format? Like .xls?
I've managed to change the format but not add one!

fileFilter:="Text Files (*.txt), *.txt")

Thanks in advance!

Lepa's picture
User offline. Last seen 2 weeks 5 days ago. Offline
Joined: 06/23/2008
Posts: 590
Re: Save as File Using Visual Basic Excel Macro

Here you go, I have not tried it yet. Let us know if it works.

fileFilter:="Text Files (*.txt), *.txt") & _ " Excel Macro Enabled Workbook (*.xlsm), *.xlsm")

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Mike's picture
Mike (not verified)
Re: Save as File Using Visual Basic Excel Macro

To bad, it didn't work. :/

Lepa's picture
User offline. Last seen 2 weeks 5 days ago. Offline
Joined: 06/23/2008
Posts: 590
Re: Save as File Using Visual Basic Excel Macro

Here this should work - just tested it.

fileFilter:=" Microsoft Office Excel Workbook (*.xls), *.xls," & _
        " Text Files (*.txt), *.txt")

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

iris's picture
iris (not verified)
Re: Save as File Using Visual Basic Excel Macro

Hi Lepa/Mike

what if I want the save as command to save the document exactly as it is (with the macros) to a new excell document under a new name in a different library as an excell document and not as *.txt?
I want the new document to be exactly like the one I'm saving... with all the features and macros.

thanks

Iris

Mike's picture
Mike (not verified)
Re: Save as File Using Visual Basic Excel Macro

Thanks Lepa, it worked absolutely fine!

Uhm, Iris. I'm not exactly sure what you mean. If you want, what you are saying, to be a part of the code or how you just do it practically. You can always press "save as", change the name and change file format to .xls.
In order to save it as a .xls just look at what Lepa wrote for me. :)
It will make it possible to save it as both a .xls and a .txt .

If I got it all wrong, please be more concise and we'll see what we can do. :)

/Mike

iris's picture
iris (not verified)
Re: Save as File Using Visual Basic Excel Macro

Thank you for your answer Mike!

The problem is solved!

:)

ja's picture
ja (not verified)
Re: Save as File Using Visual Basic Excel Macro

hi mates,

how do i make it like if whenever i open the excel file, it will prompt me to "save as" another file name?

Guest's picture
Guest (not verified)
Re: Save as File Using Visual Basic Excel Macro

Can anyone help me to write a macro for a file save as with date and time. The file name always stays the same, the date&time obviously change, but I also want the user to be able to save the file to their chosen directory. Please please help as I am not good at excel and have spent HOURS trying to sort this out!

Thanks in advance.

karen's picture
karen (not verified)
Re: Save as File Using Visual Basic Excel Macro

how to remove spaces when converted to text file

karen's picture
karen (not verified)
Re: Save as File Using Visual Basic Excel Macro

can anybody help me to set default characters incells that it will reflect when converted to text file?

Arun's picture
Arun (not verified)
how to place the new file when browse button is clicked in excel

Hi,,,how to place the new file when browse button is clicked in excel macro....

for browse:
dim a
a=application.getopenfilename
if a="false"then
..end sub

for pasting that page ,when we click browse..button....
what will be the code....

Guest's picture
Guest (not verified)
Re: Save as File Using Visual Basic Excel Macro

Below is my current code I am using, the file is saved to the current directory, but the filename isn't correct. The filename should be "109001_DTS Reimbursable Orders.xls". Because of the many files we download and manipulate it is necessary for the date to be at the beginning of the file. Any help is greatly appreciated. I'm new to the VBA code....

Sub DTSReimbursableOrders()
'
' DTSReimbursableOrders Macro
' Macro recorded 12/29/2009 by Shannon.Harbin
'
    Dim strpath As String
    Dim strfilename As String
    sPath = "D:\Documents and Settings\Shannon.Harbin\My Documents\DTS Pilot Program\"
    sFileName = "Format(Date, 'yymmdd') &_DTS Reimbursable Orders"
'   File = sFileName Might fit in nice to do this way so you have a variable to call the file
    ThisWorkbook.SaveAs (sPath) & (sFileName)

Guest Jolly's picture
Guest Jolly (not verified)
Re: Save as File Using Visual Basic Excel Macro

Thank you very much, your post realy help me :D
I want to save two sheets from document to new one with name from on cell,that was a hard fight :D but i do it :D with your help and trying and trying :D
I work with visual basic second time :D I dont have necessary bases :D and bad english :D

angelo_84's picture
angelo_84 (not verified)
Re: Save as File Using Visual Basic Excel Macro

is it possible to avoid the "save as" pop up window?

Jordan's picture
Jordan (not verified)
Re: Save as File Using Visual Basic Excel Macro

I have an excel file that has a macro on the first sheet which inputs data on three consecutive sheets. I need these sheets to be made into individual text files. Right now I just copy each tab to a new book and then save that book manually as a .txt file under a specific name (Book10, Book11, Book12) that is referenced for another macro and workbook. I do this for each of the three sheets. I can do it fine but I want other users to not have to deal with it.

I would like a macro that will save a copy of each of these sheets as I described. I don't want the user to have to select anything from a Save As screen, just save it automatically where it's supposed to go (S:\Tax\Sales Tax\Certificate Details\Book10.txt). I'm hoping the original tab names will stay the same (Col1, Col2, Col3) and that the original book will be untouched.

Any help on this would be awesome. I'm not too savvy with VBA, most of my macros are recorded, so if you could let me know what i need to change to make this macro specific to my file, that would be so helpful. Thanks a lot to people who respond to questoins like this, it is great!

YCtan's picture
YCtan (not verified)
Re: Save as File Using Visual Basic Excel Macro

How do i save file as PDF? Should I change to?

fileSaveName = Application.GetSaveAsFilename( _
InitialFileName:="C:\temp\filename_" + VBA.Strings.Format(Now, "mmddyyyy") + ".pdf", _
fileFilter:="PDF Files (*.pdf), *.pdf")

Thanks

Lukas's picture
Lukas (not verified)
Re: Save as File Using Visual Basic Excel Macro

Hi,

I want to save it to Pdf format, I changed code to:

fileSaveName = Application.GetSaveAsFilename( _
InitialFileName:="C:\temp\filename_" + VBA.Strings.Format(Now, "mmddyyyy") + ".pdf", _
fileFilter:="PDF Files (*.pdf), *.pdf")

....it is saved as Pdf but then when I want to open it, Adobe cannot read it ....should I change something more in original VBA code?

thx

Lukas

Lepa's picture
User offline. Last seen 2 weeks 5 days ago. Offline
Joined: 06/23/2008
Posts: 590
Re: Save as File Using Visual Basic Excel Macro

You can not do that. Excel doesn't know how to convert a doc to pdf format. You need a pdf converter software to do that. Saving the file with .pdf extension doesn't mean you are actually creating a pdf file.

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Sam's picture
Sam (not verified)
Re: Save as File Using Visual Basic Excel Macro

Hi Dears,

I want to save an excel file in a text format ending with current date format. However, I want the file to automatically save without having a pop up of save displayed. Is this doable pls advise.

Guest's picture
Guest (not verified)
Re: Save as File Using Visual Basic Excel Macro

can anybody tell me how to rename the file with the values in the cell, for ex: cell C2, D4, E5 & H8. a "_" would do in between the each cell values. Help is much appreciated.

ryan's picture
ryan (not verified)
Re: Save as File Using Visual Basic Excel Macro

Hi, is it also possible to save only a selected range of cells in a worksheet?

Dennis P.'s picture
Dennis P. (not verified)
Re: Save as File Using Visual Basic Excel Macro

Hi,

Thx for the VBA code. it works fine, but I heva a small problem.

When I use the save button with the macro i created thx to you, I loss my layout of my newly saved xls.
Everything is gone, the colors,... the only thing thats left is the text I have written in the cells.

I hope you can help me.

TIA Dennis.

Branco's picture
Branco (not verified)
Re: Save as File Using Visual Basic Excel Macro

Hi,
How can I save a new text file with the same name. In other words
Is there any symbol or text that I put in the place of filename to keep the same name as the original one?

Thanks

Joanna's picture
Joanna (not verified)
Re: Save as File Using Visual Basic Excel Macro

If you just mean saving it without changing the name you can use

ActiveWorkbook.Save

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
8 + 9 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.