generate 65 workbooks with code

[amal1973]

Hello..
I have created a workbook in excel. It has much functionality. There is VBA code on the sheet level and on the workbook level. What I am trying to do is to generate 65 workbooks with the same functionality, but the data will be supplied by a record set from an access database..
Please help me in figuring out how to create theses books in an automated process

I have started and this is a snap of my code

Private Function ExportDepartmentData(Department As String, cn As ADODB.Connection)
    Dim rs As ADODB.Recordset
    Dim row As Long
    Dim objField As ADODB.Field
    Dim loffset As Long

    
    objEx.Workbooks.Add   ' create a new workbook
    
    Set rs = New ADODB.Recordset
    
    rs.Open "SELECT Tbl_Inventory.[GL RC NBR], Tbl_Inventory.[GL CO NBR], Tbl_Inventory.[EQUIP DESC], Tbl_Inventory.DEVICE, Tbl_Inventory.[SERIAL NUMBER], Tbl_Inventory.[DEPR START DATE], Tbl_Inventory.POB, Tbl_Inventory.LOCATION, Tbl_Inventory.ADDRESS, Tbl_Inventory.CITY, Tbl_Inventory.STATE  FROM Tbl_Inventory  WHERE Tbl_Inventory.[GL RC NBR] =  '" & Department & "'", cn, adOpenForwardOnly, adLockReadOnly

    
    'rs.Open " SELECT  RC ,Account, Amount  FROM TEST WHERE RC =  '" & Department & "'", cn, adOpenForwardOnly, adLockReadOnly
   
    
     If Not rs.EOF Then
     objEx.ActiveSheet.Range("A16").CopyFromRecordset rs
    
    With objEx.ActiveSheet.Range("A15")
      For Each objField In rs.Fields
      .Offset(0, loffset).Value = objField.Name
      loffset = loffset + 1
     Next objField
  .Resize(1, rs.Fields.Count).Font.Bold = True
 End With
 
 
 
 
 Cells.Select
    Cells.EntireColumn.AutoFit
    
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "INVENTORY LIST"
    ..

    ..
    ..
    objEx.ActiveWorkbook.SaveAs "C:\Inventory\46b\ " &     Department & ".xls"
    objEx.ActiveWorkbook.Close
End Function
[\code] 
apreceate any help

 

[kylua]

Save the original workbook under different names and then reopen the original?

Eg (pseudo code)
do til end of depts
Populate appropriately with data from recordset.
Activeworkbook.saveas("Dept: Trash Disposal&quot
get next dept data
loop

This way all workbook functionality is preserved automatically and the original is untouched.

 

[amal1973]

thank you ..
i will give it a try ..