By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,478 Members | 1,601 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,478 IT Pros & Developers. It's quick & easy.

Access Table Export to Excel

P: 283

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.



Here is what I have so far
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim rs As DAO.Recordset
  3. Set db = CurrentDb
  4. Set rs = db.OpenRecordset("tbl adj and refunds", dbOpenSnapshot)
  6. 'Start a new workbook in Excel
  7. Dim oApp As New Excel.Application
  8. Dim oBook As Excel.Workbook
  9. Dim oSheet As Excel.Worksheet
  11. Set oBook = oApp.Workbooks.Add
  12. Set oSheet = oBook.Worksheets("Sheet1")
  14. oApp.Visible = True
  15. oApp.UserControl = True
  17. fldCount = rs.Fields.Count
  18. For iCol = 1 To fldCount
  19.     oSheet.Cells(1, iCol).Value = rs.Fields(iCol - 1).Name
  20. Next
  22. '**The line below is the problem, nothing copies**
  23. oSheet.Range("A2").CopyFromRecordset rs
  26. 'Format the header row as bold and autofit the columns
  27. With oSheet.Range("A1").Resize(1, fldCount)
  28. .Font.Bold = True
  29. .EntireColumn.AutoFit
  30. End With
  33. 'Close the Database and Recordset
  34. rs.Close
  35. db.Close
Dec 29 '11 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 15k+
P: 31,709
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?
Dec 29 '11 #2

Expert 100+
P: 446
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.

Dec 29 '11 #3

P: 283
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
Dec 29 '11 #4

Expert Mod 15k+
P: 31,709
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)?
Dec 29 '11 #5

P: 283
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.

Slen :D
Dec 29 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.