How do I format a number to have at least 5 digits in a file name with a macro? The Next CEO of Stack OverflowHow do I format/pad a number to have at least 5 digits?How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?VBA Print to PDF and Save with Automatic File NameHyperlink Columns = Excel MacroImport Word File name and data into Excel in a consistent formatPlace pdf in newly created folder with same nameSave as PDF and Excel 2016 Copy In Folder and Filename created from Cell ValueHow to save a file with the location and name as cell values using VBAAttach certain filesMacro failing to get correct PivotTable nameHow do I format/pad a number to have at least 5 digits?
Are there languages with no euphemisms?
How do I solve this limit?
The King's new dress
Inappropriate reference requests from Journal reviewers
What makes a siege story/plot interesting?
Science fiction (dystopian) short story set after WWIII
Natural language into sentence logic
Anatomically Correct Mesopelagic Aves
How do I construct this japanese bowl?
Grabbing quick drinks
What does this shorthand mean?
Robert Sheckley short story about vacation spots being overwhelmed
How do I go from 300 unfinished/half written blog posts, to published posts?
How to write the block matrix in LaTex?
Why didn't Khan get resurrected in the Genesis Explosion?
Is it safe to use c_str() on a temporary string?
How to start emacs in "nothing" mode (`fundamental-mode`)
Term for the "extreme-extension" version of a straw man fallacy?
How do we know the LHC results are robust?
Can the Reverse Gravity spell affect the Meteor Swarm spell?
What does "Its cash flow is deeply negative" mean?
Why do professional authors make "consistency" mistakes? And how to avoid them?
Only print output after finding pattern
How long to clear the 'suck zone' of a turbofan after start is initiated?
How do I format a number to have at least 5 digits in a file name with a macro?
The Next CEO of Stack OverflowHow do I format/pad a number to have at least 5 digits?How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?VBA Print to PDF and Save with Automatic File NameHyperlink Columns = Excel MacroImport Word File name and data into Excel in a consistent formatPlace pdf in newly created folder with same nameSave as PDF and Excel 2016 Copy In Folder and Filename created from Cell ValueHow to save a file with the location and name as cell values using VBAAttach certain filesMacro failing to get correct PivotTable nameHow do I format/pad a number to have at least 5 digits?
I'm working on a macro which involves generating a filename and saving an excel sheet as pdf with that name.
I was able to generate target folder name as follows.
user_name = Environ$("UserName")
file_dir = "C:Users" & user_name & "DocumentsJobs"
The file name is created as follows.
Job_No = Site & "SV" & num_from_cell
PDF = Job_No & ".pdf"
file_path = file_dir & PDF
Currently the above code returns MWSV234.pdf. I want it to be MWSV00234.pdf. num_from_cell comes from one of the cells in Excel sheet.
In short, I want to left pad num_from_cell to 5 digits. Could you please tell me how?
excel vba
edited Mar 7 at 14:10
Pᴇʜ
24.8k63052
asked Mar 7 at 13:59
Peet BosPeet Bos
212
add a comment |
I'm working on a macro which involves generating a filename and saving an excel sheet as pdf with that name.
I was able to generate target folder name as follows.
user_name = Environ$("UserName")
file_dir = "C:Users" & user_name & "DocumentsJobs"
The file name is created as follows.
Job_No = Site & "SV" & num_from_cell
PDF = Job_No & ".pdf"
file_path = file_dir & PDF
Currently the above code returns MWSV234.pdf. I want it to be MWSV00234.pdf. num_from_cell comes from one of the cells in Excel sheet.
In short, I want to left pad num_from_cell to 5 digits. Could you please tell me how?
excel vba
edited Mar 7 at 14:10
Pᴇʜ
24.8k63052
asked Mar 7 at 13:59
Peet BosPeet Bos
212
2
Job_No = Site & "SV" & Right("0000" & num_from_cell, 5)
– omegastripes
Mar 7 at 15:13
add a comment |
I'm working on a macro which involves generating a filename and saving an excel sheet as pdf with that name.
I was able to generate target folder name as follows.
user_name = Environ$("UserName")
file_dir = "C:Users" & user_name & "DocumentsJobs"
The file name is created as follows.
Job_No = Site & "SV" & num_from_cell
PDF = Job_No & ".pdf"
file_path = file_dir & PDF
Currently the above code returns MWSV234.pdf. I want it to be MWSV00234.pdf. num_from_cell comes from one of the cells in Excel sheet.
In short, I want to left pad num_from_cell to 5 digits. Could you please tell me how?
excel vba
edited Mar 7 at 14:10
Pᴇʜ
24.8k63052
asked Mar 7 at 13:59
Peet BosPeet Bos
212
I'm working on a macro which involves generating a filename and saving an excel sheet as pdf with that name.
I was able to generate target folder name as follows.
user_name = Environ$("UserName")
file_dir = "C:Users" & user_name & "DocumentsJobs"
The file name is created as follows.
Job_No = Site & "SV" & num_from_cell
PDF = Job_No & ".pdf"
file_path = file_dir & PDF
Currently the above code returns MWSV234.pdf. I want it to be MWSV00234.pdf. num_from_cell comes from one of the cells in Excel sheet.
In short, I want to left pad num_from_cell to 5 digits. Could you please tell me how?
excel vba
excel vba
edited Mar 7 at 14:10
Pᴇʜ
24.8k63052
asked Mar 7 at 13:59
Peet BosPeet Bos
212
edited Mar 7 at 14:10
Pᴇʜ
24.8k63052
asked Mar 7 at 13:59
Peet BosPeet Bos
212
edited Mar 7 at 14:10
Pᴇʜ
24.8k63052
edited Mar 7 at 14:10
Pᴇʜ
24.8k63052
edited Mar 7 at 14:10
Pᴇʜ
24.8k63052
24.8k63052
asked Mar 7 at 13:59
Peet BosPeet Bos
212
asked Mar 7 at 13:59
Peet BosPeet Bos
212
asked Mar 7 at 13:59
Peet BosPeet Bos
212
212
2
Job_No = Site & "SV" & Right("0000" & num_from_cell, 5)
– omegastripes
Mar 7 at 15:13
add a comment |
2
Job_No = Site & "SV" & Right("0000" & num_from_cell, 5)
– omegastripes
Mar 7 at 15:13
2
2
Job_No = Site & "SV" & Right("0000" & num_from_cell, 5)– omegastripes
Mar 7 at 15:13
Job_No = Site & "SV" & Right("0000" & num_from_cell, 5)– omegastripes
Mar 7 at 15:13
add a comment |
1 Answer
1
active
oldest
votes
It would be safer to use Environ$("HomePath") to get the users home directory rather than Environ$("UserName")
file_dir = Environ$("HomePath") & "DocumentsJobs"
Then use Format to format with leading zeros for 5 numbers
Job_No = Site & "SV" & Format(num_from_cell, "00000")
PDF = Job_No & ".pdf"
file_path = file_dir & PDF
answered Mar 7 at 14:04
TomTom
6,67511844
1
Environ$("HomePath")returns a single backslash at my (company) computer. I usually go forEnviron$("UserProfile")
– FunThomas
Mar 7 at 14:08
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%2f55045565%2fhow-do-i-format-a-number-to-have-at-least-5-digits-in-a-file-name-with-a-macro%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
It would be safer to use Environ$("HomePath") to get the users home directory rather than Environ$("UserName")
file_dir = Environ$("HomePath") & "DocumentsJobs"
Then use Format to format with leading zeros for 5 numbers
Job_No = Site & "SV" & Format(num_from_cell, "00000")
PDF = Job_No & ".pdf"
file_path = file_dir & PDF
answered Mar 7 at 14:04
TomTom
6,67511844
1
Environ$("HomePath")returns a single backslash at my (company) computer. I usually go forEnviron$("UserProfile")
– FunThomas
Mar 7 at 14:08
add a comment |
It would be safer to use Environ$("HomePath") to get the users home directory rather than Environ$("UserName")
file_dir = Environ$("HomePath") & "DocumentsJobs"
Then use Format to format with leading zeros for 5 numbers
Job_No = Site & "SV" & Format(num_from_cell, "00000")
PDF = Job_No & ".pdf"
file_path = file_dir & PDF
answered Mar 7 at 14:04
TomTom
6,67511844
1
Environ$("HomePath")returns a single backslash at my (company) computer. I usually go forEnviron$("UserProfile")
– FunThomas
Mar 7 at 14:08
add a comment |
It would be safer to use Environ$("HomePath") to get the users home directory rather than Environ$("UserName")
file_dir = Environ$("HomePath") & "DocumentsJobs"
Then use Format to format with leading zeros for 5 numbers
Job_No = Site & "SV" & Format(num_from_cell, "00000")
PDF = Job_No & ".pdf"
file_path = file_dir & PDF
answered Mar 7 at 14:04
TomTom
6,67511844
It would be safer to use Environ$("HomePath") to get the users home directory rather than Environ$("UserName")
file_dir = Environ$("HomePath") & "DocumentsJobs"
Then use Format to format with leading zeros for 5 numbers
Job_No = Site & "SV" & Format(num_from_cell, "00000")
PDF = Job_No & ".pdf"
file_path = file_dir & PDF
answered Mar 7 at 14:04
TomTom
6,67511844
answered Mar 7 at 14:04
TomTom
6,67511844
answered Mar 7 at 14:04
TomTom
6,67511844
answered Mar 7 at 14:04
TomTom
6,67511844
6,67511844
1
Environ$("HomePath")returns a single backslash at my (company) computer. I usually go forEnviron$("UserProfile")
– FunThomas
Mar 7 at 14:08
add a comment |
1
Environ$("HomePath")returns a single backslash at my (company) computer. I usually go forEnviron$("UserProfile")
– FunThomas
Mar 7 at 14:08
1
1
Environ$("HomePath") returns a single backslash at my (company) computer. I usually go for Environ$("UserProfile")– FunThomas
Mar 7 at 14:08
Environ$("HomePath") returns a single backslash at my (company) computer. I usually go for Environ$("UserProfile")– FunThomas
Mar 7 at 14:08
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%2f55045565%2fhow-do-i-format-a-number-to-have-at-least-5-digits-in-a-file-name-with-a-macro%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
2
Job_No = Site & "SV" & Right("0000" & num_from_cell, 5)– omegastripes
Mar 7 at 15:13