VBA for Saving PDF - Save Location going to My Documents

GCav

New Member
Joined
Jan 6, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

New Member
Joined
Jan 6, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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!?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,241
Messages
5,600,503
Members
414,384
Latest member
GrandmasCookies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top