Saving excel sheet to .pdf using VBA to specific folders

alex90

New Member
Joined
Mar 28, 2014
Messages
8
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
2,030
be sure the folder name has an end slash
and try NOT to use drive letters on the network, always use UNC naming.

Code:
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
 
Last edited:

alex90

New Member
Joined
Mar 28, 2014
Messages
8
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

New Member
Joined
Mar 28, 2014
Messages
8

ADVERTISEMENT

??? 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

New Member
Joined
Mar 28, 2014
Messages
8
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

New Member
Joined
Mar 28, 2014
Messages
8
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?? ;)
 

Forum statistics

Threads
1,148,294
Messages
5,745,942
Members
423,985
Latest member
sayed manzar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top