Code doesn’t work- HELP

[eekka]

Sub ExportWorkbookAsPDF()

Dim wb As Workbook

Dim ws As Worksheet

Dim filePath As String

Set wb = ActiveWorkbook

Set ws = wb.ActiveSheet

filePath = Environ("USERPROFILE") & "\Desktop\" & Left(wb.Name, InStrRev(wb.Name, ".")) & ".pdf"



ws.Columns("A").ColumnWidth = 4.78

ws.Columns("B").ColumnWidth = 13.11

ws.Columns("E").ColumnWidth = 13.11

ws.Columns("F").ColumnWidth = 9.11

ws.Columns("G:H").ColumnWidth = 10.33

ws.Columns("C:D").ColumnWidth = 13

ws.Columns("I").ColumnWidth = 10.56

ws.Columns("O").ColumnWidth = 9.22

ws.Columns("P").ColumnWidth = 10.56

ws.Columns("R").ColumnWidth = 10.33

ws.Columns("S").ColumnWidth = 9.22

ws.Columns("U").ColumnWidth = 9.22

wb.ActiveSheet.PageSetup.Orientation = xlLandscape

Dim tableRange As Range

Set tableRange = ActiveSheet.Range("A1:V13")

ActiveSheet.Rows(tableRange.Row + tableRange.Rows.Count & ":" & ActiveSheet.Rows.Count).Hidden = True

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

End Sub

Here’s my code, it’s quite simple but basically it keeps showing error in this active workbook export. Idea is to transform the file to fit landscape view as pdf and save it to desktop. I know there’s a function to automatically save it to fit, but the issue is it doesn’t save the file to desktop as pdf

 

[Domenic]

For Windows Excel...

1) The syntax for ExportAsFixedFormat seems fine.

2) The path and file assigned to filePath seems fine, except that the name will include 2 dots (ie. filename..pdf). But that shouldn't be a problem.

3) A workbook must be active in order for the code to run successfully. Is this the case?

4) Can you please confirm the line in which you're getting the error, and the type of error you're getting?

 

[eekka]

For Windows Excel...

1) The syntax for ExportAsFixedFormat seems fine.

2) The path and file assigned to filePath seems fine, except that the name will include 2 dots (ie. filename..pdf). But that shouldn't be a problem.

3) A workbook must be active in order for the code to run successfully. Is this the case?

4) Can you please confirm the line in which you're getting the error, and the type of error you're getting?

Sub ExportWorkbookAsPDF()

Dim wb As Workbook

Dim ws As Worksheet

Dim filePath As String



' Set the workbook object

Set wb = ActiveWorkbook

Set ws = wb.ActiveSheet



' Set the file path for saving the PDF

filePath = wb.Path & "\" & Left(wb.Name, InStrRev(wb.Name, ".")) & "pdf"







' Set the workbook to landscape orientation

wb.ActiveSheet.PageSetup.Orientation = xlLandscape

ThisWorkbook.ActiveSheet.Columns("B:H").AutoFit

ThisWorkbook.ActiveSheet.Columns("J:N").AutoFit

ThisWorkbook.ActiveSheet.Columns("P:V").AutoFit

ws.Columns("A").ColumnWidth = 4.78

ws.Columns("I").ColumnWidth = 10

ws.Columns("O").ColumnWidth = 6

ThisWorkbook.ActiveSheet.Rows.AutoFit

ThisWorkbook.ActiveSheet.PageSetup.Zoom = False

ThisWorkbook.ActiveSheet.PageSetup.FitToPagesWide = 1

ThisWorkbook.ActiveSheet.PageSetup.FitToPagesTall = 1





' Define the table range

Dim tableRange As Range

Set tableRange = ActiveSheet.Range("A1:V13")



' Hide cells below the table

ActiveSheet.Rows(tableRange.Row + tableRange.Rows.Count & ":" & ActiveSheet.Rows.Count).Hidden = True



With ActiveSheet

.ExportAsFixedFormat 0, Environ("USERPROFILE") & "\Desktop\" & _

.Name = filePath & ".pdf", OpenAfterPublish:=True



End With





End Sub

Here’s the updated code. However, it saves as file as FALSE.pdf. Is it possible to add a line in order to keep original name when saving?

 

[Domenic]

Try the following...

Sub ExportWorkbookAsPDF()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim filePath As String
    
    ' Set the workbook object
    Set wb = ActiveWorkbook
    
    ' Set the worksheet object
    Set ws = wb.ActiveSheet
    
    ' Set the file path for saving the PDF
    filePath = wb.Path & "\" & Left(wb.Name, InStrRev(wb.Name, ".")) & "pdf"
    
    ' Set the page setup settings
    With ws.PageSetup
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    
    With ws
        .Columns("B:H").AutoFit
        .Columns("J:N").AutoFit
        .Columns("P:V").AutoFit
        .Columns("A").ColumnWidth = 4.78
        .Columns("I").ColumnWidth = 10
        .Columns("O").ColumnWidth = 6
        .Rows.AutoFit
    End With
    
    ' Define the table range
    Dim tableRange As Range
    Set tableRange = ws.Range("A1:V13")
    
    ' Hide cells below the table
    ws.Rows(tableRange.Row + tableRange.Rows.Count & ":" & ws.Rows.Count).Hidden = True
    
    ws.ExportAsFixedFormat Type:=xlTypePDF, filename:=filePath, OpenAfterPublish:=True

End Sub

By the way, in future, please place your code within code tags so that it's easier to read, and copy/paste. You'll find the button for the code tag on the menu bar, just above the text area.

Hope this helps!