Export Query Help

[dmonney]

Ok forgive me I'm a newb. I curently have a query in access that I'm trying to make a button to automatically send the results of this query to excell. When I run the code (below) it gives me a compile error saying user defined type not found on the highlighted portion

Private Sub ExporttoX_Click()
On Error GoTo Err_ExporttoX_Click



Dim db As DAO.Database
Dim rs As DAO.Recordset
'Set db = DAO.DBEngine.Workspaces(0).OpenDatabase( _
"C:\database.mdb")
Set db = CurrentDb
Set rs = db.OpenRecordset("qbpexcel", 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(1)

'Add the field names in row 1
Dim i As Integer
Dim iNumCols As Integer
iNumCols = rs.Fields.Count
For i = 1 To iNumCols
oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next

'Add the data starting at cell A2
oSheet.Range("A2").CopyFromRecordset rs

'Format the header row as bold and autofit the columns
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
End With

oApp.Visible = True
oApp.UserControl = True

'Close the Database and Recordset
rs.Close
db.Close

End Sub

Any help would be apreaciated

 

[tehNellie]

As is, I believe you'll need to have a reference set to Microsoft Excel nn.nn Object library.


You can write the code to not require the reference to be explicitly set by declaring oApp as a standard object and using:

set oAPP = CreateObject("excel.application")

you might find it beneficial to include the reference while you code to take advantage of intellisense prompts but code to not require an explicit reference being set to reduce the chances of problems further down the line.

There's more information than you'll probably ever want to know about early and late binding in this Microsoft article
(or for a quick and easy version here it's for excel and outlook rather than Access and Excel, but the principles are the same)