Save PDF - Select file location

**ZeDoctor** · 01-18-2013, 10:51 AM

Hi Guys,

I already have a code below which perfectly to save an excel sheet as a pdf file. But the problem is it saves it in a set location as specified in the code, what I want to achieve is to be able to bring up a promt box to select the location to save this PDF to. I'm sure it is a simple code edit, but I am fairly useless when it comes to coding and couldn't find it anywhere on the internet (perhaps due to rubbish search terms).

Any help is very much appreciated.

Worksheets("PDF2").Visible = True

Sheets("PDF2").Activate



    ThisFile = Range("O6").Value
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        ThisFile, Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
         
            Sheets("FOBs").Activate
    Worksheets("PDF2").Visible = False

**hulpeloos** · 01-18-2013, 11:27 AM

The below will prompt you for a location selection and as in your example it will save the file with the text thats in cell 06 of the currently active sheet

Sub location()
Dim objShell As Object
Dim objFolder As Object
Dim strFolderFullPath As String

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder(0, "Please select a folder", 0)

If (Not objFolder Is Nothing) Then
    On Error Resume Next
    If IsError(objFolder.Items.Item.Path) Then strFolderFullPath = CStr(objFolder): GoTo Here
    On Error GoTo 0
    If Len(objFolder.Items.Item.Path) > 3 Then
        strFolderFullPath = objFolder.Items.Item.Path & Application.PathSeparator
    Else
        strFolderFullPath = objFolder.Items.Item.Path
    End If

Here:
MsgBox "Your selected location : " & strFolderFullPath, vbInformation, "ObjectFolder:= " & objFolder
Set objFolder = Nothing
Set objShell = Nothing
Dim MyLocation$
    MyLocation = strFolderFullPath
If Left(MyLocation, 1) <> "\" Then
Directory = MyLocation & "\"
End If
ThisFile = MyLocation & ActiveSheet.Range("O6").Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=ThisFile, Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        
Canceling:
End If
End Sub