Access Table Export to Excel

[slenish]

Hello,

I am having some trouble getting a table to open up as an Excel file after you press a button on a form. So far I have part of the code working where it will open up the Excel file but it will only display the headers and no information. I know which line of the code is not working just not sure why.

Any help with this would be appreciated.

Thanks,

Slen


Here is what I have so far

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl adj and refunds", dbOpenSnapshot)
 
'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
 
Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets("Sheet1")
 
oApp.Visible = True
oApp.UserControl = True
 
fldCount = rs.Fields.Count
For iCol = 1 To fldCount
    oSheet.Cells(1, iCol).Value = rs.Fields(iCol - 1).Name
Next
 
'**The line below is the problem, nothing copies**
oSheet.Range("A2").CopyFromRecordset rs
 
 
'Format the header row as bold and autofit the columns
With oSheet.Range("A1").Resize(1, fldCount)
.Font.Bold = True
.EntireColumn.AutoFit
End With
 
 
'Close the Database and Recordset
rs.Close
db.Close
 

[NeoPa]

Have you compiled this code?

I assume line #23 is the problem and there is no error message.

You tell us very little information. I see no code that opens Excel (See Application Automation). Does Excel even open properly?

[sierra7]

Hi
The fact that your headings are copying means that the spreadsheet is opening and you have some sort of recordset.

As a shot in the dark, try a 'rs.MoveFirst' at line #21.

S7

[slenish]

Hello NeoPa,

The code does execute and excel opens properly it just only shows the headers from the table and none of the information. Line 23 (oSheet.Range("A2").CopyFromRecordset rs)
is the problem because it should be setting the OpenRecordset to start copying the info at cell A2, but alas nothing...

Sierra7 - tried your suggest and no luck :D

[NeoPa]

OK. But before I go to the trouble of setting up a test rig why don't you tell me what happens when you try it with the approach suggested in the linked article (which is the recommended way of doing it), CreateObject(Class)?

[slenish]

Hi NeoPa,

Appreciate the quick response. I will try the CreateObject(class) and let you know what happens tomorrow. I dont have enough time to test it today.

Thanks,
Slen :D