Hi all,
Hopefully my question is relevant to this forum seeing as though I'm using Access to control Excel. The issue itself is an Excel VBA issue.
I'm looking to export the contents of multible table to one excel workbook. Here's what I have so far:
Code:
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set xlWB = objXL.Workbooks.Add
Set xlWS = xlWB.Worksheets(1)
xlWS.Name = "Tab1"
Set rst = CurrentDb.OpenRecordset("tblOne")
xlWS.Range("A1").Select
For Each fld In rst.Fields
With objXL.ActiveCell
.Value = fld.Name
.Font.Bold = True
.Font.ColorIndex = 2
.Interior.Color = 1
.Offset(0, 1).Select
End With
Next fld
rst.MoveFirst
xlWS.Range("A2").CopyFromRecordset rst
This works fine to get the first tab working. I then use:
Code:
Set xlWS = xlWB.Worksheets(2)
xlWS.Name = "Tab2"
Set rst = CurrentDb.OpenRecordset("tblTwo")
to move to the next worksheet, rename it, and select the next recordset to be pasted in. This also works fine. However, when I then try:
Code:
xlWS.Range("A1").Select
For Each fld In rst.Fields
With objXL.ActiveCell
.Value = fld.Name
.Font.Bold = True
.Font.ColorIndex = 2
.Interior.Color = 1
.Offset(0, 1).Select
End With
Next fld
rst.MoveFirst
xlWS.Range("A2").CopyFromRecordset rst
again, I get a runtime error "Select method of Range class failed." If I remove the section for the field headers, the CopyFromRecordset works fine as well and pastes in the proper data. I just can't seem to get the headers in. I've tried closing the recordset and setting it to Nothing prior to opening the second recordset, hoping to refresh things but it's not working. Help please.