PDA

View Full Version : [SOLVED] PDF from a macro in excel



nathandavies
04-11-2018, 05:30 AM
Hi all,

I have created a macro to PDF a hidden page using a macro, but i want to use cells within the workbook to save the pdf in the correct location and with the correct title, i have tried this code but it seems to crash on the file name lien every time.

i have attached my code for the reference.

Thanks in advance.



Sub Macro4()' Macro4 Macro


ProjectRootFolder = Sheets("RootFolder").Range("B5").Value
JobNumber = Sheets("Summary").Range("CJobNumber").Value
CSiteName = Sheets("Summary").Range("CSiteName").Value
CCompanyName = Sheets("Summary").Range("CCompanyName").Value
CVaritationNumber1 = Sheets("Summary").Range("CVaritationNumber1").Value


Range("AB49:AD49").Select
ActiveCell.FormulaR1C1 = "PDF Quote 1"
Sheets("Variation Quote 1").Visible = True
Sheets("Variation Quote 1").Select
Range("A1:K62").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
ProjectRootFolder \ CCompanyName \ JobNumber & " " & CSiteName \ CVaritationNumber1.pdf _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
Sheets("Variation Quote 1").Visible = False
Sheets("Summary").Se

Bob Phillips
04-11-2018, 05:56 AM
With Sheets("Variation Quote 1")

.Range("AB49").Value = "PDF Quote 1"
.Visible = True
.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=ProjectRootFolder & Application.PathSeparator & _
CCompanyName & Application.PathSeparator & _
JobNumber & " " & CSiteName & Application.PathSeparator & _
CVaritationNumber1 & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
.Visible = False
End With

Paul_Hossler
04-11-2018, 05:57 AM
I'm thinking you're missing some &'s and quotes





msgbox ProjectRootFolder & "\" & CCompanyName & "\" & JobNumber & " " & CSiteName & "\" & CVaritationNumber1.pdf

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
ProjectRootFolder & "\" & CCompanyName & "\" & JobNumber & " " & CSiteName & "\" & CVaritationNumber1.pdf _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

nathandavies
04-11-2018, 06:21 AM
Thank you both!

a mixture of both codes and i got it working perfectly.