• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to safe worksheet as PDF

P

PolarSpartan

New Member
I'm trying to save a file as a pdf using a file name and folder location that is dependent on variables in the worksheet. Here's the macro:

'Assign variable name for the file name
FN = Sheet6.Range("N10").Value

'Save to PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FN, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

End Sub

If the file is saved at the root directory level, this works fine. If I replace the FN variable with a file location, the macro will save in the right directory, but when I use the variable to save to a folder location other than a root directory, I get an error (1004).

Any ideas how to get the variable name to work?
 
Thanks for the reply, but I'm not sure that I'm understanding, Marc. The cell reference (N10 in the vb above) is a cell that contains the full path. As a test case, I have 'e:\test\test' in for the value. When it is 'e:\test', the macro works, with 'e:\test\test' it does not.
 

For example, if you want to create the file Test.pdf in existing path E:\Test\ : FN = "E:\Test\Test.pdf" …
​
 
Why not just use save as pdf, quicker than making a macro and then assigning a button, the ability is already there.


.
 
bobhc, the file is a template, used by multiple people, then repeated. I'm trying to make it easy for them to save it consistently and define the file name and location using a key word that they enter, and the date of their update.

Marc L, if I'm understanding your suggestion, I've tried just that, but still get the error. Using FN=e:\Test.pdf, the macro works fine. Using FN=e:\Test\Test.pdf, it results in an error.
 
used by multiple people
Click to expand...

But you would then need to make sure all users are allowed to have macro/vba enabled some would some may not and your files would be corrupted as they tried to do the impossible.....You know what users are like and the many levels of excel skill.


.
 
Last edited:

PolarSpartan, code works on my side with an existing path.
So the problem is not the code …
​
 
Ok, maybe I need to be checking on how the network security is set up.

Thanks a lot for following up. I appreciate the help.
 
You must log in or register to reply here.
Back
Top