Link to home
Start Free TrialLog in
Avatar of snooflehammer
snooflehammerFlag for Australia

asked on

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?
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

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
Avatar of snooflehammer

ASKER

This is a macro I run from within an Excel workbook?
Yes, I would do it from the template workbook.
OK. I'll get to try this out in the next 48 hours. I'll revert then
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.
ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you