Access to Excel - Separate .xls for each Header

[cfoxhaley]

I am fairly new to VBA and office Automation, and need some guidance. Here is my basic problem.

In my Access (2002) database, I have a Header table linked to Detail Table. The header contains a several descriptive fields, including a file name. Each header has multiple detail records with numeric and descriptive fields.

For each Header, I need to create a separate Excel spreadsheet. The data in the header is spread out over the first few lines of the spreadsheet, followed by a line holding column headings for the details. All of the detail records follow, one line per record.

In Access, I have already setup a Form with a Subform for the user to review the data before creating the spreadsheet. The Header is in the mainform, and the detail in the subform.

I would like to add a button to the main form for the user to click to create the spreadsheet, using the file name stored in the Header record.

Is Automation the way to go for this? Will it allow me to control exactly which columns and rows contain the header information, and then add all of the details starting at row 8? I've downloaded the automation help file provided by Microsoft, but don't want to go down this path if you think there is a simpler approach.

If there are any books/sites with detailed examples that you know of, I would appreciate that also.

Thanks in advance.

 

[CautionMP]

cfoxhaley,
Here are some thoughts.

[tab]I need to create a separate Excel spreadsheet.
[tab]Workbook per header record or a single Workbook with a Worksheet for each header record?

[tab]Is Automation the way to go for this?
[tab]Depends on your situation, I would try [tt]DoCmd.OutputTo[/tt] on your current Form or a Report first.
[tab]You loose some control of the output but it is easier.

[tab]Will it allow me to control exactly which columns and rows contain the header information,
[tab]and then add all of the details starting at row 8?
[tab]Yes.

[tab]If there are any books/sites with detailed examples that you know of...
[tab]um... try searching for "[tt]Excel.Application[/tt]" in this forum or
[tab]forum705

If you decide to go the automation route here is a snipit that should get you moving in the right direction. I used a couple of different methods of referencing cells so you can see some of the options.

Private Sub Command1_Click()
  Dim objXL As Object
  Dim objWKB As Object
  Dim objWKS As Object
  Dim objField As Object
  Dim lngColumn As Long
  
  'Create the destination workbook and grab the first worksheet
  Set objXL = CreateObject("Excel.Application")
  objXL.Visible = True
  Set objWKB = objXL.workbooks.Add
  Set objWKS = objWKB.worksheets(1)
  'or add a new one
  'Set objWKS = objWKB.worksheets.Add
  
  'Write some header info in specific cells.
  objWKS.Range("A1") = Me.TextBox1
  objWKS.Cells(1, 2) = Me.TextBox2

  
  'Write your header here in Row 8
  lngColumn = 0
  For Each objField In Me.subDetail.Form.RecordsetClone.Fields
    lngColumn = lngColumn + 1
    objWKS.Cells(8, lngColumn) = objField.Name
  Next objField
  'Write the data starting in row 9
  objWKS.Range("A9").CopyFromRecordset Me.subDetail.Form.RecordsetClone
  
  Set objWKS = Nothing
  objWKB.SaveAs "C:\Test.xls" 'Me.TextBox3
  objWKB.Close
  objXL.Quit
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[cfoxhaley]

WOW! This was much more help than I expected. Thanks. It works like a charm, except that in the detail, it returns all of the fields in the underlying table, including the 4 fields that make up the primary key. Here is the code I am using:

Private Sub CreateFile_Click()
Dim objXL As Object
Dim objWKB As Object
Dim objWKS As Object
Dim objField As Object
Dim lngColumn As Long

'Create the destination workbook and grab the first worksheet
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set objWKB = objXL.Workbooks.Add
Set objWKS = objWKB.Worksheets(1)

'Write Header data to worksheet
objWKS.Range("A1") = "Version:"
objWKS.Range("B1") = Me.GLVersion
objWKS.Range("D1") = Me.GLVersionDesc

objWKS.Range("A2") = "Ledger"
objWKS.Range("B2") = Me.GLLedger
objWKS.Range("D2") = Me.GLLedgerDesc

objWKS.Range("A3") = "Fiscal Year"
objWKS.Range("B3") = Me.GLYear

objWKS.Range("A4") = "Posting Period"
objWKS.Range("B4") = Me.GLPer
objWKS.Range("A4") = "To"
objWKS.Range("B4") = Me.GLPer

objWKS.Range("A5") = "Company Code"
objWKS.Range("B5") = Me.GLCoCode
objWKS.Range("D5") = Me.GLCoCodeDesc

objWKS.Range("A6") = "Currency"
objWKS.Range("B6") = Me.GLCurr
objWKS.Range("D6") = Me.GLCurrDesc

objWKS.Range("A7") = "Account"
objWKS.Range("B7") = "Account Desc"
objWKS.Range("C7") = "Profit Ctr"
objWKS.Range("D7") = "Profit Ctr Desc"
objWKS.Range("E7") = "Amount"
objWKS.Range("F7") = "DK"
objWKS.Range("G7") = "Unit"

'Write Detail data on subform to worksheet
objWKS.Range("A9").CopyFromRecordset Me.subPCA.Form.RecordsetClone

Set objWKS = Nothing
objWKB.SaveAs Me.GLFileName
objWKB.Close
objXL.Quit

End Sub

-------------------------------

Is there a way to use CopyFromRecordset and tell it to skip the first 4 fields? Or do I need to loop through the recordset and process each record individually?

 

[cfoxhaley]

Nevermind the previous request for a way to loop through and move the individual fields in the recordset to specific columns in the spreadsheet. With a little research, I figured out how to do it. It may not be elegant, but it works.

-------------------------
Set rst = Me.subPCA.Form.RecordsetClone

lngRow = 8
rst.MoveFirst
While Not rst.EOF
objWKS.Cells(lngRow, 1) = rst.Fields(4).Value
objWKS.Cells(lngRow, 2) = rst.Fields(9).Value
objWKS.Cells(lngRow, 3) = rst.Fields(5).Value
objWKS.Cells(lngRow, 4) = rst.Fields(10).Value
objWKS.Cells(lngRow, 8) = rst.Fields(7).Value
objWKS.Cells(lngRow, 9) = rst.Fields(1).Value
rst.MoveNext
lngRow = lngRow + 1
Wend

objWKS.Columns("A:K").AutoFit
-------------------------

Now I just need to get the spreadsheet formatted.
Where can I find a reference to let me figure out how to format the newly created worksheet? I found samples for Autofit, but need to format a couple columns to be left justified, and one to be numeric (, with 2 decimal places), and also bold a line.

 

[PHV]

Why not simply use a template (.xlt) when creating the workbook ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CautionMP]

cfoxhaley,
Glad to hear it worked for you.

...it returns all of the fields in the underlying table...

You can use a row/column loop but [tt]CopyFromRecordset[/tt] is much faster and can handle your problem. Build a query with the fields you want, add the key fields you want hidden to the end, then use the MaxColumns to keep them from showing in the worksheet.

[tab]CopyFromRecordset(Data As Unknown, [MaxRows], [MaxColumns])

Where can I find a reference to let me figure out how to format the newly created worksheet?

PHV has a good point, try the template. If that doesn't work for you IMHO the easiest way to figure out the objects/properties/methods/syntax is to use the Macro recorder in Excel. Record what you want to do then incorporate it in your Access project.

Hope this helps,
CMP

 

[cfoxhaley]

Thanks to everyone! The app works beautifully. I feel like you guys made me an instant expert.