VBA Code to Save PDF file to location as defined in a cell

[Carl Stephens]

Hello All,

I have the below code that prints to pdf and gives the file name based on cell N1, and in cell N2 I have the file path location of where I want the file to save, but for the life of me cannot get the code right, and I am hoping that someone can kindly enlighten me on what the code should look like. Thank you all.

Sub PrintVisa()

s = Range("N1").Value
'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
s, Quality:=xlQualityStandard, IncludeDocProperties _
:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

MsgBox "Success - Ensure that you close the PDF file before printing another document."

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

 

[DanteAmor]

Try this:

Sub PrintVisa()
  Dim s As String, p As String
  
  s = Range("N1").Value   'file name
  p = Range("N2").Value   'path
  If Right(p, 1) <> "\" Then p = p & "\"
  If Dir(p, vbDirectory) = "" Then
    MsgBox "The path does not exist"
    Exit Sub
  End If
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=p & s, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

  MsgBox "Success - Ensure that you close the PDF file before printing another document."
End Sub

 

[Carl Stephens]

Try this:

Sub PrintVisa()
  Dim s As String, p As String
 
  s = Range("N1").Value   'file name
  p = Range("N2").Value   'path
  If Right(p, 1) <> "\" Then p = p & "\"
  If Dir(p, vbDirectory) = "" Then
    MsgBox "The path does not exist"
    Exit Sub
  End If
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=p & s, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

  MsgBox "Success - Ensure that you close the PDF file before printing another document."
End Sub

Thank you. I got an run-time error 53 - file not found on the below code. Do you know what the error is?

Sub PrintVisa()
Dim s As String, p As String

s = Range("N1").Value 'file name
p = Range("N2").Value 'path
If Right(p, 1) <> "\" Then p = p & "\"
If Dir(p, vbDirectory) = "" Then
MsgBox "The path does not exist"
Exit Sub
End If
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=p & s, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

MsgBox "Success - Ensure that you close the PDF file before printing another document."
End Sub

 

[Carl Stephens]

Thank you. I got an run-time error 53 - file not found on the below code. Do you know what the error is?

Sub PrintVisa()
Dim s As String, p As String

s = Range("N1").Value 'file name
p = Range("N2").Value 'path
If Right(p, 1) <> "\" Then p = p & "\"
If Dir(p, vbDirectory) = "" Then
MsgBox "The path does not exist"
Exit Sub
End If
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=p & s, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

MsgBox "Success - Ensure that you close the PDF file before printing another document."
End Sub

Same error again......sorry to be a pain.

 

[DanteAmor]

You can put here what you have inside cell N2
And also check that the folder exists on your computer

 

[Carl Stephens]

You can put here what you have inside cell N2
And also check that the folder exists on your computer

I have a One Drive address in the cell, and I have just changed the address to my desktop and it works, so the issue is with the One Drive address. Thank you for making the code work, now I need to resolve the One Drive path issue.

 

[Carl Stephens]

I have a One Drive address in the cell, and I have just changed the address to my desktop and it works, so the issue is with the One Drive address. Thank you for making the code work, now I need to resolve the One Drive path issue.

This is what is in cell N2. The stars are to hide my account details. I checked and the folder exists on my One Drive account. I am using the http address rather then the local path, which seems to be the issue.
Thank you.

h t t p s://carnivalcorp-my.sharepoint.com/personal/****/_layouts/15/onedrive.aspx?id=%2Fpersonal%2Fcarl%5Fstephens%5Fseabourn%5Fco%5Fuk%2FDocuments%2FNew%20Trackers%20%2D%20HA%20Group%2FSeabourn%2FVisa%20Letters

 

[Carl Stephens]

This is what is in cell N2. The stars are to hide my account details. I checked and the folder exists on my One Drive account. I am using the http address rather then the local path, which seems to be the issue.
Thank you.

h t t p s://carnivalcorp-my.sharepoint.com/personal/****/_layouts/15/onedrive.aspx?id=%2Fpersonal%2Fcarl%5Fstephens%5Fseabourn%5Fco%5Fuk%2FDocuments%2FNew%20Trackers%20%2D%20HA%20Group%2FSeabourn%2FVisa%20Letters

Would you be able to advise instead, on what the code will look like after saving to pdf, to then save to the desktop, no matter who the user is (as there are multiple users). I am thinking that the below code needs to be inserted somewhere? But, I am not too sure, as I am fairly new to coding. Thank you.

user = Environ("Username")
desktop = "C:\Users\" & user & "\Desktop\"

 

[DanteAmor]

Change this line:

p = Environ("USERPROFILE") & "\Desktop\"

 

[Carl Stephens]

Change this line:

p = Environ("USERPROFILE") & "\Desktop\"

Thank you sir.