VBA export Access recordsets to Excel File

[afeets]

My aim is to open from a command, several SQL views exporting each individual view into it's own worksheet within one excel file.

I then hope to create a report involving charts pointing to the data from the worksheets.

My problem so far is that I can only insert the first View into the worksheet(1), the second recordset I open overwrites the first.

My code so far

!Private Sub cmdViewStats_Click()

Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Dim xl As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Dim strSql As String


strSql = "Select * from View_AllSchoolVisits"


Set xl = CreateObject("excel.Application")

Set xlBook = Workbooks.Add("C:\test.xls")

Set cmd = New ADODB.Command

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSql


Set rs = cmd.Execute

xl.Visible = True
xlBook.Windows(1).Visible = True

Set xlSheet = xlBook.Worksheets(1)

xl.Cells(2, 1).CopyFromRecordset rs


strSql = "Select * from view_OfficersTotalTimeSpentAtSchool"

Set rs = Nothing

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSql


Set rs = cmd.Execute

Set xlSheet = xlBook.Worksheets(2)

xlBook.Windows(1).Visible = True

xl.Cells(2, 1).CopyFromRecordset rs


Set rs=Nothing
Set cmd=Nothing
Set xl=Nothing

 

[xlbo]

Your issue is that you are not using the correct hierarchy of objects. In excel, the object model is as follows:

App>Workbook>Worksheet>Range>Cells

This line references cells directly from the app object:

xl.Cells(2, 1).CopyFromRecordset rs

not sure why you are doing this as you define xlbook and xlsheet earlier on but then do not use them

try this instead:

xlSHEET.Cells(2, 1).CopyFromRecordset rs

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

and btw - for future ref, please post VBA questions in the VBA forum: forum707

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question