Save file using a dynamic path

Question

I have code to save a file. Here the path is static.

Sub savefile()

Dim strpath As String
Dim fry As String
Dim mth As String
Dim yr As String

yr = Year(Now)
mth = MonthName(Month(Now))
fry = Application.WorksheetFunction.Weekday(Date, 11)

Filename = "D:\Users\Desktop\ docs\" & yr & " " & "Week Of" & " " & mth & " " & fry & ".jpg"

ThisWorkbook.SaveCopyAs (Filename)

End Sub

How to replace this static or hardcode with a dynamic path?

In that case, you may have to ask the user, the path to save the file. — shahkalpesh, Nov 23 '18 at 07:40
would whis work for you? Application.Dialogs(xlDialogSaveAs).Show filename — W_O_L_F, Nov 23 '18 at 07:56
Are you trying to save a workbook as a JPG? This cannot work. Please tell what your actual goal is. — Pᴇʜ, Nov 23 '18 at 08:06

score 0 · Answer 1 · answered Nov 23 '18 at 08:18

Try below sub

Sub SaveFileAs()
Dim strpath As String
Dim fry As String
Dim mth As String
Dim yr As String

    yr = Year(Now)
    mth = MonthName(Month(Now))
    fry = Application.WorksheetFunction.Weekday(Date, 11)

    Filename = yr & " " & "Week Of" & " " & mth & " " & fry

Application.Dialogs(xlDialogSaveAs).Show (Filename)
End Sub

Wolfaloo · Answer 2 · 2018-11-23T09:12:48.843

The same way you have done with the year, you can allow your user to input data for the path into a variable that is a string and then use & to put the entire path togheter.

example:

Sub savefile()

Dim strpath As String
Dim fry As String
Dim mth As String
Dim yr As String
Dim path As String

yr = Year(Now)
mth = MonthName(Month(Now))
fry = Application.WorksheetFunction.Weekday(Date, 11)    

Filename = yr & " " & "Week Of" & " " & mth & " " & fry & ".jpg"
Path = Application.DefaultFilePath & yr & " " & "Week Of" & " " & mth & " " & fry & ".jpg"

Application.Dialogs(xlDialogSaveAs).Show (Filename)
ThisWorkbook.SaveCopyAs (Path)


End Sub

If you want your user to input the path directly you can use the application.dialogs(xlDialogSaveAs).Show (Filename)

If you want the program to save all the files in a predefined path that can altho change depending on the structure of the computer you are using you could use the Application.DefaultFilePath that would save to documents in my case, depending on which folder the user has set up as it's default savefile path it will save there.

NOTE: the default saving location can be changed and it will be where Excel saves files by default.

Wizhi · Answer 3 · 2019-01-09T22:34:18.580

0

If you share the same network drive the path should be stated as an UNC Path, for example: (\\?\C:\my_dir).

To find the UNC path, use the cmd.exe (command prompt) and write net use.

edited Jan 09 '19 at 22:34

answered Jan 09 '19 at 14:37

Wizhi

6,424
4
25
47

Save file using a dynamic path

3 Answers3