Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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

Report
Posts
5
Registration date
Thursday February 21, 2013
Status
Member
Last seen
May 9, 2013
 -
 rivera -
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
Related:

6 replies

Answer 1 / 6
Posts
2663
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 8, 2020
 444
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
Thank you
9
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Answer 2 / 6
Posts
5
Registration date
Thursday February 21, 2013
Status
Member
Last seen
May 9, 2013
 9
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
9
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Answer 3 / 6

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.
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 760
use TrowaD code at https://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
Answer 4 / 6
Posts
5
Registration date
Thursday February 21, 2013
Status
Member
Last seen
May 9, 2013
 9
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.
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 760
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
Report
>
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
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!
Report
>
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
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
Report
Posts
1
Registration date
Wednesday May 11, 2016
Status
Member
Last seen
May 11, 2016
did u get this function sorted? please share the code
Answer 5 / 6

Hi,
Brilliant - that seems to be working! Presumably I can apply that principle to other columns fields too... that is a massive help!

Matt.
Report
Posts
48699
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
October 6, 2020
 14,670
@m_albiges

Brilliant you say ! Rivisa1 is as brilliant as a quasar, a solar mass !
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 760
Ambucias : Credit is to TrowaD. It was his solution. I just suggested a line
Report
Posts
48699
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
October 6, 2020
 14,670
@rizvisa1,

You are much too modest ! Modesty is the kind of pride that hurts the least. You and TrowanD make a dynamic duo.
Report

thank you so much its such a big relief
Answer 6 / 6

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
View all 8 comments
Report
Posts
4
Registration date
Thursday February 18, 2016
Status
Member
Last seen
February 19, 2016
i faced same issue, I searched in many source finally got solution using Long Path Tool
Report

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?
Report

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.
Report
Posts
2663
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 8, 2020
 444
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
Report

If you are getting file name too long then use Long Path Tool
Ask a question