Software: Excel 2010OS: Windows 2007 Home Premium 64bitIntel i7 CPU w/ 9GB RAMLooking for a coded macro solution.Problem: Our company uses a simple Excel spreadsheet to enter in new basic info for new Estimates and Jobs (Job Number, Client Name, Job Name and Production Company)Estimate folders and sub-folders are created automatically. The names of each begins with the EstimateNumber.When an estimate turns into a job, I would like a macro to go and replace the estimate number at the beginning of that specific folder and it's sub-folders with the new job number and also add the job number to each file that has been created in the folder and sub-folders.I imagine this would be accomplished by by SELECTING the estimate number on a spread sheet and clicking a "Convert to Job button" (which would also change the selected cell to the value in the NextJobNum.txt file - this last part is already scripted.More Detail: Our Estimate folders are created like this (always with a decreasing negative number, assuming an Estimate Number of -3432)C:\Jobs\Est No. - Client - Product - Who Pays the billsC:\Jobs\-3432 - XXXXX XXXXX - PEPSI COLA - UPSTREAM PRODUCTIONSwith subfloders:C:\Jobs\010 - -3432 - CLIENT INFO - PEPSI COLA - XXXXX XXXXXC:\Jobs\010 - -3432 - CLIENT INFO - PEPSI COLA - XXXXX XXXXX\-3432 PRODUCTION NOTES\Schedule.txtC:\Jobs\010 - -3432 - CLIENT INFO - PEPSI COLA - XXXXX XXXXX\-3432 PRODUCTION NOTES\Job Description.txtC:\Jobs\020 - -3432 - RENDERINGS -PEPSI COLA - XXXXX XXXXXC:\Jobs\020 - -3432 - RENDERINGS -PEPSI COLA - XXXXX XXXXX\-3432 RENDER FILES-A\Client renderings.pdfC:\Jobs\020 - -3432 - RENDERINGS -PEPSI COLA - XXXXX XXXXX\-3432 RENDER FILES-B\millions of pics.jpgC:\Jobs\030 - -3432 - SHOP DRAW - PEPSI COLA - XXXXX XXXXXand so on...Each folder holds various files of job information We would like them to read when finished (assuming the NextJobNumber located in NextJobNum.txt is 8000):C:\Jobs\8000 - XXXXX XXXXX - PEPSI COLA - UPSTREAM PRODUCTIONSwith subfloders:C:\Jobs\010 - 8000 - CLIENT INFO - PEPSI COLA - XXXXX XXXXXC:\Jobs\010 - 8000 - CLIENT INFO - PEPSI COLA - XXXXX XXXXX\8000 PRODUCTION NOTES\8000 - Schedule.txtC:\Jobs\010 - 8000 - CLIENT INFO - PEPSI COLA - XXXXX XXXXX\8000 PRODUCTION NOTES\8000 - Job Description.txtC:\Jobs\020 - 8000 - RENDERINGS -PEPSI COLA - XXXXX XXXXXC:\Jobs\020 - 8000 - RENDERINGS -PEPSI COLA - XXXXX XXXXX\8000 RENDER FILES-A\8000 - Client renderings.pdfC:\Jobs\020 - 8000 - RENDERINGS -PEPSI COLA - XXXXX XXXXX\8000 RENDER FILES-A\8000 - million of pics.jpgC:\Jobs\030 - 8000 - SHOP DRAW - PEPSI COLA - XXXXX XXXXXIt is possible someone may have a file open when this macro is run - perhaps a warning with a delayed renaming try at 1AM?Or a warning on who has what file open and we can tell them to shut it?Thanks. Questions? I can send my current code that does everything I need except what is described above.
File ID: 439264
In the spreadsheet the user will enter 3 pieces of information in cells E4, E5 and E6.
Then they press a button, either New Job or New Estimate.
At this point the next job or estimate number is XXXXX and the information is copied down to the row below, allowing the next entry.
If the user selects one or more CLIENTS and presses the "Make Folders" button, folders and subfolders are automatically created with their corresponding job or estimate number. At this point the entire office will begin to fill the folders with information regarding the ESTIMATE.
Here's where you come in, if the job is awarded, the ESTIMATE files and folders need to have the ESTIMATE number removed and replaced with the job number.
It all sounds complicated but it's pretty straight forward.
We would look at the row selected and use the number in column D as the ESTIMATE number then read the number in InvNum.txt as the replacement number - then the number in InvNum.txt should be increased by 1 and saved. (Sub WriteInvoiceNumber() does this BUT is hard coaded to overwrite cell (4,4) or D4 when, for this case it should be the selected row and column #4. In addition, WriteInvoiceNumber also calls Sub InvNumAvailable which has a line or two that will copy row 4 and move it down one row - we don't want these lines for what we are about to do.
Then we can start the renaming of folders and sub folders beginning with the Estimate number. I have started this code as sub FinalConvert() where I have selected the Estimate Number (current row, column #4) but have not changed it to the number in InvNum.txt (increase the text file by one and re-save it)
If you look at the Sub FinalConvert() file, where it says
======== WORKING HERER ==========
I have tried my very first attempt and changing a folder name
Also, we will have to look at the files created under the same folders and simply add the InvNum (which is synonymous with JobNo) beginning of each file name.
I appreciate your help.
If you could be conscious of error handling as you look through my entire code, perhaps we could work something out to write this code as well.
Yes - "c:\1234Excel.xlsx" As "c:\5678Excel.xlsx" is in my first attempt under the Sub FinalConvert()
Can I call a sub while in the middle of a sub? Like gosub and return?
Regarding "c:\1234Excel.xlsx" As "c:\5678Excel.xlsx"
Assuming we have a specific Folder with multiple branching sub-folders, what might the code look like to append the InvNum to the beginning of each?
I can replace an individual file or folder no problem but I'm looking for help automating the process of all folders and files without knowing what they are called.
Assuming we have a specific Folder with multiple branching sub-folders, what might the code look like to append the InvNum to the beginning of each? How can I append to all *.* the files with the least amount of code?
I won't know what the files are called.
The same is true with the folders - I will know the names of the folders created automatically, but the users will create folders as they require.
Exactly - that's part of the help I'm looking for.
How could we append a string in InvNum to the beginning of any and all files (*.*) under a know folder that extends to all of it's sub folders?
What code might append InvNum to the beginning of ALL folders and sub folders under a known directory?
I'm not going to look up every file and folder and perform a single command to each, but the script should. Does that make sense?
That looks closer to what I was looking for. Let me work with that for a moment.
Regarding the start folder, I'm developing this code for the first folder:
I'm trying to make sure the directory exists with the EstNo and IF it does then go ahead and start changing all the folder names.
If Dir(Root & "\" & Client & "\" & EstNo & " - " & Product & " - " & Client & " - " & ProdCo & "\") = "" Then MkDir Root & "\" & Client & "\" & JobNo & " - " & Product & " - " & Client & " - " & ProdCo & "\"End If
Where Root is hard-coded as a variable
And the following I'll need to revise to be selected row along with the specific Columns
JobNo = rng(r, c - 1)Client = rng(r, c + 10)Product = rng(r, c + 11)ProdCo = rng(r, c + 12)
Thanks Richie -
I'm trying to run your code on it's own but I must be missing something?
according to your code:
FoldersArray = funcGetSubfolders("C:\testt")
I've created a folder C:\testt with a number of subfolders and files.
Can I run this on it's own? I get a "Path/File access error". I have none of the folders or files open.
In your first few lines you talk about a new document, do you mean a document in memory or a text document? Need I do or provide something here?
I can't get past the "Path/File access error"
I can clearly access the path I'm entering and I can create and change files via my macros.
Could there be a module that you're using that I must grant access to or authorize?
I really am trying hard to see your code work. I've closed all my applications and opened only your script. Tried various directories. Same error. I try my script for creating directories and it works fine.
Thanks Richie -
Yes. Correct. I have downloaded your file twice and run only that file.
I have directed the line:
FoldersArray = funcGetSubfolders("C:\testt\")
to a folder by that name and path,
and I have directed it to:
FoldersArray = funcGetSubfolders("C:\Jobs\Brendan McDear")
FoldersArray = funcGetSubfolders("C:\Jobs\Brendan McDear\")
Both folders exist with sub folders and files
Here's a screen grab of the error and the directory that exists.
Yes, I tried it with and without a "\" at the end. No luck either way.
I'll keep the "\" off the end in the future.
Richie - that solves the error.
Now it runs with no errors but does not seem to do anything. No changes to files or folders under C:\testt
Which part should I look at to append (or is it pre-pend?) to the beginning of the Folder names?
I can't seem to see where I'll add my JobNum to append to the beginning of the folders - for that matter I can't append anywhere.
I've tried the following:
Name FoldersArray(i) As JobNum & FoldersArray(i)
Name FoldersArray(i) As FoldersArray(i) & "2020"
Gives the Access Error
Name FoldersArray(i) As "2020" & FoldersArray(i)
Invalid procedure call in argument
Name FoldersArray(i) As FoldersArray & "2020" & (i)
Type mismatch error
What happens when you run the exact script you gave me?
did you run this? what does it do on your computer?
On mine it replaces what ever folder I type into the:
FoldersArray = funcGetSubfolders("C:\test")
so C:\test becomes C:\testh but no sub folders are touched.
and then a "file not found error"
A couple of things, I need the 'h' to go at the beginning of the folder name.
I fell we are getting closer.
I'll be at work all day, but will check in in the evening.
For me, it would only change the root folder, then error. I could run a second time but it would still error.
I could change the folder back to "test" and run again, it changes "test" to testh" then errors.
I think if it ends up working for you, it should work for me.
I'll upload my test folders (they are very simple):
Wikisend: File ID: 127600
I’m Rachel, and I’m moderator for this topic. It seems the professional has left this conversation. This happens occasionally, and it's usually because the professional thinks that someone else might be a better match for your question. I've been working hard to find a new professional to assist you right away, but sometimes finding the right professional can take a little longer than expected.
I was checking to see if you had already found your answer or if you still needing assistance from another one of the professionals? Please let me know if you wish to continue waiting or if you would like for us to close your question. Also remember that JustAnswer has a multitude of categories to help you with all your needs from Health, Pets, Computers, Taxes, Cars, Finance, Law, to Home Improvement, and more.
Rachel - I'd like to close the question at this time. If I can't find the answer by next weekend, I'll try again later.
Thanks for the attempt.