Can I make the document generated in the VBA code below be saved in the source file folder or that it asks to save in a specific location?

Question

I am creating a macro in Excel that selects a text and pastes in another tab of the spreadsheet and generates a . pdf automatically.

I want to try to make the user on another computer save this file or in the same path that the source sheet will be in or else appear to him the option to save. (I would like the first option because if he only has the second option he will have to do it repeatedly.

I can make the document generated in the VBA code below be saved in the source file folder or that it asks to save in a specific location?

Sub imprimirAG2P()
'
' imprimirAG2P Macro
' imprimir AG 2P
'

'
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "AG1 2P"
    Sheets("MEC - 111 - AGITATOR").Select
    Range("G7").Select
    ActiveCell.FormulaR1C1 = "AG1 2P"
    Range("G8").Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "D:\00 - MEUS DOCUMENTOS\DOCUMENTOS\01 - DOC CONTROL\01 - FVI - FVM\01 - FVI\03 - FVI PRONTAS\FVI\AG1 2P.pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    Sheets("databank").Select
End Sub

Answer 1

Hi, Marcus!

To meet your option just use the property Activeworkbook.Path, which is the attribute that says where the spreadsheet is saved (from the premise that it is not a file not yet saved)

So:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        Activeworkbook.path & "\AG1 2P.pdf" _ 

If you want to give the user a prompt you use:

Dim f As Object
Set f = Application.FileDialog(msoFileDialogFolderPicker)

f.InitialFileName = ActiveWorkbook.Path
f.AllowMultiSelect = False
f.Show

MsgBox f.SelectedItems(1)

The difference is that you will have to treat a little what will appear, to protect yourself from empty selection ( if f.Selecteditems(1) <> "" then...) and etc

But the general logic is this

Answer 2

I discovered here that if I take the "Path" of the file as for example "D: OTHER HD" and leave only the file name: AG1 2P.pdf, it automatically saves in "C: user so-and-so Documents"

´´´
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "AG1 2P.pdf" _ 
´´´

It doesn’t quite solve the problem but it already helps.

Now all that remains is whether I can get the document to be saved to the source file folder or to be saved to a specific location.