Save File To A Specified Location As Per A Cell

[CarlStephens]

Hello All,

I have the below code that prints to pdf and saves it in the same location as the excel file, can someone kindly advise what I would need to adjust in the FileName code for the pdf file to save in a path location that is in a cell on sheet 1, thank you.

Sub PrintVisa()

Dim FileName As String
FileName = ThisWorkbook.Path & "\Seabourn_Visa_Letter.pdf"
MsgBox "The visa letter will now save in the One Drive/Joiners Docs folder. Check the details, and then move the letter into the candidate folder before sending Email 1 (tab E1)."
Sheets(Array(12)).Copy 'it creates a new workbook containing the first three sheets
With ActiveWorkbook
.ExportAsFixedFormat xlTypePDF, FileName, , , , , , True
.Close False
End With
End Sub

 

[rlv01]

Usually it would be something like this:

   FileName = Trim(ThisWorkbook.Worksheets("Sheet1").Range("A1").Value)

Note that the above depends on Cell A1 containing text that references an existing file folder otherwise there will be an error. So if you you want to harden it ,you would need to add some error checking code.

 

[CarlStephens]

Thank you. It is working, kind of......I am getting an error on this line now. Any ideas how to fix it?

.ExportAsFixedFormat xlTypePDF, FileName, , , , , , True

 

[rlv01]

Thank you. It is working, kind of......I am getting an error on this line now. Any ideas how to fix it?

.ExportAsFixedFormat xlTypePDF, FileName, , , , , , True

You did not share the actual error message, but it's likely because the variable FileName does not reference a valid path. This is where error checking comes in.

Sub PrintVisa()
    Dim CellContainingFilePath As Range
    Dim FileName As String, FolderName As String

    Set CellContainingFilePath = ThisWorkbook.Worksheets("Sheet1").Range("A1")    '<- edit to suit
    FileName = Trim(CellContainingFilePath.Value)

    If FileName = "" Then
        MsgBox "No File Data in Cell " & CellContainingFilePath.Address(, , , True), vbOKOnly Or vbExclamation, "File Name Error"
        Exit Sub
    End If

    If InStrRev(FileName, "\") - 1 > 0 Then
        FolderName = Left(FileName, InStrRev(FileName, "\") - 1)

        With CreateObject("Scripting.FileSystemObject")
            If Not .FolderExists(FolderName) Then
                MsgBox "Folder not found:" & vbCr & FolderName, vbOKOnly Or vbExclamation, "Cell: " & CellContainingFilePath.Address(, , , True)
                Exit Sub
            End If
        End With
    Else
        MsgBox "Please Specify the full file path (folder + file name) in Cell " & CellContainingFilePath.Address(, , , True) & vbCr & FolderName, vbOKOnly Or vbExclamation, "File Folder Error"
        Exit Sub
    End If

    MsgBox "The visa letter will now save in the " & FolderName & " folder. Check the details, and then move the letter into the candidate folder before sending Email 1 (tab E1)."
    Sheets(Array(12)).Copy                            'it creates a new workbook containing the first three sheets
    With ActiveWorkbook
        .ExportAsFixedFormat xlTypePDF, FileName, , , , , , True
        .Close False
    End With
End Sub

 

[CarlStephens]

Thank you so much for this. I now realise what the issue is......the cell with the path address is linked to my One Drive and it is a HTTP address, which it has to be, rather than my C: as there are other users outside of my network that need to access the file/macro. If I put the C: address, the code works perfectly, however if I put the HTTP address, it does, even though if I click the cell, the link works.

What is weird, it the below code saves the pdf file where the excel doc is saved, which is on my OneDrive, so it knows where to save it but not read the cell with the same address in (I copied from the OneDrive web page where it says "Copy Link". Any ideas?

Sub PrintVisa()

Dim FileName As String
FileName = ThisWorkbook.Path & "\Seabourn_Visa_Letter.pdf"
MsgBox "The visa letter will now save in the One Drive/Joiners Docs folder. Check the details, and then move the letter into the candidate folder before sending Email 1 (tab E1)."
Sheets(Array(12)).Copy 'it creates a new workbook containing the first three sheets
With ActiveWorkbook
.ExportAsFixedFormat xlTypePDF, FileName, , , , , , True
.Close False
End With
End Sub

 

[rlv01]

Onedrive folders can be accessed via an http link or they can be mapped into your standard file structure. I suspect that ThisWorkbook.Path is doing the latter which is why you can save the file. That can be tested:

Sub ShowFilePath
     Debug.Print Thisworkbook.Path
end sub

If you do that, what is the result?