VBA for Saving PDF - Save Location going to My Documents

[GCav]

Hi all,

I have a working code which does what I need except no matter what I try it keeps saving to My Documents.

I did have a previous code which was within the file to prevent the file being saved, but allowing a pdf to be saved. I'm not sure if there's lingering code preventing my new code from working!?

Anyway, the code goes as:

Sub EPC_Checklist()

Dim FilePath As String
Dim FileName As String
Dim Report As String

FilePath = "Y:/Shared/Technical/EPC Checklist/"
Report = " EPC Checklist"

With ActiveSheet
FileName = .Range("B3").Value & (Plot) & .Range("B8").Value & Report
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FileName

End With
End Sub



The resulting file has the following file name:

Taverham1 EPC Checklist

This is generating from Cell B3 [Taverham] and Cell B8 [1]

How can I get this to save to the file path as listed after the FilePath?

Also I would like the the word Plot to be added before the number 1.

Essentially exported file path would be:


Y:/Shared/Technical/EPC Checklist/Taverham Plot 1 EPC Checklist.pdf


Any help would be much appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub EPC_Checklist()

Dim FilePath As String
Dim FileName As String
Dim Report As String

FilePath = "Y:\Shared\Technical\EPC Checklist\"
Report = " EPC Checklist"

With ActiveSheet
FileName = .Range("B3").Value & " Plot " & .Range("B8").Value & Report
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FilePath & FileName

End With
End Sub

 

[GCav]

Hi & welcome to MrExcel.
How about

Sub EPC_Checklist()

Dim FilePath As String
Dim FileName As String
Dim Report As String

FilePath = "Y:\Shared\Technical\EPC Checklist\"
Report = " EPC Checklist"

With ActiveSheet
FileName = .Range("B3").Value & " Plot " & .Range("B8").Value & Report
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FilePath & FileName

End With
End Sub

Ah great thanks. I thought I had tried that before. Forward and backslashes can be a pain!?

 

[GCav]

Works perfectly.

 

[Fluff]

You're welcome & thanks for the feedback.