Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Application.FileDialog(msoFileDialogSaveAs) with .pdf as filter

Tags:

excel

vba

I want the user to select the path and the file name of a PDF file that they are exporting.

I created the below to display the SaveAs dialog:

Sub Test3()

    Set myFile = Application.FileDialog(msoFileDialogSaveAs)
    With myFile
        .Title = "Choose File"
        .AllowMultiSelect = False
        .ButtonName = "&Save As"
        .initialFilename = "Export"
        .Title = "Save PDF as"
        If .Show <> -1 Then
            MsgBox "You have cancelled"
            Exit Sub
        End If
        FileSelected = .SelectedItems(1)
    End With

End Sub

The value of FileSelected is later fed into the pdf export code.

I want to add a filter to the above FileSave dialog so that it only shows PDF as the file save type.

Currently the value of FileSelected ends up being "Driveletter:/Folder/Name.xlsm" so when I feed it into the pdf export it saves the pdf with xlsm extension.

I tried .Filter.Clear and then .Filters.Add.

like image 239
CaptainABC Avatar asked Jan 24 '14 14:01

CaptainABC


2 Answers

Never mind I figured it out:

Sub Test3()

On Error Resume Next
Dim FileSelected As String

FileSelected = Application.GetSaveAsFilename(initialFilename:="Export", _
                                         FileFilter:="PDF Files (*.pdf), *.pdf", _
                                         Title:="Save PDF as")
If Not FileSelected <> "False" Then
MsgBox "You have cancelled"
Exit Sub
End If

If FileSelected <> "False" Then
MsgBox FileSelected 'where FileSelected is what I will be
                    'later feeding into the PDF Export Code
Exit Sub
End If

End Sub
like image 111
CaptainABC Avatar answered Sep 30 '22 18:09

CaptainABC


I added a loop within the option list to run through the file types and select the one I wanted. It has its faults, but works...

For I = 1 To .Filters.Count
    fName = .Filters(I).Description
    If fName = "PDF" Then
        .FilterIndex = I
        Exit For
    End If
Next
like image 34
Trex Avatar answered Sep 30 '22 18:09

Trex