Generate excel workbooks based on excel list [Solved/Closed]

m_albges 5 Posts Thursday February 21, 2013Registration date May 9, 2013 Last seen - Feb 21, 2013 at 07:03 AM - Latest reply:  rivera
- Jan 16, 2017 at 04:36 AM
Hello,

I need to generate several hundred identical excel workbooks using a macro. I'd like to standardise the file names based on a list in Excel. So if column A says 'AC.334.997' and column B says 'Process improvement project', it would generate workbooks based on the template - in this case named 'AC.334.997_Process improvement project', and so on.

Any help would be much appreciated!

Thanks,
Matt.

See more 

23 replies

Best answer
m_albges 5 Posts Thursday February 21, 2013Registration date May 9, 2013 Last seen - Feb 21, 2013 at 11:55 AM
7
Thank you
Hi Trowa - great work!

This works well - the only issue is that each workbook it creates is a duplicate of the original list whereas what I need is for each workbook to be a given template... any idea how to do that?

Could I specify a template or a location of a file that it will then take as a sample and copy for each individual file that it creates?

Many Thanks!
Matt.

Thank you, m_albges 7

Something to say? Add comment

CCM has helped 1810 users this month

TrowaD 2433 Posts Sunday September 12, 2010Registration dateContributorStatus September 17, 2018 Last seen - Feb 21, 2013 at 10:49 AM
2
Thank you
Hi Matt,

See if this code is to your liking:
Sub CreateWBs()
Dim lRow, x As Integer
Dim wbName As String

lRow = Range("A" & Rows.Count).End(xlUp).Row
x = 1
Do
    x = x + 1
    wbName = Range("A" & x).Value & "_" & Range("B" & x).Value
    ActiveWorkbook.SaveAs Filename:="C:\Documents\" & wbName & ".xls"
Loop Until x = lRow

End Sub


Change the bold part to your own destination directory.
Running the code will save your workbook under a different name mentioned in columns A and B. It will keep renaming until there is no more data in columns A and B and thus creating more workbooks in the destination folder.

Best regards,
Trowa
2
Thank you
Fantastic work - thanks again to both of you. I've got this working now which is excellent. I do have a related question which would be the icing on the cake if we could get this up and running:

I've already got quite a bit of information about each of the schemes and what I'd like to do is self populate each file with this data. So let's say we add a 'Priority score' in column C alongside the ID number and scheme name, and ask the macro to populate that information in a specific cell in each of the files it is saving.

So as well as having a file for each scheme, you also have the relevant priority score in a specified cell. I've got quite a bit of information of this sort, but someone can show me the principle, I can expand for other columns or other information I've already got.

Thanks again!
Matt.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 26, 2013 at 12:52 PM
use TrowaD code at http://ccm.net/forum/affich-689536-generate-excel-workbooks-based-on-excel-list#1

then before you say " ActiveWorkbook.SaveAs ", you update the columns as for example

Sheets("SheetToUpdate").Range("A1") = Sheets("Lookup").Range("C" & x).Value
in this example,

Sheets("SheetToUpdate") is the sheet that you want to update
Range("A1") is the cell on the sheet that you want to update

Sheets("Lookup") is the sheet name that you want to update from
Range("C" & x).Value is the value you want to update
m_albges 5 Posts Thursday February 21, 2013Registration date May 9, 2013 Last seen - Feb 22, 2013 at 05:23 AM
0
Thank you
Hi,
I've had a couple of issues this morning in testing and trying to get it to work. It creates a couple of new workbooks no problem, then stops with the message:

"Run-time error '1004':

Method 'SaveAs' of object '_Workbook' failed"

... then asking me to de-bug.

I'm wondering whether some of the file names are too long due to the project names being too long - is there a way we could take just the first 20 characters of the project name (column B)? Any other suggestions?

Thanks again,
Matt.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 23, 2013 at 12:44 PM
try this
a bit twist on TrowaD code
Option Explicit

