Print to PDF - reference file name to cell

martijn88 · Jan 13th 2021

Hello,

I want to print a file to pdf with the file folder and name referenced to certain cell values.

Am trying to get this to work:

Code

Sub SaveRangeAsPDF()
Dim saveLocation As String
Dim rng As Range
saveLocation = "C:/Users/bobby/desktop/" & Range("g1").Value & Range("h1").Value & ".pdf"
Set rng = Sheets("sheet1").Range("A2:M34")
rng.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation
End Sub

Display More

G1 contains the folder with the formula: =h10 & "/"

H1 contains the file name with formula: =e6

If I use saveLocation = "C:/Users/bobby/desktop/folder/file.pdf" it works so I guess the issue must be the cell references.

Can anyone help me please?

**Carim** · Jan 13th 2021

Hello,

You could try the following

saveLocation = "C:\Users\bobby\desktop\" & Range("g1").Value & Range("h1").Value & ".pdf"

without forgetting cell G1 =h10 & "\"

Hope this will help

martijn88 · Jan 13th 2021

Hi Carim,

Thanks for your quick response.

I tried your suggestion but somehow it does not work.

I get an error 1004 (document is not saved, may still be opened or there was an error saving) and it show these lines in yellow:

rng.ExportAsFixedFormat Type:=xlTypePDF, _

Filename:=saveLocation

Could it be that I am missing an add-in or setting or something? I just can't understand why it does not work.

**royUK** · Jan 13th 2021

Attach an example of the workbook

martijn88 · Jan 13th 2021

I attached an example file.

Thanks in advance!

**royUK** · Jan 13th 2021

I wanted to see what is in the actual file, i.e. what is in H11.

I'm sure that I helped you with this before.

Have you changed the username o the correct username in your code

Code

"C:\Users\username\desktop\"

martijn88 · Jan 13th 2021

That must have been many years ago royUK, last time I needed this was in 2011.

This 2011 file used to work just fine, but after testing, the code also does not work anymore in this file. Same error.

I'm using office 2016 now.

The file path is correct in my original file. It also works when I directly enter it into the code without referring to cells.

The problem occurs after I try to refer to both a folder and the file name in a cell.

**royUK** · Jan 14th 2021

This should work

Code

Sub SaveRangeAsPDF()
Dim saveLocation As String
Dim rng As Range
With Blad1
saveLocation = "C:\Users\username\desktop\" & .Range("H10").Value & Application.PathSeparator & .Range("H11").Value & ".pdf"
MsgBox saveLocation: End
Set rng = .Range("b5:f19")
End With
rng.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation, _
OpenAfterPublish:=True
End Sub

Display More

martijn88 · Jan 14th 2021

Thanks for your help Roy.

The message box shows the correct file location and name, but only after I delete the "." before the ranges.

Then the code ends.

When I delete the "End" from the code after the messagebox, it shows the correct file path.

But it still does not save the PDF. Same error as before.

**royUK** · Jan 15th 2021

I thought I had removed that.

Is the range on Blad1, because removing the"." indicates different sheet contains the details.

martijn88 · Jan 15th 2021

It is on blad1, however, the file location is correct and that is also on blad1.

I think Microsoft may have recently broken the code. Found a workaround lowering standards, not saving to pdf but instead printing physically.

Can you actually get the code to work on your system?

**royUK** · Jan 16th 2021

This works perfectly for me.

martijn88 · Jan 16th 2021

Doesn't work here...

But thanks to this I finally found the issue. The folder must already exist and it did not.

As I remembered, it would be created automatically but I was wrong.

Added some code problem solved!

Thanks Roy!

Code

Sub SaveRangeAsPDF()
Dim saveLocation As String
Dim filePath As String
Dim fileName As String
Dim rng As Range
With Blad1
filePath = .Range("H10").Value
fileName = .Range("H11").Value & ".pdf"
saveLocation = filePath & Application.PathSeparator & fileName
If Dir(filePath, vbDirectory) = "" Then
MkDir filePath
End If
Set rng = .Range("b5:f19")
End With
rng.ExportAsFixedFormat Type:=xlTypePDF, _
fileName:=saveLocation, _
OpenAfterPublish:=True
End Sub

Display More

**royUK** · Jan 16th 2021

I had assumed you were saving to an existing folder.

martijn88 · Jan 16th 2021

If only we did not make assumptions things would be so much easier.

I also added a check to prevent the PDF from overwriting and add a v2..v3..v4.etc if the file already exists. This is my final code:

Code

Sub SaveRangeAsPDF()
Dim saveLocation As String
Dim filePath As String
Dim fileName As String
Dim rng As Range
Dim n As Integer
With Blad1
filePath = .Range("H10").Value
fileName = .Range("H11").Value
If Dir(filePath, vbDirectory) = "" Then
MkDir filePath
End If
Set rng = .Range("b5:f19")
End With
saveLocation = filePath & Application.PathSeparator & fileName
n = 2
Do While Dir(saveLocation & ".pdf") <> ""
saveLocation = filePath & Application.PathSeparator & fileName & " v" & n
n = n + 1
Loop
rng.ExportAsFixedFormat Type:=xlTypePDF, _
fileName:=saveLocation, _
OpenAfterPublish:=False
End Sub

Display More

Thanks again Roy.

Print to PDF - reference file name to cell

Share

Similar Threads

Range Names as Variable

How to update file names when the file names contain specific string referenced from a table?