Free Microsoft Excel 2013 Quick Reference

VBA to Save Workbook as pdf

I want to put a button on my sheet that will save the entire workbook as a pdf. I want it to save in the same directory in which the excel file is located and I want it to save with the same file name as the excel file. I am sure this is possible and I wouldn't imagine it would be very difficult but I am have a difficult time with it. Any help would be appreciated.


Is there way to write a VBA macro on a workbook (derived from an .xlt file) that on workbook_close would force you to save the workbook as something other than filename1.xls...also I guess if you try and save said workbook it should verify that you have not tried to save it as filename1.xls.

ie. (excuse my very bad pseudo code)

    Open a save As box 
Else Close the file. 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


What is best and cheapest way to print worksheets as pdf files? It is really only for one Excel woorkbook. We have a number of outside independent sales reps and Adobe Acrobat Pro would kill us costwise.

This is in Excel 2003.

I need to print:
a) one worksheet as a pdf file and
b) print several worksheets as one pdf file. (not always the same worksheets, they are selected via a macro)

It is fine that user will have to name pdf file every time it is created/saved, in fact in this case it is a positive.

I have read about a number of free or cheap software promising it will do this, but I am leary about trying something unless someone has experience with it.

I would like to create a button on a worksheet that will save the workbook as a unique file name. For example, if a cell contained "Feb-3" I would want the file name to be Feb-3.xls The next time I used this workbook I might want to save it as "Feb-3A.xls"

I tried to auto record a marco, however the macro used fixed text. If I updated the information in the cell, the code in the macro was hardcoded for the original info.
I need to have a way the simple save a workbook using data in a cell as the file name.
I do not use marco much, I'm sure there is an easy way to do this.

I have a workbook that I would like to save 2 of the worksheets (Test Record and Data Transfer) as pdf and email them.

Hello all,

I wondering if anyone can help me, I have a workbook with 2 sheets. I have created a userform and have several macros. My question is this, is there a way to save a copy of this workbook in .xlsx format removing all the macros and vba? I would like to execute this from a command button on my userform.

So I would press the button and it would ask like normal where I would like to save a copy to, with the exception that I want to save it as a non macro file format.

Thank you...

Hi everyone,

I am using the following code to open (Save As) Dialog Box:

I am also using the following code to open (Open) Dialog Box:

However, after installing (Save As PDF Add-In) in MS Office 2007, What is the proper VBA code to open (Publish as PDF or XPS) Dialog Box?

Temporarily, I am using the following two codes and they work fine, but I think they are not the correct codes for such cases,

If Application.GetSaveAsFilename(ActiveWorkbook.Path & "" & ActiveSheet.Name, _
"PDF (*.PDF), *.PDF") = False Then Exit Sub

I appreciate your usual help,

Best Regards,

hi guys im after a macro to save a sheet(s) as pdf to a designated folder
the workbook could have anything from 3 sheets in it to 8 but i would like to designate which sheet to save after it has run its own print macro

is it possible to save a selected sheet as a pdf?

i would like to save the pdf as cell a2 and then what the sheet is called

thanks in advance

I'm very fresh to vba, so please forgive me for any easy question.
IIn VBA (excel), I'm opening a word document, filling with values from an xls file and save it. I can save it as .docx but I can't find a way to save it as pdf.
The code i'm trying to use is:
ActiveDocument.ExportAsFixedFormat OutputFileName:= ActiveDocument.Path & "" & ActiveDocument.Name & ".pdf", ExportFormat:= wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= wdExportOptimizeForPrint, Range:=wdExportAllDocument, Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:=True, UseISO19005_1:=False
Vba doesn't recognize all the wd* (enums?).
Please help

I have an Excel 2003 template with an Auto_Open macro that imports data from a database and then performs a Save As to a new file and then closes the application. I was having trouble at first because the new file still had the Auto_Open macro that would run when it was opened which I didn't want happening but one of the postings on this site answered that question and directed me to However, I now have one final problem that I can't seem to get past.

After the template is saved as a new file and the VBA module is deleted, I can't keep Excel from prompting whether or not to save when I end the macro with Application.Quit. I've put ActiveWorkbook.Save directly before it with no luck. I've even done a Save As to a second new file before Application.Quit yet I'm still prompted to Save when Excel exits. If I manually say Yes and go into the new file, it successfully stripped the VBA module from it however, if I answer no (or use Application.DisplayPrompts = False before the Application.Quit) the new file still has the Auto_Open macro in it.

Any thoughts? Is there a way to perform Application.Quit with a command prompt that will answer "yes" to the prompt?


