Saving excel sheet to .pdf using VBA to specific folders

[alex90]

Hi,

Firstly, my knowledge of coding is basic at best....i've looked on various pages to find a solution and concluded the below. However, whilst everything works perfectly, unfortunately i cannot seem to get the file to save to the correct folder. I keeps saving to my documents!!

Sub CentreSoft()

pdfName = ActiveSheet.Range("X20")
ChDir "Z:\Centresoft\Coding Forms PDF\Centresoft"
fileSaveName = ActiveSheet.Range("X20")

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

MsgBox "PDF File Saved Correctly"
End Sub

If you could add a bit of code to print the pdf after the save (or excel doc prior to saving), that would be amazing too.

Please help!!!!

Many thanks

 

[ranman256]

be sure the folder name has an end slash
and try NOT to use drive letters on the network, always use UNC naming.

Sub CentreSoft()
Dim vDir


pdfname = ActiveSheet.Range("X20")
vDir = "\\server\folder\Centresoft\Coding Forms PDF\Centresoft\"       'be sure of the last slash
fileSaveName = vDir & pdfname


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fileSaveName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False


MsgBox "PDF File Saved Correctly"
End Sub

 

[alex90]

I amended \\server\folder\ to Z:\ as well

 

[alex90]

If I don't use drive letters, how do I start the search for the folder? We have multiple drives on our network that all begin with a letter!

 

[alex90]

??? My original message saying thankyou and the file not working hasn't appeared!! I copied and pasted exactly as you guided and amended as per the above threads?

Didn't want you to think I was rude

 

[alex90]

Sorry for all the questions.....I have got past (I think) the letters element but still the code doesn't work. This is what I have entered;

The part in red is what is failing.

Sub CentreSoft()
Dim vDir

pdfname = ActiveSheet.Range("X20")
vDir = "\\cdhnas\cdh\Centresoft\Coding Forms PDF\Centresoft"
fileSaveName = vDir & pdfname

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fileSaveName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

MsgBox "PDF File Saved Correctly"
End Sub

 

[alex90]

Oh my!!! Got it, so so sorry.

It was as simple as an character needing to be upper case (not lower)!!

Thanks so much for your assistance

any chance of the code to auto print??