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
Code:
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
Export Recordsets to Excel
