VBA Save As PDF

[toughie]

Hi im looking for simple code to use vba to save an excel file as a PDF ?

Here is my code so far :


Set wb = Workbooks.Add
ThisWorkbook.Activate
ActiveSheet.Copy Before:=wb.Sheets(1)
wb.Activate
wb.SaveAs Filename:= Range("G5") & "-" & Range("G7") & Range("D7") & Range("N1")

Any help will be appreciated thanks

 

[Prish]

Hi Toughie

The following will assist you:

Sub SavePDF()

Dim PdfFile As String


' Define PDF filename and path
  PdfFile = "C:\Temp\Filename.pdf"
' Export activesheet as PDF
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With


End Sub

 

[toughie]

If Val(ComboBox5.Value) = Range("K8") = True Then



PdfFile = "M:\bot\macdata\Vam Cell\VAM LOG" & Range("G5") & "-" & Range("G7") & Range("D7") & Range("N1")
With ActiveSheet
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
wb.Activate
Else
MsgBox "Incorrect Revision"
Exit Sub

I have this code now and i cant get it to run without getting error else without if

any idea

 

[John_w]

You are missing the End With shown in post no. 2.

 

[toughie]

where should i put the end with?

 

[Prish]

after Exit Sub

 

[toughie]

Thanks for the help Prish that worked

I get save type method failed error when I use this code now any idea whats wrong with this?

wb.SaveAs Filename = "C:\Users\andy_\OneDrive\Desktop\VAMLog" & Range("G5") & "-" & Range("G7") & Range("D7") & Range("N1") & ".xlsm"

 

[Domenic]

The syntax for your named parameter is incorrect. It should be Filename:=" . . . " . By the way, you should specify the file format as well. So it should be...

wb.SaveAs Filename:="C:\Users\andy_\OneDrive\Desktop\VAMLog" & Range("G5") & "-" & Range("G7") & Range("D7") & Range("N1") & ".xlsm", FileFormat:=52 'xlOpenXMLWorkbookMacroEnabled

Hope this helps!

 

[John_w]

after Exit Sub

Putting it there doesn't make sense because then the With ... End With is spread between the If and Else blocks.

I would put the End With as close to the With as possible, i.e. immediately after the .ExportAsFixedFormat line.