• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 149
  • Last Modified:

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?
0
snooflehammer
Asked:
snooflehammer
  • 4
  • 3
1 Solution
 
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
 
Saqib Husain, SyedEngineerCommented:
Yes, I would do it from the template workbook.
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
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
 
Saqib Husain, SyedEngineerCommented:
Sorry, change it to

set sws=activesheet
0
 
snooflehammerAuthor Commented:
Thank you
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now