Save file using a dynamic path
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:UsersDesktop docs" & yr & " " & "Week Of" & " " & mth & " " & fry & ".jpg"
ThisWorkbook.SaveCopyAs (Filename)
End Sub
How to replace this static or hardcode with a dynamic path?
excel vba
edited Jan 9 at 17:00
Community♦
11
asked Nov 23 '18 at 7:38
ShaonShaon
609
add a comment |
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:UsersDesktop docs" & yr & " " & "Week Of" & " " & mth & " " & fry & ".jpg"
ThisWorkbook.SaveCopyAs (Filename)
End Sub
How to replace this static or hardcode with a dynamic path?
excel vba
edited Jan 9 at 17:00
Community♦
11
asked Nov 23 '18 at 7:38
ShaonShaon
609
In that case, you may have to ask the user, the path to save the file.
– shahkalpesh
Nov 23 '18 at 7:40
But how to write the code behind?
– Shaon
Nov 23 '18 at 7:45
would whis work for you? Application.Dialogs(xlDialogSaveAs).Show filename
– W_O_L_F
Nov 23 '18 at 7:56
1
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 8:06
add a comment |
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:UsersDesktop docs" & yr & " " & "Week Of" & " " & mth & " " & fry & ".jpg"
ThisWorkbook.SaveCopyAs (Filename)
End Sub
How to replace this static or hardcode with a dynamic path?
excel vba
edited Jan 9 at 17:00
Community♦
11
asked Nov 23 '18 at 7:38
ShaonShaon
609
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:UsersDesktop docs" & yr & " " & "Week Of" & " " & mth & " " & fry & ".jpg"
ThisWorkbook.SaveCopyAs (Filename)
End Sub
How to replace this static or hardcode with a dynamic path?
excel vba
excel vba
edited Jan 9 at 17:00
Community♦
11
asked Nov 23 '18 at 7:38
ShaonShaon
609
edited Jan 9 at 17:00
Community♦
11
asked Nov 23 '18 at 7:38
ShaonShaon
609
edited Jan 9 at 17:00
Community♦
11
edited Jan 9 at 17:00
Community♦
11
edited Jan 9 at 17:00
Community♦
11
11
asked Nov 23 '18 at 7:38
ShaonShaon
609
asked Nov 23 '18 at 7:38
ShaonShaon
609
asked Nov 23 '18 at 7:38
ShaonShaon
609
609
In that case, you may have to ask the user, the path to save the file.
– shahkalpesh
Nov 23 '18 at 7:40
But how to write the code behind?
– Shaon
Nov 23 '18 at 7:45
would whis work for you? Application.Dialogs(xlDialogSaveAs).Show filename
– W_O_L_F
Nov 23 '18 at 7:56
1
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 8:06
add a comment |
In that case, you may have to ask the user, the path to save the file.
– shahkalpesh
Nov 23 '18 at 7:40
But how to write the code behind?
– Shaon
Nov 23 '18 at 7:45
would whis work for you? Application.Dialogs(xlDialogSaveAs).Show filename
– W_O_L_F
Nov 23 '18 at 7:56
1
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 8:06
In that case, you may have to ask the user, the path to save the file.
– shahkalpesh
Nov 23 '18 at 7:40
In that case, you may have to ask the user, the path to save the file.
– shahkalpesh
Nov 23 '18 at 7:40
But how to write the code behind?
– Shaon
Nov 23 '18 at 7:45
But how to write the code behind?
– Shaon
Nov 23 '18 at 7:45
would whis work for you? Application.Dialogs(xlDialogSaveAs).Show filename
– W_O_L_F
Nov 23 '18 at 7:56
would whis work for you? Application.Dialogs(xlDialogSaveAs).Show filename
– W_O_L_F
Nov 23 '18 at 7:56
1
1
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 8:06
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 8:06
add a comment |
3 Answers
3
active
oldest
votes
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
answered Nov 23 '18 at 8:18
Harun24HRHarun24HR
4,1382721
add a comment |
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.
answered Nov 23 '18 at 8:24
WolfalooWolfaloo
7211
add a comment |
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.
answered Jan 9 at 14:37
WizhiWizhi
3,44411131
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53442461%2fsave-file-using-a-dynamic-path%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Nov 23 '18 at 8:18
Harun24HRHarun24HR
4,1382721
add a comment |
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
answered Nov 23 '18 at 8:18
Harun24HRHarun24HR
4,1382721
add a comment |
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
answered Nov 23 '18 at 8:18
Harun24HRHarun24HR
4,1382721
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
answered Nov 23 '18 at 8:18
Harun24HRHarun24HR
4,1382721
answered Nov 23 '18 at 8:18
Harun24HRHarun24HR
4,1382721
answered Nov 23 '18 at 8:18
Harun24HRHarun24HR
4,1382721
answered Nov 23 '18 at 8:18
Harun24HRHarun24HR
4,1382721
4,1382721
add a comment |
add a comment |
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.
answered Nov 23 '18 at 8:24
WolfalooWolfaloo
7211
add a comment |
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.
answered Nov 23 '18 at 8:24
WolfalooWolfaloo
7211
add a comment |
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.
answered Nov 23 '18 at 8:24
WolfalooWolfaloo
7211
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.
answered Nov 23 '18 at 8:24
WolfalooWolfaloo
7211
edited Nov 23 '18 at 9:12
answered Nov 23 '18 at 8:24
WolfalooWolfaloo
7211
answered Nov 23 '18 at 8:24
WolfalooWolfaloo
7211
answered Nov 23 '18 at 8:24
WolfalooWolfaloo
7211
7211
add a comment |
add a comment |
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.
answered Jan 9 at 14:37
WizhiWizhi
3,44411131
add a comment |
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.
answered Jan 9 at 14:37
WizhiWizhi
3,44411131
add a comment |
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.
answered Jan 9 at 14:37
WizhiWizhi
3,44411131
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.
answered Jan 9 at 14:37
WizhiWizhi
3,44411131
edited Jan 9 at 22:34
answered Jan 9 at 14:37
WizhiWizhi
3,44411131
answered Jan 9 at 14:37
WizhiWizhi
3,44411131
answered Jan 9 at 14:37
WizhiWizhi
3,44411131
3,44411131
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53442461%2fsave-file-using-a-dynamic-path%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
In that case, you may have to ask the user, the path to save the file.
– shahkalpesh
Nov 23 '18 at 7:40
But how to write the code behind?
– Shaon
Nov 23 '18 at 7:45
would whis work for you? Application.Dialogs(xlDialogSaveAs).Show filename
– W_O_L_F
Nov 23 '18 at 7:56
1
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 8:06