Macro to Save File as PDF based on Cell Conte

June 21, 2010 at 05:27:03
Specs: Windows XP

I am looking for a macro that when I click on a button, the workbook is saved as a PDF file and automatically named based on cell content. Excel 2007

Sub pdfsave()

pdfname = Range("A1").Value

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfname, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End Sub

This works, but I want to save it straight to a specific folder on my computer. for example c:\my documents/PDF.
Also, do you you if it is possible to save the document as a PDF and at the same time as an excel file in the same macro?


See More: Macro to Save File as PDF based on Cell Conte

Report •


#1
June 23, 2010 at 04:44:19

"...save it straight to a specific folder..."
Try changing
Filename:=pdfname
to
Filename:="c:\my documents\PDF\" & pdfname


As for saving it as an Excel file...

Hopefully someone can help you out with that.
I will only be able to that tonight.

In what Excel format do you want to save the file?
Excel 2003 (.xls)
Excel 2007 Macro-Free (.xlsx)
Excel 2007 Macro-Enabled (.xlsm)


Report •

#2
June 23, 2010 at 04:50:37

I would prefer to save in .xlsx

Report •

#3
June 23, 2010 at 10:15:10

pdfname = Range("A1").Value

I will assume that Cell A1 is just a filename.
In other words, "somefilename" and not "somefilename.pdf".
If it is "somefilename.pdf", then the code need to be changed..


This will save your file as a Macro-Free Excel Workbook in your specified folder.
Then it will also save it as a Pdf file in the same folder.

This Macro should not be in your workbook.
If it is, I suggest you move it to your Personal Macro File.
Otherwise, Excel will bring up a Confirmation Box about saving Macros in a Macro-Free workbook.

Sub DocSave()

' Set Variables.
' Note: {FileExt = ".xlsx"} is not needed.  Excel 2007 will save the file with the correct extension.
    SaveFolder = "C:\My Documents\PDF\"
    DocName = Range("A1").Value
    FileExt = ".xlsx"

' Save as Macro-Free Excel.
    ActiveSheet.SaveAs Filename:=SaveFolder & DocName & FileExt, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ' ActiveSheet.SaveAs Filename:=SaveFolder & DocName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

' Save as PDF
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=SaveFolder & DocName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    True

End Sub


Note:
{FileExt = ".xlsx"} may not be needed.
When I tested it (without FileExt), Excel 2007 and 2010 Beta saved the file with the correct extension.
Needless to say, Excel 2003 just gave an error.


Report •

Related Solutions

#4
June 24, 2010 at 02:34:36

Thanks I will try this tomorrow when I get home.

When I want to save as excel 2003 , do I just change FileExt = ".xlsx" to FileExt = ".xls"?


Report •

#5
June 24, 2010 at 11:37:51

"...do I just change FileExt = ".xlsx" to FileExt = ".xls"?..."

Not quite.

You can change FileExt = ".xlsx" to FileExt = ".xls", but then your code for saving will have to be changed.


If you save from within 2003, you can use:

    ActiveSheet.SaveAs Filename:=SaveFolder & DocName & FileExt

If you save from within 2007, but in a 2003 format, you can use:

     ActiveSheet.SaveAs Filename:=SaveFolder & DocName & FileExt, FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False


Report •

#6
June 24, 2010 at 12:24:25

Edit:
Shortened some sentences that were split over two lines.


Sub MultiSave()

' Just change {FileExt = ".xlsx"} to whatever you need.

' Set Variables.
    SaveFolder = "C:\My Documents\PDF\"
    DocName = Range("A1").Value
    FileExt = ".xlsx"
' .xls = Excel 2003
' .xlsx = Excel 2007/2010Beta Macro-Free
' .xlsm = Excel 2007/2010Beta Macro-Enabled


' Find out what Version of Excel is Active.
    Select Case Int(Application.Version)

' Excel 2003.
    Case 11

        ' Save as Excel Workbook.
        ActiveSheet.SaveAs _
        Filename:=SaveFolder & DocName & FileExt

        ' As far as I know, you can't save a Pdf from Excel 2003.
        ' Your best bet is probably to install PDF Creator.
        ' Then print to the PDF Creator printer.

' Excel 2007 / 2010 Beta.
' Note: There is no Office Version 13.
    Case 12, 14

        Select Case FileExt

' Excel 2003 Format, from Within Excel 2007 / 2010 Beta.
        Case ".xls"

            ' Save as Excel Workbook.
            ActiveSheet.SaveAs _
            Filename:=SaveFolder & _
            DocName & FileExt, _
            FileFormat:=xlExcel8, _
            CreateBackup:=False

            ' Save as PDF
            ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=SaveFolder & DocName, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True

' Save as Macro-Enabled Excel 2007 / 2010 Beta Workbook.
        Case ".xlsm"

            ' Save as Excel Workbook.
            ActiveSheet.SaveAs _
            Filename:=SaveFolder & DocName & FileExt, _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
            CreateBackup:=False

            ' Save as PDF
            ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=SaveFolder & DocName, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True

' Save as Macro-Free Excel 2007 / 2010 Beta Workbook.
        Case ".xlsx"

            ' Save as Excel Workbook.
            ActiveSheet.SaveAs _
            Filename:=SaveFolder & DocName & FileExt, _
            FileFormat:=xlOpenXMLWorkbook, _
            CreateBackup:=False

            ' Save as PDF
            ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=SaveFolder & DocName, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True

' Unknown File Format.
        Case Else

' Just save as if we are in Excel 2003, and hope for the best.
            ActiveSheet.SaveAs _
            Filename:=SaveFolder & DocName & FileExt

        End Select

' Unknown Version of Excel.
    Case Else

' Do the same as with Excel 2003.
        ActiveSheet.SaveAs _
        Filename:=SaveFolder & DocName & FileExt

    End Select

End Sub


Report •

#7
October 5, 2010 at 07:04:09

pls help ....

i am trying to save a sheet with ctuepdf and sent it with outlook (Excel +Outlook 2003)
the macro is not saving the pdf file each time.

Sub VerstuurEmail()

Dim objOl As Outlook.Application

Dim objMail As Object

Dim fileName As String

Set objOl = Outlook.Application

Set objMail = objOl.CreateItem(olMailItem)

Sheets("ENGELS").Select

fileName = "Y:\ Dag Prijzen Verzend\Engels\Dailyprices.pdf"

Application.ActivePrinter = "CutePDF Writer op CPW2:"

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

SendKeys fileName & "{ENTER}" & "{TAB}" & "{ENTER}", True

With objMail

.To = "to@.nl"

.BCC = "to@.nl"

.Body = "Goodmorning our Todays prices --Prices can change during the day--"

.Subject = "Daily prices English"

.NoAging = True

.Attachments.Add "Y: \Dag Prijzen Verzend\Engels\Dailyprices.pdf"

.Display

Application.Wait (Now + TimeValue("0:00:02"))

Application.SendKeys "%e"

End With

Set objOl = Outlook.Application

Set objMail = objOl.CreateItem(olMailItem)

End Sub


Report •


Ask Question