Sub copyTemplate()
   Dim lRow, x As Integer
   Dim wbName As String
   Dim fso        As Variant
   Dim dic        As Variant
   Dim colA       As String
   Dim colB       As String
   Dim colSep     As String
   Dim copyFile   As String
   Dim copyTo     As String
   
   Set dic = CreateObject("Scripting.Dictionary") 'dictionary to ensure that duplicates are not created
   Set fso = CreateObject("Scripting.FileSystemObject") 'file scripting object for fiile system manipulation
   
   colSep = "_" 'separater between values of col A and col B for file name
   dic.Add colSep, vbNullString ' ensuring that we never create a file when both columns are blank in between
   
   'get last used row in col A
   lRow = Range("A" & Rows.Count).End(xlUp).Row
   
   x = 1
   copyFile = "C:\Document\Template\Termplate.xls" 'template file to copy
   copyTo = "C:\Document\projects\" 'location where copied files need to be copied
   
   Do
    x = x + 1
    
    colA = Range("A" & x).Value 'col a value
    
    colB = Range("B" & x).Value ' col b value
    colB = Left(Range("B" & x).Value, 20) 'only retain first 20 characters
    
    wbName = colA & colSep & colB ' create new file name
    
    If (Not dic.Exists(wbName)) Then 'ensure that we have not created this file name before
      fso.copyFile copyFile, copyTo & wbName & ".xls" 'copy the file
      dic.Add wbName, vbNullString 'add to dictionary that we have created this file
   End If
   
Loop Until x = lRow

Set dic = Nothing ' clean up
Set fso = Nothing ' clean up

End Sub
AREIFEY > rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 25, 2016 at 11:35 PM
Is it possible to combine the two separate Sub's? For example, I have a list of filenames I want the template to be named in column A and then in column B I have a list alongside all of the file names containing a value to be input into a specific cell on the template file prior to saving with column A's filename. Basically, I would like the list separate from my template file to be replicated. I wouldn't want the list showing up in any of my replicated workbooks.

Thanks!
paulac > rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jul 10, 2016 at 05:58 AM
As per other I am trying to do both these things at once and just cant figure it out as need to save as and not copy like the above code has.

Could you possibly post the above with using a template in a file location as well as copying the data from the data sheet macro location
fkadir 1 Posts Wednesday May 11, 2016Registration date May 11, 2016 Last seen - May 11, 2016 at 12:25 PM
did u get this function sorted? please share the code
0
Thank you
Hi,
Brilliant - that seems to be working! Presumably I can apply that principle to other columns fields too... that is a massive help!

Matt.
Ambucias 55106 Posts Monday February 1, 2010Registration dateModeratorStatus September 24, 2018 Last seen - Feb 27, 2013 at 05:23 AM
@m_albiges

Brilliant you say ! Rivisa1 is as brilliant as a quasar, a solar mass !
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 27, 2013 at 09:38 AM
Ambucias : Credit is to TrowaD. It was his solution. I just suggested a line
Ambucias 55106 Posts Monday February 1, 2010Registration dateModeratorStatus September 24, 2018 Last seen - Feb 27, 2013 at 05:00 PM
@rizvisa1,

You are much too modest ! Modesty is the kind of pride that hurts the least. You and TrowanD make a dynamic duo.
thank you so much its such a big relief
wagnercardozo - Dec 2, 2015 at 01:04 AM
0
Thank you
Hello there
Thanks for this macro, that worked perfectly!

I have a question:

Is it possible to instead of typing the address where I want the file to be saved (C\Documents\...) the macro save where the template is located?

Thanks
Wagner
dominik123123 4 Posts Thursday February 18, 2016Registration date February 19, 2016 Last seen - Feb 19, 2016 at 12:47 PM
i faced same issue, I searched in many source finally got solution using Long Path Tool
Hi, thanks for your code, could you tell me how to modification your code to get new value name workbooks from other sheets. Could you show me how to fix my problem?
Hi @TrowaD

Can you modify this code for the following requirement:
I have created 908 different files with different names from an excel list, having the same template. However I also need to populate the first 3 cells (A2, B2 and C2) of each of these files with values in that same excel list corresponding to the filenames.
TrowaD 2433 Posts Sunday September 12, 2010Registration dateModeratorStatus September 17, 2018 Last seen - Dec 13, 2016 at 11:50 AM
Hi Swiftalker,

Could you create a new thread (short threads are better then long ones) and explain in greater detail what you would like to achieve?
Which code did you use? You would like to fill A2, B2, C2 with values, where can those values be found?

Best regards,
Trowa
If you are getting file name too long then use Long Path Tool