location to save pdf

[robertvdb]

I have a workbook which is being used by different users, but on a central network PC. There is a macro which saves specific Ranges as a PDF.

Depending on which user, the pdf needs to be saved in different directories.

Example: when Bob needs to save a PDF, the location is C:\Documents\BobFiles
for John it will be C:\Documents\JohnFiles

and so on.

Is there any way where John and Bob can indicate their location PRIOR to saving, so that they do not save their files at the wrong place ?

 

[HaHoBe]

Hi robertvdb,

maybe use

"C:\Documents\" & Environ("Username") & "Files"

or

Environ("USERPROFILE")

which will return "C:\Users\Holger" and could be amended with "Files".

Ciao,
Holger

 

[cspengel]

You could add the following code at beginning of macro:

Dim saveDir As String
saveDir = InputBox("Enter the location to save the PDF:", "Save Location")

That will prompt user to enter location. You could then use that variable to construct full file path where pdf should be saved.

Dim pdfFile As String
pdfFile = saveDir & "\" & "MyPDF.pdf"

or something like that..

 

[robertvdb]

Thanks cspengel

two remarks:

1) how to select saveDir through a File Dialog box ?
2) I would like to keep the saveDir value throughout the project, so to also use it in other Subs in the project.

Thanks

 

[HaHoBe]

Hi Robert,

maybe

Sub MrE_1226506_1700A12()
Dim strPathFolder As String

With Application.FileDialog(msoFileDialogFolderPicker)
  If .Show = -1 Then strPathFolder = .SelectedItems(1)
End With

MsgBox strPathFolder
End Sub

If you want to use the variable elsewhere you could declare strPathFolder as a flobal variable at the top of a standard module like

Public g_strPathFolder As String
'

Sub MrE_1226506_1700A12()

With Application.FileDialog(msoFileDialogFolderPicker)
  If .Show = -1 Then g_strPathFolder = .SelectedItems(1)
End With

MsgBox g_strPathFolder
End Sub

You should make sure by checking that the variable holds a value before applying it elsewgere.

Holger

 

[robertvdb]

Thanks Holger, I'll test it out...

 

[robertvdb]

Sorry to keep you waiting, Holger. Your code works fine.

Thanks, Ralph

 

[HaHoBe]

Hi robert,

thanks for the feedback, glad we could help here.

Holger