Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Create multiple excel workbook in loop

Posted on 2013-11-05
7
Medium Priority
?
486 Views
Last Modified: 2014-02-04
Hi Experts,

I have access VBA code to collect necessary data to another sheet of the same workbook.
before save and close the sheet, I need to create excel workbooks using the names in the collected sheet.

for example, test.xlxs has two sheets all_data and filtered_data. The sheet filtered_data has cell contain some string like alpha, bravo, charlee, delta.
I want to create excel workbooks named alpha.xlxs, bravo.xlxs, charlee.xlxs and delta.xlxs ,
 it will create all strings of that cell in loop then save and close the test.xlxs.
copy some related data to the created excel workbook with a sheet name(use the workbook name)
Set wb_Source_01 = xl.Workbooks.Open("C:\test.xlsx")
Set ws_Source_01 = wb_Source_01.Sheets("all_data")
Set ws_Source_02 = wb_Source_01.Sheets("filtered_data")

first copied necessary data from all_data sheet to filtered_data sheet like below.

           ws_Source_02.Cells(LineCounter, 1) = ws_Source_01.Cells(LineCounter, 1)
           ws_Source_02.Cells(LineCounter, 2) = ws_Source_01.Cells(LineCounter, 2)

Once all necessary data copied to sheet filtered_data before save and close the workbook, wants to create excel workbook name it one of the cell value of the filtered_data sheet above. then save some related data from the filtered data to that newly created sheet then save it continue until all data of that cell if there 10 then wants to create 10 excel workbooks with the name of the cell in the filtered_data sheet.

Once done finally save and close the work book test.xlxs.

Please advice vba code to do that.

Thanks
0
Comment
Question by:alam747
5 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 39624059
Can you just clarify what you want to do?

You have a sheet, 'all_data', that has all the data and another sheet, 'filtered_data', that only has a few strings in cells.

You want to create a new workbook for each of the strings in 'filtered_data' and extract the data from 'all_data' the relevant sheet based on some criteria.

If that's what you have, how can it be determinend which workbook the data from 'all_data' should go to?

Is it based on a specific column?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39624248
@alam747,

this can be clearly understood if you can upload a copy of the excel file..



.
0
 

Author Comment

by:alam747
ID: 39627117
Hi Imnorie,

You're are correct, the data pull from filtered sheet to the newly created workbook the filtered sheet have have related filed there is a key that use to pull from.

the data in filtered sheet like as:
alpha   001  other related informamtion which will be copied in the newly cerated workbook alpha.xlsx
bravo 002    other related information which will be copied in newly created workbook bravo.xlsx

Therefore I can use the code 001 to pull entire cells of the raw to alpha.xlsx workbook.

Please provide me VBA code to do that in a loop.

Thanks
0
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 2000 total points
ID: 39637287
Can you post the test workbook with the data used to create the other workbooks?  Also, you only mention Excel in this post, but it is in the Access section.  Are you running the code from Access or Excel?  In the meanwhile, here is some code that creates workbooks in a loop, using data in an Access table:

Public Sub CreateWorkbooks()
'Created by Helen Feddema 10-Nov-2013
'Last modified by Helen Feddema 10-Nov-2013

On Error GoTo ErrorHandler

   Dim appExcel As New Excel.Application
   Dim wkb As Excel.Workbook
   Dim rst As DAO.Recordset
   Dim strData As String
   Dim strPath As String
   Dim wks As Excel.Worksheet
   
   strPath = Application.CurrentProject.Path & "\"
   Set rst = CurrentDb.OpenRecordset("tblTestData")
   
   Do While Not rst.EOF
      strData = rst![Data]
      Set wkb = appExcel.Workbooks.Add
      'Do something with workbook here
      Set wks = wkb.Sheets(1)
      wks.Range("A1").Value = "Testing"
      wkb.SaveAs strPath & strData
      wkb.Close
      rst.MoveNext
   Loop
   
ErrorHandlerExit:
   Set appExcel = Nothing
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in CreateWorkbooks procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 

Author Closing Comment

by:alam747
ID: 39834426
Thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

824 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