Export multiple queries to one Excel file

[jcmeyer5]

I have 4 queries that I want to export. I want them to be 4 tabs in the same Excel file. I want to do this in a macro (a form has a command button that will run the macro).

I found a few options for exporting in the macro commands, but none of them want to add a tab... they all want to overwrite. Anyone know how to do this?

Thanks!

 

[PHV]

Why not pulling the data from Excel instead of pushing from Access ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jcmeyer5]

If it were just me using it, I would be right there with you. Unfortunately, the people that will be using these have trouble making hyperlinks in their emails. I dont want them to have to think about this.

 

[fredericofonseca]

The following is one way.

It has one problem with me. It will sometimes crash Excel, and if you run it twice without closing Access it doesn't always work well (Excel sticks in memory but does not show created document).

To add more than one worksheet you just need to mess with the SheetsInNewWorkbook property.

Public Sub invoice_create_excel()
On Error GoTo error_handler

'The functionality - -
'Retrieve current DB name so it can be used on the ADO connection.
'then execute the required SQL to retrieve the records into the Excel worksheet.

Dim objExcel As Object
Dim objExcelwkb As Object
Dim objexcelwks As Object

Dim adoRS As ADODB.Recordset
Dim sSQL As String

Dim ADOErrors As String
Dim j As Long
Dim i As Long
Dim sFilename As String
Dim bResult As Long
    ADOErrors = "ADO Error issued: "
    Set adoRS = New ADODB.Recordset
    sSQL = "my_query_statement"
    adoRS.Open sSQL, Application.CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    
    If adoRS.BOF And adoRS.EOF Then
        bResult = MsgBox("There is nothing to report.", vbOKOnly, "Invoice Management Report - New Invoices")
        adoRS.Close
        Set adoRS = Nothing
        End
    End If
    On Error Resume Next
    Set objExcel = GetObject(, "Excel.Application")
    If TypeName(objExcel) = "Nothing" Then
    'Excel was not open
        Set objExcel = CreateObject("Excel.Application")
    End If
    Err.Clear
    On Error GoTo error_handler
    
    objExcel.SheetsInNewWorkbook = 1
    Set objExcelwkb = excel.Workbooks.Add
    Set objexcelwks = objExcelwkb.Worksheets(1)
    
    objexcelwks.Activate
    objexcelwks.Range("a3").CopyFromRecordset adoRS
    adoRS.Close
    Set adoRS = Nothing
    
    objExcel.Visible = True
    Set objExcelwkb = Nothing
    Exit Sub

error_handler:
    If Application.CurrentProject.Connection.Errors.Count > 0 Then
        
        For i = 0 To Application.CurrentProject.Connection.Errors.Count - 1
            ADOErrors = ADOErrors & Application.CurrentProject.Connection.Errors(i).SQLState & "=" & Application.CurrentProject.Connection.Errors(i).Description & vbCrLf
        Next i
        j = MsgBox("Last SQL being executed: " & sSQL & vbCrLf & ADOErrors)
    Else
        j = MsgBox("error on module::invoice_create_excel " & vbCrLf & Err.Description)
    End If
End Sub

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[PHV]

Frederico, replace this:
Set objExcelwkb = excel.Workbooks.Add
By this:
Set objExcelwkb = objExcel.Workbooks.Add

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

And this:
Set objExcelwkb = Nothing
By this:
Set objExcelwks = Nothing
Set objExcelwkb = Nothing
Set objExcel = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fredericofonseca]

Ooops. My adjustments on the code removed a few bits and bobs.

The problem I have will be subject to a new thread as soon as I have time to investigate further. It has nothing to do with "set to nothing", as so far I have tried a very minor combination ahd it still had problem although I was doing all the "proper" steps. But this will be another thread...

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[JerryKlmns]

This works for me

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Christos_AAL", Server_Location & "MonadesPolisis.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Christos_Terms", Server_Location & "MonadesPolisis.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Christos_Revolving", Server_Location & "MonadesPolisis.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Christos_T/X", Server_Location & "MonadesPolisis.xls", True

4 tabs (name1:Christos_AAL, name2:Christos_Terms, name3:Christos_Revolving, name4:Christos_T/X) in the same Excel file (name:MonadesPolisis.xls).