VBA Access export help

  • Thread starter Thread starter troy_lee
  • Start date Start date
T

troy_lee

This is the code I am using to export a query from Access.
Dim db As DAO.Database
Dim rs As DAO.Recordset

'Set db = DAO.DBEngine.Workspaces(0).OpenDatabase( _
"C:\database.mdb")
Set db = CurrentDb
'This is a crosstab of all units by Unit Type vs. Failure Category
Set rs = db.OpenRecordset("qryPA_Cross_1", dbOpenSnapshot)

'Start a new workbook in Excel
Dim oApp As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Set oApp = New Excel.Application
Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
oApp.Visible = True

'Add the field names in row 1
Dim intMaxRow As Integer
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
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
End If
Next

'Add the data starting at cell A2
oSheet.Range.(Cells(2, 1), .(Cells(intMaxRow,
intMaxCol)).CopyFromRecordset rs

The column headings are filling in fine. But, I am getting a "Syntax
Error" at the last line of this code. Any ideas?

Thanks in advance.
 
Just a though, but maybe

oSheet.Range.(oSheet.Cells(2, 1),
oSheet.Cells(intMaxRow,intMaxCol)).CopyFromRecordset rs


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Can you just use DoCmd.TransferSpreadsheet? I just used it the other day
for the first time to transfer the results of a query into a workbook.

hth,

Doug
 
Back
Top