VBA code for "Save As" dialog box and PDF File format

[DAD]

Hi Ladies and Gents,

I am very new at VBA coding in excel, so please excuse my lack of technical literacy.

I am trying to write a simple VBA script in my excel spreadsheet and link it to a button so when pressed, the "Save As" dialog box opens and the "Format" defaults to PDF. That is the main objective of the script. I am also interested to know if it is possible to insert words (from two cells in the spreadsheet) into the "File Name" section of the "Save As" dialog box?

The reason I don't want to run an export as PDF script as I have need in other threads in the forum, is that many colleagues will be running this spreadsheet, and they need the ability to save the files wherever they desire on their machine.

I hope my query is clear and contains enough info.

Thanks in advance for any help.

 

[John_w]

Try this. The PDF file name is constructed from cells B4 and B5 on the first sheet.

Public Sub Save_Workbook_As_PDF()

    Dim i As Integer, PDFindex As Integer
    Dim PDFfileName As String
    
    With ActiveWorkbook
        PDFfileName = .Worksheets(1).Range("B4").Value & .Worksheets(1).Range("B5").Value & ".pdf"
    End With
    
    With Application.FileDialog(msoFileDialogSaveAs)
            
        PDFindex = 0
        For i = 1 To .Filters.Count
            If InStr(VBA.UCase(.Filters(i).Description), "PDF") > 0 Then PDFindex = i
        Next

        .Title = "Save workbook as PDF"
        .InitialFileName = PDFfileName
        .FilterIndex = PDFindex
        
        If .Show Then
            ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, fileName:=.SelectedItems(1), _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
        End If
    
    End With
    
End Sub

 

[DAD]

Hi John,

Thank you very much for the suggestion. I entered the code as shown above, just changed the data cells (B4 and B5), but unfortunately it didn't work. I had an error " Run-time error '438': Object doesn't support this property or method". The debugger highlighted the line "With Application.FileDialog(msoFileDialogSaveAs)" . Any more suggestions would be much appreciated.

 

[DAD]

Could I be missing something in my Excel setup? Is that why it didn't work perhaps?

 

[John_w]

It works on Excel 2007 and Excel 2016. I don't know why you're getting that error - have you searched for the error message along with the code causing it?

Here is an alternative:

Public Sub Save_Workbook_As_PDF2()

    Dim PDFfileName As String
    
    With ActiveWorkbook
        PDFfileName = .Worksheets(1).Range("B4").Value & .Worksheets(1).Range("B5").Value & ".pdf"
    End With

    With Application.FileDialog(msoFileDialogSaveAs)

        .Title = "Save workbook as PDF"
        .InitialFileName = ThisWorkbook.Path & "\" & PDFfileName
        
        If .Show Then
            ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, fileName:=.SelectedItems(1), _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End If
    
    End With
    
End Sub

 

[DAD]

Hi John, Thanks again for the reply. I must apologise, as I did not mention I am using Excel on Office for Mac, perhaps that is what the issue is. I neglected to mention it as I believed that the VBA coding side of it was identical to the Windows version, but perhaps I am very mistaken about that.

I tried your new code you suggested, and it came back with the same error on the "With Application.FileDialog(msoFileDialogSaveAs)" line. I will try to attack a screen grab of the debugger highlighting the code.

Again, my apologies for my lack of technical knowledge in this field. But again, if you have any suggestions on how to fix this it would be appreciated.

[/IMG]

 

[John_w]

I don't have any knowledge of VBA for Mac OSX, but I believe some parts of the Windows Excel object model are not supported on OSX, including Application.FileDialog.

If you search for something like "FileDialog(msoFileDialogSaveAs) mac" or "GetSaveAsFilename mac" you should find some solutions.

 

[mikerickson]

Try this. It worked on my Mac.

Sub Test()
    Application.Dialogs(xlDialogSaveAs).Show , 46
End Sub

 

[DAD]

Hi Mike, thanks very much for that, very simple and almost perfect. Do you know a way of inserting by default a file name in the "Save As" box, inserted from the String values from two different cells in the spreadsheet?

 

[mikerickson]

Sub Test()
    Application.Dialogs(xlDialogSaveAs).Show "myName" , 46
End Sub

https://msdn.microsoft.com/en-us/library/office/aa221648(v=office.11).aspx