CODEWITHSUNDEEP
vbaexcelpdf
Ryan
Ryan

Reputation: 21

Excel macro - Export to PDF

I have a work book that has many macros to export various worksheets as pdfs in the same location the workbook is saved.

My issue is, if the workbook is saved in a folder on the desktop, then the PDFs are generated just fine.

When the workbook is saved on a network location, the pdfs don't generate. below is a sample of the macro:

Sub PDF_CStmtP()

    Application.ScreenUpdating = False

    ThisWorkbook.Sheets(Array("C Stmt - P")).Select

    pdfname = fileSaveName
    ChDir ActiveWorkbook.Path & "\"
    fileSaveName = "Closing Statement (Purchase)"

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

    Application.ScreenUpdating = True

    ActiveWorkbook.Sheets("Main Menu").Activate

    MsgBox "File Saved " & " " & fileSaveName
End Sub

Upvotes: 2

Views: 1856

Answers (1)

cbridgerg00
cbridgerg00

Reputation: 21

Your issue is the ChDir command, see here for an explanation: https://www.techonthenet.com/excel/formulas/chdir.php

The important part of this is "The CHDIR statement lets you change the current directory on the current drive. If you need to change drives, try using the CHDRIVE statement first."

When you are trying to save to a network drive you are changing the drive letter from C:\ to whatever the network drive is mapped to, in my case it was U:\ .

The simple fix to your code is to move the Path from ChDir to just being in the filename, so your code should look like:

Sub PDF_CStmtP()

Application.ScreenUpdating = False

ThisWorkbook.Sheets(Array("C Stmt - P")).Select

pdfname = fileSaveName
'ChDir ActiveWorkbook.Path & "\"
fileSaveName = ActiveWorkbook.Path & "\" & "Closing Statement (Purchase)"

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

Application.ScreenUpdating = True

ActiveWorkbook.Sheets("Main Menu").Activate

MsgBox "File Saved " & " " & fileSaveName
End Sub

There are a few other edits you could make to clean it up, but this will solve the issue at hand.

**Based on the comment about the message box, you could change the code to this:

Sub PDF_CStmtP()

Application.ScreenUpdating = False

ThisWorkbook.Sheets(Array("C Stmt - P")).Select

pdfname = "Closing Statement (Purchase)"
'ChDir ActiveWorkbook.Path & "\"
fileSaveName = ActiveWorkbook.Path & "\" & pdfname

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

Application.ScreenUpdating = True

ActiveWorkbook.Sheets("Main Menu").Activate

MsgBox "File Saved " & " " & pdfname
End Sub

Upvotes: 2

Related Questions