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

Question

Ask a question Report

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

m_albges

Posts: 5
Registration date: Thursday February 21, 2013
Last seen: May 9, 2013

- Feb 21, 2013 at 07:03 AM - Latest reply: rivera - Jan 16, 2017 at 04:36 AM

Excel Ask a question Report

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.

Report Respond to TrowaD

TrowaD

Posts: 2451
Registration date: Sunday September 12, 2010
Status: Contributor
Last seen: March 21, 2019

411 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

Report Respond to m_albiges

m_albiges Feb 26, 2013 at 12:13 PM

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.

Report

rizvisa1

Posts: 4475
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: January 6, 2016

917 - 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

Report Respond to m_albges

m_albges

Posts: 5
Registration date: Thursday February 21, 2013
Last seen: May 9, 2013

8 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.

Report

rizvisa1

Posts: 4475
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: January 6, 2016

917 - 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

Report

AREIFEY > rizvisa1

Posts: 4475
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: January 6, 2016

- 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!

Report

paulac > rizvisa1

Posts: 4475
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: January 6, 2016

- 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

Report

fkadir

Posts: 1
Registration date: Wednesday May 11, 2016
Last seen: May 11, 2016

- May 11, 2016 at 12:25 PM

did u get this function sorted? please share the code

Report Respond to m_albiges

m_albiges Feb 27, 2013 at 04:54 AM

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.

Report

Ambucias

Posts: 50491
Registration date: Monday February 1, 2010
Status: Moderator
Last seen: March 19, 2019

20710 - Feb 27, 2013 at 05:23 AM

@m_albiges

Brilliant you say ! Rivisa1 is as brilliant as a quasar, a solar mass !

Report

rizvisa1

Posts: 4475
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: January 6, 2016

917 - Feb 27, 2013 at 09:38 AM

Ambucias : Credit is to TrowaD. It was his solution. I just suggested a line

Report

Ambucias

Posts: 50491
Registration date: Monday February 1, 2010
Status: Moderator
Last seen: March 19, 2019

20710 - 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.

Report

aditya - Dec 25, 2014 at 10:12 AM

thank you so much its such a big relief

Report Respond to wagnercardozo

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

View all 8 comments

Report

dominik123123

Posts: 4
Registration date: Thursday February 18, 2016
Last seen: February 19, 2016

- Feb 19, 2016 at 12:47 PM

i faced same issue, I searched in many source finally got solution using Long Path Tool

Report

Rafaeel - Apr 21, 2016 at 05:34 AM

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

swiftalker - Dec 12, 2016 at 11:47 AM

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

TrowaD

Posts: 2451
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: March 21, 2019

411 - 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

Report

rivera - Jan 16, 2017 at 04:36 AM

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

Ask a question

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
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
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!
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
@m_albiges

Brilliant you say ! Rivisa1 is as brilliant as a quasar, a solar mass !
Ambucias : Credit is to TrowaD. It was his solution. I just suggested a line
@rizvisa1,

You are much too modest ! Modesty is the kind of pride that hurts the least. You and TrowanD make a dynamic duo.
@wagnercardozo , the path is hard coded, you can change it to where ever you want it to be saved
Hi there,
Thank you for your answer, however I want the user choose where he wants to save it.

I am using in different folders and the final user will not be able to change the vba code.

Thanks
Most easy way would be to pop up a input box where user may enter the folder to save the files. This in simplest form will suffer from GIGO (garbage in , garbage out)
Other way would be to have a dialog box where user navigate to the place. If you do search, you should find example for both cases
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.
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

m_albges · Accepted Answer · 2013-02-21T11:55:59-0500

Best answer

Report Respond to m_albges

m_albges

Posts: 5
Registration date: Thursday February 21, 2013
Last seen: May 9, 2013

8 Feb 21, 2013 at 11:55 AM

8

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.

Say "Thank you" 8

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

CCM 3408 users have said thank you to us this month

Audio (7,949)

Hardware (46,332)

Internet/Social Networks (32,907)

Linux/Unix (4,107)

MacOS (1,788)

Messaging (33,625)

Mobile (27,975)

Network (12,183)

Office Software (15,893)

Programming (2,469)

Software (20,851)

Video (163)

Video Games (8,260)

Viruses/Security (4,537)

Webmastering (867)

Windows (30,725)

Bugs and Suggestions (986)

Members' Cafe (1,630)

Opinions About Reviews (99)

Opinions About the News (1,175)

Ranking Software (6)

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

6 replies

Your reply