VBA Dynamic save File
I have a code where I am going to save my file. But here the path is static. How to make it dynamic path so that anybody in any system can save this file.
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
I just want how to replace this static or hardcode with a dynamic path?
excel vba excel-vba
asked Nov 23 at 7:38
Shaon
297
add a comment |
I have a code where I am going to save my file. But here the path is static. How to make it dynamic path so that anybody in any system can save this file.
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
I just want how to replace this static or hardcode with a dynamic path?
excel vba excel-vba
asked Nov 23 at 7:38
Shaon
297
In that case, you may have to ask the user, the path to save the file.
– shahkalpesh
Nov 23 at 7:40
But how to write the code behind?
– Shaon
Nov 23 at 7:45
would whis work for you? Application.Dialogs(xlDialogSaveAs).Show filename
– W_O_L_F
Nov 23 at 7:56
Are you trying to save a workbook as a JPG? This cannot work. Please tell what your actual goal is.
– Pᴇʜ
Nov 23 at 8:06
add a comment |
I have a code where I am going to save my file. But here the path is static. How to make it dynamic path so that anybody in any system can save this file.
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
I just want how to replace this static or hardcode with a dynamic path?
excel vba excel-vba
asked Nov 23 at 7:38
Shaon
297
I have a code where I am going to save my file. But here the path is static. How to make it dynamic path so that anybody in any system can save this file.
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
I just want how to replace this static or hardcode with a dynamic path?
excel vba excel-vba
excel vba excel-vba
asked Nov 23 at 7:38
Shaon
297
asked Nov 23 at 7:38
Shaon
297
asked Nov 23 at 7:38
Shaon
297
asked Nov 23 at 7:38
Shaon
297
asked Nov 23 at 7:38
Shaon
297
297
In that case, you may have to ask the user, the path to save the file.
– shahkalpesh
Nov 23 at 7:40
But how to write the code behind?
– Shaon
Nov 23 at 7:45
would whis work for you? Application.Dialogs(xlDialogSaveAs).Show filename
– W_O_L_F
Nov 23 at 7:56
Are you trying to save a workbook as a JPG? This cannot work. Please tell what your actual goal is.
– Pᴇʜ
Nov 23 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 at 7:40
But how to write the code behind?
– Shaon
Nov 23 at 7:45
would whis work for you? Application.Dialogs(xlDialogSaveAs).Show filename
– W_O_L_F
Nov 23 at 7:56
Are you trying to save a workbook as a JPG? This cannot work. Please tell what your actual goal is.
– Pᴇʜ
Nov 23 at 8:06
In that case, you may have to ask the user, the path to save the file.
– shahkalpesh
Nov 23 at 7:40
In that case, you may have to ask the user, the path to save the file.
– shahkalpesh
Nov 23 at 7:40
But how to write the code behind?
– Shaon
Nov 23 at 7:45
But how to write the code behind?
– Shaon
Nov 23 at 7:45
would whis work for you? Application.Dialogs(xlDialogSaveAs).Show filename
– W_O_L_F
Nov 23 at 7:56
would whis work for you? Application.Dialogs(xlDialogSaveAs).Show filename
– W_O_L_F
Nov 23 at 7:56
Are you trying to save a workbook as a JPG? This cannot work. Please tell what your actual goal is.
– Pᴇʜ
Nov 23 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 at 8:06
add a comment |
2 Answers
2
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 at 8:18
Harun24HR
3,7062717
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 at 8:24
Wolfaloo
7211
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%2fvba-dynamic-save-file%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
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 at 8:18
Harun24HR
3,7062717
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 at 8:18
Harun24HR
3,7062717
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 at 8:18
Harun24HR
3,7062717
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 at 8:18
Harun24HR
3,7062717
answered Nov 23 at 8:18
Harun24HR
3,7062717
answered Nov 23 at 8:18
Harun24HR
3,7062717
answered Nov 23 at 8:18
Harun24HR
3,7062717
3,7062717
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 at 8:24
Wolfaloo
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 at 8:24
Wolfaloo
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 at 8:24
Wolfaloo
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 at 8:24
Wolfaloo
7211
edited Nov 23 at 9:12
answered Nov 23 at 8:24
Wolfaloo
7211
answered Nov 23 at 8:24
Wolfaloo
7211
answered Nov 23 at 8:24
Wolfaloo
7211
7211
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2fvba-dynamic-save-file%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 at 7:40
But how to write the code behind?
– Shaon
Nov 23 at 7:45
would whis work for you? Application.Dialogs(xlDialogSaveAs).Show filename
– W_O_L_F
Nov 23 at 7:56
Are you trying to save a workbook as a JPG? This cannot work. Please tell what your actual goal is.
– Pᴇʜ
Nov 23 at 8:06