Report

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

Ask a question m_albges 5Posts Thursday February 21, 2013Registration date May 9, 2013 Last seen - Last answered on Jan 16, 2017 at 04:36 AM by 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 
Helpful
+6
plus moins
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.
Was this answer helpful?  
Helpful
+2
plus moins
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 4482Posts Thursday January 28, 2010Registration date ContributorStatus 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
Helpful
+1
plus moins
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
Helpful
+0
plus moins
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 4482Posts Thursday January 28, 2010Registration date ContributorStatus 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- 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 - 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 1Posts 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
Helpful
+0
plus moins
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 43382Posts Monday February 1, 2010Registration date ModeratorStatus August 15, 2017 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 4482Posts Thursday January 28, 2010Registration date ContributorStatus 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 43382Posts Monday February 1, 2010Registration date ModeratorStatus August 15, 2017 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.
aditya- Dec 25, 2014 at 10:12 AM
thank you so much its such a big relief
Helpful
+0
plus moins
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 4Posts 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
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?
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.
Reply
TrowaD 2259Posts Sunday September 12, 2010Registration date ModeratorStatus August 15, 2017 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
Reply
rivera- Jan 16, 2017 at 04:36 AM
If you are getting file name too long then use Long Path Tool
Reply

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!