Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Button on Excel to save as PDF Macro Error

0

UPDATED (excel file attached)

Good afternoon All,

I am working on a Dashboard project and I found the Marco instructions to create a PDF with the click of a button on this site. I downloaded it and tested it out unedited and it saved the sample just fine.

However, when I try to create this in my own file (pretty much copy past with the exception of the file name location and file save location) but it keep giving me a run time error 1004 and i cant seem to find out what is going on.

I think it is probably a simple fix but I am very green in the excel marcos space. So any input would be much appriceated.

"

Option Explicit

Sub CreatePDFDash()

' https://www.teachexcel.com/

'

' Full VBA/Macro Course: https://www.teachexcel.com/vba-course-update.php?src=tut_file

'

' Create PDF

    ' Variables

    Dim newFilename As String

    ' Make a filename.

    newFilename = "C:\Users\Steven\Desktop\Performance Dashboards\" & _

        ActiveSheet.Range("A1").Value & ".pdf"

    ' Make the PDF.

    ActiveSheet.ExportAsFixedFormat _

        Type:=xlTypePDF, _

        Filename:=newFilename, _

        Quality:=xlQualityStandard, _

        IgnorePrintAreas:=False, _

        OpenAfterPublish:=True

End Sub

Answer
Discuss

Answers

0
Selected Answer

Hi MrGerlach50 and welcome to the Forum.

You can attach an Excel file to your question (which nearly always helps us 9 but you didn't so I'll make some guesses...

You're referring to the file from Don's tutorial Create PDF's of Any Worksheet with a Single Button Click.

Provided your file path "C:\Users\Steven\Desktop\Performance Dashboards\" actually exists, it seems fine. The second part of variable newFilename is:

ActiveSheet.Range("A1").Value & ".pdf"

so the possible cause of error 1004 is that the active sheet (the one you can see in a single sheet view):

  1. is the wrong one and cell A1 is empty OR
  2. the contents of cell A1 start or end with a space, period, hyphen or underline.
  3. A1 contains one or more of the characters which are not permitted in filenames by Windows.

The latter "illegal" characters are as follows:

  • * asterisk
  • ? question mark
  • / forward slash
  • $ dollar sign
  • ! exclamation point
  • ' single quotes
  • " double quotes
  • : colon
  • @ at sign
  • + plus sign
  • ` backtick
  • | pipe
  • = equal sign

Suggest you check for those and try again.

Also it's good practice too (but not always necessary) to keep your filenames to a reasonable length, under 31 characters say.

REVISION 18 November 2023:

In the file now attached to your question, the file path and name is created by the line

    ' Make a filename.
    newFilename = "C:\Users\Steven\Desktop\" & _
        ActiveSheet.Range("E2").Value & ".pdf"

but E2 contained:

Last Update 11/16/2023

which contains two instances of the (illegal) forward slash /.(see above)

The simplest way is to rewrite the date in a different, (filename) legal format. This is shown in the attached, revised file where (yellow) cell E2 now contains:

Last Update Nov 16 2023

so the code saves/displays the pdf as expected (using a known filepath on my computer), saving it in your folder as Last Update Nov 16 2023.pdf

If you really need to show the date as mm/dd/yyyy in the pdf, that's possible if you replace the line of code above with this (changes in bold):

    ' Make a filename, replacing each / with -.
    newFilename = "C:\Users\Steven\Desktop\" & _
        WorksheetFunction.Substitute(ActiveSheet.Range("E2").Value, "/", "-") & _
        ".pdf"

and your file will be saved as Last Update 11-16-2023.pdf for example.

Hope this helps fix your problem. If so, please be sure to remember to mark this Answer as Selected.

Discuss

Discussion

Thanks for the input! I truely appreciate the time you have taken to help so far.

The issue did not seem to solve I even tried to save the path to the desktop to simplify the PDF save. I even tried to make a simple version of it but keep getting the error with the debugging tool pointing to the last quarter of the macro.

Any ideas?
MrGerlach50 (rep: 2) Nov 17, '23 at 2:27 pm
I can't help now but will try to look at your file tomorrow (thanks for attaching that) and respond. 
John_Ru (rep: 6232) Nov 17, '23 at 3:17 pm
Please see REVISION 18 November 2023 to my Answer and a new version of your file. Don't forget to mark the Answer as Selected once your prove it works for you please.
John_Ru (rep: 6232) Nov 18, '23 at 7:26 am
Wow is was sitting there right in front of me. I removed the ":" after your initial post but completely went bind on the "/" in th date.

Thanks a lot for your help that did the trick!
MrGerlach50 (rep: 2) Nov 20, '23 at 9:13 am
Glad you sorted the problem in the end. Thanks for selecting my Answer, Steven.
John_Ru (rep: 6232) Nov 20, '23 at 9:23 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login