I'm trying to use VBA to save a copy of a workbook, I want to just save the
values not all the formulas and links. So I've got the macro to copy the
sheet and then paste special with values only and then I want to Save it. I
want to be prompted where to save it. I've tried using activeworkbook.close
but because I have a workbook before close event macro it just closes without
saving the changes.
Is there anyway I can either stop the before close macro running or can I
get SaveCopyAs to ask for a filename

I am currently writing an Excel VBA for manupicating an excel file an
then save as a tab delimited file. I have successfully use th
following code to save it as unicode text file.

ActiveWorkbook.SaveAs filename:= _
filepath & filename & ".txt", FileFormat:= _
xlUnicodeText, CreateBackup:=False

However, I would like it to save as UTF-8. Is there anyway I coul
handle this? Thanks ^_^:confused

Message posted from

Hi everyone,

I am using the following code to open (Save As) Dialog Box:

I am also using the following code to open (Open) Dialog Box:

However, after installing (Save As PDF Add-In) in MS Office 2007, What is the proper code to open (Publish as PDF or XPS) Dialog Box?

Temporarily, I am using the following two codes and they work fine, but I think they are not the correct codes for such cases,

If Application.GetSaveAsFilename(ActiveWorkbook.Path & "" & ActiveSheet.Name, _
"PDF (*.PDF), *.PDF") = False Then Exit Sub

I appreciate your usual help,

Best Regards,

After populating my workbook using a macro I want to save workbook (with new name) but without macro.Copying the data to another workbook loose my sheet formating.

I am starting to delve into Excel 2007 and was wondering if you could use VBA to save a file with a password. I know you can use VBA to save a file, but what about with a password? Meaning, when you open the file, you have to have the password for the file to actually open....

I'm trying to write VBA to save a worksheet that contains 50 ComboBoxes and I would like the save worksheet. When the saved worksheet is opened it needs to be locked so that it cannot be edited.

I have a project where I'm using VBA to update multiple excel
workbooks and creating custom copies of the workbooks as PDF files.
The custom copies of the workbooks are customized by what sheets and
individual wants to see. I have successfully used PDFwriter to
suppress the "Save as PDF File" dialog by programatically changing the
registry entries for PDFFileName, bExecViewer and bDocInfo and using
the printOut method of excel to get what I need. This works well for
all but two of the workbooks. These two workbooks create the PDF files
as programmed but at the end of the print to PDF before control
returns to the VBA code, the "Save as PDF File" dialog pop's up. I
acts as if either PDFwriter or Excel does not recognize that it had
already output a PDF copy of the workbook.

This is anoying as I have not been able to figure out what is
triggering the dialog box to pop up after the print to PDF is
complete. Has anyone experienced this or have any possible solutions
for this situation? I'm using Excel 2000 and Acrobat 5.0.

Hi :
Any ideas on a macro to save a sheet (or two) in a workbook as an entirely seperate workbook. I need to maintain all formats and column dimensions.


Hi folks,

may be I am too stupid to find the right google search arguments for my
Here goes:

EXCEL 97 Workbook on Windows 2000 prof. machine with VBA code in Module
1. Works as intended.

Now I hide the VBA code via VBA project properties and save ok.
Subsequent attempts to save the workbook will store it under a new
(random ?) filename without ".xls". An associated message will tell me
so as well as that the
file cannot be renamed and I should close this file.

What am I missing ?

Thanks in advance
Friedrich Hofmann

Hello all!

I'm using a VBScript file which I've added to Server 2003 scheduler to
automatically open up Excel, load it with a workbook which is in turn
populated with data, print it out, dynamically come up with a filename which
is figured off of the current date and time which is then appended to the
original filename of the workbook, save it in HTML format, then close
itself. Works like a charm.

I've decided I don't want to save the file in HTML format but would instead
like to save it in PDF. The reason being is HTML files can modified by any
with a text editor. PDF files are relatively permanent and secure which is
what I need.

I have Acrobat 6.0 Pro installed. I can use the print function, select
Adobe PDF as the printer, a prompt comes up asking for a file name to save
to, and it gets converted. At least when I do it manually it's like that.
I thought I could figure out how to do it within VBScript easily. As it
turns out, I'm freaking lost and have no idea how to do it. I was hoping
some info would be on Adobe's website but I'm not finding anything.

Anybody have any ideas? This workbook based report runs every 2 hours. I
have to be able to create a PDF with its own filename that I assign to it
and save it to a specified directory.

I don't have the vbscript file I'm using to do the above with handy at the
moment. If anyone wants it, just let me know and I'll post it later.

Thanks all!

Chris Smith

Hi everyone,

I need some help with the VBA code to accomplish the following:

1. Save sheet "Invoice" of workbook "Chargeback Workbook" as its own workbook with formats and values only. Prompt for save destination.

