Filepath in VBA

[Gavin Harrison]

Hi all.

I'm hoping you can help.

I have a excel sheet, with a macro to export as a PDF. See below, But I would like to either set the destination folder or initiate the save as dialogue box so the user can set the location the export is saved.

If anyone can help in updating the below code to incorporate that, I would very much appreciate it.

Sub Save_Excel_as_PDF()


ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, FileName:=Sheets("Great Rooms Report").Range("AI3").Text _


MsgBox "Your report has been saved as: " & Range("AI3").Value & "."


End Sub

Many Thanks
Gavin

<tbody>
</tbody>

 

[BrianM]

Slightly modified. Credit to www.contextures.com

Sub PDFActiveSheet()
'www.contextures.com
'for Excel 2010 and later
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

strPathFile = strPath & strFile

'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")

'export to PDF if a folder was selected
If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & myFile
End If

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub

 

[Gavin Harrison]

Hi Brian,

Thanks for the reply.

Is there anything I need to make specific as this is going straight to "could not create PDF file"

Thanks again.

 

[BrianM]

Gavin,
Are you trying to default the name of the .pdf to the contents of this "AI3"; or, do you want the person to manually enter the filename? Also, can you step through the code and tell me what last line of code is selected prior to selecting "errHandler"? It's kind hard to work through this without an actual representation of your file. I'm just using a blank file with one tab named "Great Rooms Report".

Brian

 

[Gavin Harrison]

Hi Brian.

Currently the file name is defaulted to the contents of cell AI3 which is essentially the week ending date, which is fine. But my original code 9/10 saves to the folder the file is located in, but sometimes doesn't.

This is why I would like the user to be able to specify where the file is saved so they can locate it.

Thanks
Gavin

 

[Gavin Harrison]

I have updated my original code to:

Sub Save_Excel_as_PDF()


strfile = ThisWorkbook.Path & "" & strfile
myfile = Application.GetSaveAsFilename _


ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, FileName:=Sheets("Great Rooms Report").Range("AI3").Text _


MsgBox "Your report has been saved to the selected folder named as: " & Range("AI3").Value & "."




End Sub


It seems so work, no idea what it means though haha.

Thanks for your help.