Solved

Script or similar to create hundreds of cutomised Excel files

Posted on 2013-12-17
7
137 Views
Last Modified: 2013-12-19
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
Comment
Question by:snooflehammer
  • 4
  • 3
7 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
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
 

Author Comment

by:snooflehammer
Comment Utility
This is a macro I run from within an Excel workbook?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
Yes, I would do it from the template workbook.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:snooflehammer
Comment Utility
OK. I'll get to try this out in the next 48 hours. I'll revert then
0
 

Author Comment

by:snooflehammer
Comment Utility
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
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
Comment Utility
Sorry, change it to

set sws=activesheet
0
 

Author Closing Comment

by:snooflehammer
Comment Utility
Thank you
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now