jselaya
asked on
How can I fix VBA code so that a spreadsheet exports to PDF
Hello Experts,
Im trying to help a user in my office with a Excel 2010 spreadsheet. The spreadsheet was originally created on a MAC. There is visual basic code that allows the spreadsheet to be saved as a PDF file. I am not proficient with visual basic. When I try to run it I get Run Time 1004: Method 'SaveAs' of object' _Workbook' failed. Please see the code below:
Sub ExportPDF()
'
' SavePDF Macro
'
'
Application.ScreenUpdating = True
Application.DisplayAlerts = True
ActiveWorkbook.Save
'Define names
Sheets("MonthlyReport").Se lect
CurrentMonth = Sheets("DropDown").Range(" L2").Value
QuarterNumber = Sheets("DropDown").Range(" L1").Value
AttorneyName = Sheets("MonthlyReport").Ra nge("B3"). Value
CurrentYear = Sheets("MonthlyReport").Ra nge("B1"). Value
'Clear and set print areas
Sheets("MonthlyReport").Se lect
ActiveSheet.PageSetup.Prin tArea = ""
Application.Goto Reference:=QuarterNumber
ActiveSheet.PageSetup.Prin tArea = Selection.Address
Sheets("Questions").Select
ActiveSheet.PageSetup.Prin tArea = ""
Application.Goto Reference:=CurrentMonth
ActiveSheet.PageSetup.Prin tArea = Selection.Address
Sheets("PlenaryInventory") .Select
ActiveSheet.PageSetup.Prin tArea = ""
Application.Goto Reference:="PI_" & CurrentMonth
ActiveSheet.PageSetup.Prin tArea = Selection.Address
Sheets("ESOACaseInventory" ).Select
ActiveSheet.PageSetup.Prin tArea = ""
Application.Goto Reference:="ES_" & CurrentMonth
ActiveSheet.PageSetup.Prin tArea = Selection.Address
'Export to PDF
ActiveWorkbook.SaveAs Filename:=AttorneyName & "_" & CurrentYear & "_" & CurrentMonth & ".pdf", FileFormat:=xlPDF
'ActiveWorkbook.ExportAsFi xedFormat Type:=xlTypePDF _
', Filename:=AttorneyName & "_" & CurrentYear & "_" & CurrentMonth & ".pdf" _
', Quality:=xlQualityStandard , IncludeDocProperties:=True , IgnorePrintAreas:=False, OpenAfterPublish:=True
Sheets("MonthlyReport").Se lect
I am also attaching the spreadsheet. Any help you can provide will be greatly appreciated.
Thanks so much.
Jose
Monthly-Report-Template-V2.xlsm
Im trying to help a user in my office with a Excel 2010 spreadsheet. The spreadsheet was originally created on a MAC. There is visual basic code that allows the spreadsheet to be saved as a PDF file. I am not proficient with visual basic. When I try to run it I get Run Time 1004: Method 'SaveAs' of object' _Workbook' failed. Please see the code below:
Sub ExportPDF()
'
' SavePDF Macro
'
'
Application.ScreenUpdating
Application.DisplayAlerts = True
ActiveWorkbook.Save
'Define names
Sheets("MonthlyReport").Se
CurrentMonth = Sheets("DropDown").Range("
QuarterNumber = Sheets("DropDown").Range("
AttorneyName = Sheets("MonthlyReport").Ra
CurrentYear = Sheets("MonthlyReport").Ra
'Clear and set print areas
Sheets("MonthlyReport").Se
ActiveSheet.PageSetup.Prin
Application.Goto Reference:=QuarterNumber
ActiveSheet.PageSetup.Prin
Sheets("Questions").Select
ActiveSheet.PageSetup.Prin
Application.Goto Reference:=CurrentMonth
ActiveSheet.PageSetup.Prin
Sheets("PlenaryInventory")
ActiveSheet.PageSetup.Prin
Application.Goto Reference:="PI_" & CurrentMonth
ActiveSheet.PageSetup.Prin
Sheets("ESOACaseInventory"
ActiveSheet.PageSetup.Prin
Application.Goto Reference:="ES_" & CurrentMonth
ActiveSheet.PageSetup.Prin
'Export to PDF
ActiveWorkbook.SaveAs Filename:=AttorneyName & "_" & CurrentYear & "_" & CurrentMonth & ".pdf", FileFormat:=xlPDF
'ActiveWorkbook.ExportAsFi
', Filename:=AttorneyName & "_" & CurrentYear & "_" & CurrentMonth & ".pdf" _
', Quality:=xlQualityStandard
Sheets("MonthlyReport").Se
I am also attaching the spreadsheet. Any help you can provide will be greatly appreciated.
Thanks so much.
Jose
Monthly-Report-Template-V2.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much. It now works!
I have it on my File menu, but I think that's only because I have Acrobat (full version, not reader) installed on my machine.
If you don't have Acrobat (on Windows) you'll need to do it like this:
ActiveSheet.ExportAsFixedF
"C:\Users\YourFileNameHere
Quality:=xlQualityStandard
:=False, OpenAfterPublish:=True