VBA Export PDF Help

[Spaceman88]

Hey Everyone

I'm very new to VBA used excel a fair bit and can record Marcos ect... the easy stuff but I need to create a button the selects a range and then exports it to a PDF. Obviously you can do this just recording a macro but every time I do this it exports the range to a particular path and filename.... What I want the user to be able to do is select there own path and filename essentially so it stays at the export save screen. I've tried so many ways and just get errors don't really know what I'm doing lol. Even asked ChatGTP! I've seen alot of videos using the SaveAs Function that can even be customised, this would be great but as far as i can tell this doesn't work for what I am looking for! any help would be great.

TIA

[June7]

Post the code you have now.

[Spaceman88]

' Macro12 Macro
    Sheets("Sheet1").Select
    Range("A1:X40").Select
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\craig\OneDrive - Van Elle\Desktop\1231.pdf", Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
End Sub

[June7]

Try this:

Sub RangeToPDF()
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    GetFolder() & "\" & InputBox("Enter text to use as file name without extension", "Enter text", "MyFileName") & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub


Function GetFolder() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
    NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function

[Spaceman88]

hi this works wondered if it could be 1 single window instead of 2? (like the standard save or export window) also if you press cancel error appears is there anyway this not showing up. Thanks so much for your reply

[Logit]

This question has been posted on two additional forums. The OP should check those other forums for the answer he seeks.

Also in the future, when posting the same question to more than one Forum it is a requirement of all forums that you also provide links to those other locations where your question has been posted.

[June7]

AFAIK, export cannot select folder and allow file name input in one window. That would be a SaveAs operation and doesn't seem that SaveAs can save just a range.

As for the Cancel, pull calling the file dialog function out of the Export command and set a variable. Use an If Then condition to determine if variable has value. Use that variable in the Export command

strFile = GetFolder()
If strFile <> "" Then
    'code to export
End If

[Logit]

June7 : The answer to his question is posted on two other forums. Just so you know.

[georgiboy]

Hi Spaceman88,

Welcome to the forum, if you have posted the same question on multiple forums then it may be worth taking a look at the below link. The rules here are the same as most of he other forums, take a read of the below otherwise you may have issues on all of the forums you frequent.

https://excelguru.ca/a-message-to-forum-cross-posters/

Regards

George