2. Create the filename based on the values of A10&" - "&E6&" - "&E5 in sheet "Invoice."

Once I have the code, I can take care of assigning it to a button myself.

Can anyone help?

Thanks and best regards,

I am having a problem with the formatting once a workbook is saved as a pdf file. when i view the individual sheets within the workbook in print preview, they look fine (fit to one page), but as soon as I print or save to pdf, the individual sheets lose all formatting (everything seems to expand - spaces are larger, fewer words on the sheet, logo is twice the size). I have even tried using the save as xps file. I have both typed code and also recorded a macro to see if it made any difference.

Any suggestions?


Hi there,

I need to save multiple worksheets in an excel file to individual CSV files. Right now, I am doing this manually once a week, but since there are over 30 worksheets, this is obviously quite cumbersome.
Attached is an example of the logic.

I have 2 "master sheets" in the workbook that do the heavy lifting of the analysis, populating the 30 worksheets with updated data, once I ran my update links etc. What I would need is some sort of VBA code that can easily save each worksheet as a CSV file in the same folder that the master file resides in, using the worksheet name as the file name, requiring no human interaction.

Do you think that is possible?

All worksheets that need to be exported start with the word "CSV", so that could be an automatic criteria for the macro to understand which sheets need to get exported.... I couldn't find a similar solution in the forum unfortunately, which struck me as weird, as I don't believe to have such a unique requirement. If there's anything out there already that does what I need, please post the link, it's very much appreciated!

Best regards,

I need to be able to save a workbook by running a marco and it save the file automatically by pulling what ever the value of cell A1 is.

Ex: I want to run the macro and it save my excel workbook in C:Documents as (Value of Cell A1) What code would you put into Visual Basic. Please help me!



I track weekly timesheets on an excel spreadsheet and use VBA to create sheets from userforms, merge sheets to workbooks, and email to main office. The PC I was using stored files locally on hard drive and all was well for a few years. I have now been assigned a terminal server and the sheets/workbooks are now located on a network drive.

I edited the modules to reflect the new folder destinations but have troubles getting the files to save and merge from this network location. I find that the files do not save to the network drive but I find them in the default dir (My Documents) BTW I do have read/write permissions.

I have attached a bit of code where I seem to have the trouble. Please help?

     'Save time sheet with job name and number in filename
    Dim x 
    Dim wb As Workbook 
    Dim todate As String 
    Dim Foldername As String 
    jname = Range("B3") 
    jnumber = Range("H3") 
    todate = Format(Now, "mm-dd-yy") 
    Foldername = ("webservereTime" & todate) 
    Workbookname = ("webservereTime" & todate & ".xls") 
    Application.ScreenUpdating = False 
    On Error Resume Next 
    MkDir Foldername 
    On Error Goto 0 
    ChDir (Foldername) 
    Set wb = ActiveWorkbook 
    With wb 
        .SaveAs jname & " " & jnumber & ".xls", Password:="xxxx" 
    End With 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The result from the code above places my timesheets in the default (My Documents) folder. I then copy the files from My Documents to the folder on the webserver machine, e: drive, Time folder.

Then I attempt to merge them with th following code:

     'To merge weekly time sheets to one book for emailing
    Dim sBook As Workbook 
    Dim tbook As Workbook 
    Dim aSht As Worksheet 
    Dim Foldername As String 
    Dim todate As String 
    Application.ScreenUpdating = False 
    Set tbook = Workbooks.Add 
    todate = Format(Now, "mm-dd-yy") 
    Foldername = ("webservereTime" & todate & "") 
    fName = Dir(Foldername) 
    Do While (fName  "") 
        ChDir (Foldername) 
        Set sBook = Workbooks.Open(fName, , , , "xxxx") ' sheets pass protected
        For bb = 1 To sBook.Sheets.Count 
            Set aSht = sBook.Sheets(bb) 
            aSht.Copy after:=tbook.Sheets(tbook.Sheets.Count) 
            tbook.Sheets(tbook.Sheets.Count).Name = Range("H3") 
        Next bb 
        fName = Dir 
    Set xlBook = Nothing 
    Set xlSht = Nothing 
    Set tbook = Nothing 
    Set tbook = ActiveWorkbook 
    ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select 
    Application.DisplayAlerts = False 
    tbook.SaveAs ("webservereTime" & todate & ".xls") 
    Application.ScreenUpdating = False 
    MsgBox "Merge Complete!" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Here I get a runtime error 1004 cannot find "timesheet 10004.xls" when the sheet IS there. I discovered after numerous tries that a copy needs to be in My Documents as well for the merge to work. The tbook does save in the correct location.

I am stumped....when I change all locations to the local drive on my terminal server all works fine. Am I missing something??

Thanks so much for any help!!