macro to save as PDF

[pointybrows]

Can anyone help with why this macro is not saving my file as a PDF?

Sub Save()
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Path = "C:\Users\Mellissa Matthews\Desktop\Dog works\"
FileName1 = Range("B9")
FileName2 = Range("B10")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & FileName2 & ".pdf", OpenAfterPublish:=True

End Sub


Thanks

Pete

[dafer660]

Greetings,

What is the text/value of the cells B9 and B10 and what exactly do you want from both ranges?

Regards,
Daniel

[Keebellah]

Just the destination filename

Please Login or Register  to view this content.

[Kenneth Hobson]

Welcome to the forum!

It should have shown an error if the path does not exist. I like to check for it first. See the Immediate Window for output from a run where Debug.print was used. Also note what you filename resolves to.

Please Login or Register  to view this content.

[pointybrows]

Hi Daniel,
B9 is a date and B10 is a name. Both come from formulae.

Pete

[pointybrows]

Any idea why the filename doesn't work? I can get a fixed file name to work.

[pointybrows]

Hi Kenneth, thanks for the welcome!

I'm out of my depth with what you showed me! The file does save in the correct folder if I have a fixed name, but when I try to be clever it does not, would it have anything to do with the quotes?

Pete

[Kenneth Hobson]

No, quotes are not the issue. It is the subfolder path that is the issue I suspect.

Try this and then comment out the 3rd Path line and uncomment the 2nd Path line and run as the 2nd Path will always exist.

Of course I assumed that the filename in your cells creates a valid filename. They can not have quotes or other illegal filename characters. If you uncomment the Debug.Print line, you can see the built filename in the Immediate Window. Make sure that string for the filename is legal. I guess one could force a background write to a text file using that path to see if the filename is valid but with no On Error line, it should throw an error if filename is not valid.

Please Login or Register  to view this content.

[pointybrows]

OK so that worked, thanks, it now saves it to the desktop, but how would I get it to save into the Dog works folder on the desktop? Sorry if I'm being dopey!

[dafer660]

Greetings,

I think this code worked but you just needed to add ".value" at the end of the range:

Please Login or Register  to view this content.

To create the folder, put this piece of code below:

Please Login or Register  to view this content.

after Mr. Kenneth's code below:

Please Login or Register  to view this content.

However Mr.Kenneth Hobson has the right code to check the existence of the folder and it is a very cool snipet to use in the future

[Kenneth Hobson]

If it does not exist, then make it manually or make Excel make it. If you need help with the latter, see MkDir(). I can show you that method or a Shell() method if needed. In this case, MkDir() should suffice. You just need to modify the If() structure to do the check first and then MkDir Path for the Then case. The Export would be after the If() structure.

[pointybrows]

Thanks to all for your help. Got it working now, and learnt a little more about how macros work.

Thanks again.