Script or similar to create hundreds of cutomised Excel files

I need to create several hundred customised Excel files.

I have a template file with a bunch of field names as the first row. The second row is to be filled in by the recipient.

I have a master file with two columns which contains the name I want for each file, which is a number, like 92625785965 (an ABN, in fact) and a Company Name.

The company name needs to go into cell A2.

How can I go about this in an automated fashion?
snooflehammerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Sorry, change it to

set sws=activesheet
0
 
Saqib Husain, SyedEngineerCommented:
You would need a macro like this

sub generatefiles
dim sws as worksheet
dim mws as worksheet
dim cel as range
set sws=activeworksheet
set mws = workbooks("masterworkbook.xls")
for each cel in mws.range("A1:A1000")
if cel<>"" then
sws.copy
activesheet.range("A2").value=cel.offset(,1)
activeworkbook.saveas cel.value & ".xlsx"
activeworkbook.close
end if
next cel
0
 
snooflehammerAuthor Commented:
This is a macro I run from within an Excel workbook?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Saqib Husain, SyedEngineerCommented:
Yes, I would do it from the template workbook.
0
 
snooflehammerAuthor Commented:
OK. I'll get to try this out in the next 48 hours. I'll revert then
0
 
snooflehammerAuthor Commented:
I'm struggling with this.

I\ve saved a worksheet with the filenames in colum 1 and the company names in column 2. Its called Book1.xlsx and it's on my D drive, so its full path is D:\Book1.xlsx.

The macro crashes on line 5 with run-time error '424'. Object required.

I've tried entering the path to book1, but no dice.
0
 
snooflehammerAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.