Macro Loop Create Excel Files
Hello,
I have two columns of filepaths in excel. I would like to write a macro that will open the excel file listed in Column D (with a rowcounter) and save it as a new file with the name/path in Column E (same rowcounter). I would like to loop through the entire list of file paths to do this quickly.
Here is what I have so far. Can't seem to troubleshoot this though.
Please Login or Register to view this content.
Last edited by gsurge; 09-15-2011 at 04:29 PM.
Hello gsurge,
If all your files are on the same disk, you could use the Name As statement instead of opening and saving the files.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Can I use that even if the files have not been created? I simply have a series of templates that I need duplated various times and renamed
duplicated*
At this point i am creating the files for the first time with this macro (via save as)
Hello gsurge,
In that case, the answer would be no. The file must already exist. You can iterate through range easier by using a Range variable that represents the cells with data. Here is an example.
Please Login or Register to view this content.
Thanks Leith, but I receive the following error: "object variable or With block variable not set"
The debud highlights the following line:
xlapp.Workbooks.Open Filename:=template_file
Any idea??
Hello gsurge,
I see the problem. My mistake there is a typo in the For Next loop. Here is the corrected code.
Also I added a check for the "\" character in the file path.
Please Login or Register to view this content.
I actually corrected the typo, and still getting the same error on
xlapp.Workbooks.Open Filename:=template_file
Get rid of the "xlapp." bit?
Hello gsurge,
If you are running this macro outside of Excel then you need to set the variable xlapp to an instance of Excel. Here is an example.
Please Login or Register to view this content.
If you are running the macro from inside Excel then simply delete the xlapp Dim statement and prefix from your code. It would look this...
Please Login or Register to view this content.
Workbooks.Open Filename:=template_file is still giving me problems.
I did not add the xlapp but the macro cannot seem to recognize the file path referenced by template_file
Do you have any recommendations on why this is? I don't think its an issue with my filepath in the cell itself, and it is 100% spelled correctly and located in the folder.
Hello gsurge,
When the errors occurs, you can move your cursor over the variables in the statement and VBA will show you what they contain. Check the file path is what you have on the sheet and that the path is corrected formatted.
Thank you for all of the help Leith. I solved the previous issue, but now the macro will not continue in the loop. I added an ActiveWorkbook.Close command before "Next Cell", but the macro simply executes for the first cell in the range, but doesnt continue. Any thoughts?
Hello gsurge,
I set the macro to look down from the starting until it found a blank cell. Not the way I usually do it but I thought perhaps the data was all unbroken. This method ill find the cell in the column that contains data. The macro will need to skip blanks if they are encounterd. Try this macro and let me know.
Please Login or Register to view this content.
it is not broken, a consistent list in Column D
figured it out...the following code worked. Thank you Leith for all of your help. You are a life saver
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks