Exporting Query to Excel 2003

[Sandworm]

I might be a bit slow here.
Im trying to export (output to) several queries to Excel 2003 from Access 2003.
Specifically I want several queries to "export to" individual worksheets in one workbook.
Im just unsure of the simple "output to" address line to acieve this. Currently Ive only got the "output to" address line ending in the excel workbook name ...workbookname.xls so access sends the query data to the workbook and it replaces any spreadsheets with a single sheet output with the name of the "query/macro" as the spreadsheet name.
I'd like to send the data from all the queries in a single macro and get them to input the data rows below headers I already have in the excel spreadsheet if possible.
Can I do this with a simple macro or do I need to use vb to achieve this result? An example of the code if anyone has something similar would be nice.
Thanks in advance.

 

[Trevor G]

Look to use TransferSpreadsheet and you can use the same address line to Excel Workbook. You just add multiple actions to the same workbook and it will place the results on individual sheets within the workbook.

 

[Sandworm]

Thanks Trevor G
I'll try the TransferSpreadsheet option but it still doesnt resolve the issue of loading the data into already prepared speadsheets with headers.
I know I could pre-load some formatting macros and run them once the data is in, however the former option is the more prefereable. Is there an easy solution?

 

[JANR]

I beleive Bob Larson has a code to to what you want:

http://www.btabdevelopment.com/ts/tq2xlspecwspath

This code goes in a standard module and you can run it from a macro or any appropriate form event.

From a macro chose Action RunCode and type something like this in the argumentbox:

sendTQ2XLWbSheet("queryname","someSheet","c:\Database\someXldoc.xls")

JR

 

[DCrake]

Here is a snippet of code that is used to copy a query from Access to Excel into an existing workbook.

Private Sub cmdExport_Click()
'On Error GoTo btnImport_Click_error

'Is there any data to export?
    rCnt = TestNoData("Qry-CustomerHistory", "Cannot find any history for this customer and date range")
    If rCnt = 0 Then
        Me.btnPreview.Enabled = False
        Me.cmdCancel.SetFocus
        Me.cmdExport.Enabled = False
        Exit Sub
    End If

'Ask user to confirm export
    If MsgBox("A total of " & rCnt & " record(s) found." & vbCrLf & vbCrLf & "Click OK to export or Cancel to stop export", vbQuestion + vbOKCancel, "Confirmation") = vbCancel Then
        Exit Sub
    End If
'If file exists delete it
    If Dir(StrDefaultPathAndFileName) <> "" Then
        Kill StrDefaultPathAndFileName
    End If

Dim sSql As String
sSql = ""
sSql = sSql & "Select Model, UnitJobNo, UnitServiceDateReceived, UnitSerial, "
sSql = sSql & "UnitInvoiceNo, UnitInWarranty, UnitRepair, StausDesc "
sSql = sSql & "From [Qry-CustomerHistory] "
sSql = sSql & "Order By Model, UnitJobNo;"


'Export the query and open Excel
   'Start a new session in Excel
   'If default template exists use that
    If Dir(CurrentProject.Path & "\DefaultTemplate.xls") <> "" Then
        FileCopy CurrentProject.Path & "\DefaultTemplate.xls", CurrentProject.Path & "\CustomerReport.xls"
        'This uses late binding method
        Set xlApp = CreateObject("Excel.Application")
        Set xlBook = xlApp.Workbooks.Open(CurrentProject.Path & "\CustomerReport.xls")
        Set xlSheet = xlBook.Worksheets(1)
            xlSheet.Range("F1") = "Customer History Report"
            xlSheet.Range("C2") = "Between " & GetDateLower() & " and " & GetDateUpper()
            xlSheet.Range("C3") = "Client = " & Me.ClientName.Column(1)
            
            Dim Rs As DAO.Recordset
            Set Rs = CurrentDb.OpenRecordset(sSql)
            'Insert the column heading
            xlSheet.Range("A4").Value = "Model"
            xlSheet.Range("B4").Value = "Job No"
            xlSheet.Range("C4").Value = "Date Rec'd"
            xlSheet.Range("D4").Value = "Serial No"
            xlSheet.Range("E4").Value = "Invoice No"
            xlSheet.Range("F4").Value = "Job Type"
            xlSheet.Range("G4").Value = "Repair Description"
            xlSheet.Range("H4").Value = "Status"
            xlSheet.Range("A5").CopyFromRecordset Rs
            xlSheet.Columns("A:N").Select
            xlSheet.Columns("A:N").EntireColumn.AutoFit
            xlSheet.Range("A1").Select

            Rs.Close
            Set Rs = Nothing
            xlApp.Visible = True
    
    Else
    
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Qry-CustomerHistory", StrDefaultPathAndFileName, True
       DoEvents
            If Dir(StrDefaultPathAndFileName) = "" Then
                MsgBox "There was a problem creating Excel workbook", vbExclamation + vbOKOnly, "Export Abandoned"
                Exit Sub
            End If
            
            'Let user know export has finished and ask them if they want to look at it now
        
            If MsgBox("Do you want to open Excel and view the workbook now?", vbQuestion + vbYesNo + vbDefaultButton1, "Export Completed") = vbYes Then
                nDT = GetDesktopWindow()
                nApp = ShellExecute(nDT, "Open", StrDefaultPathAndFileName, "", "C:\", SW_SHOWNORMAL)
                DoEvents
            End If

    End If
    
        
        
    



btnImport_Click_exit:
Exit Sub

btnImport_Click_error:
Select Case Err
   Case 3010
      MsgBox "Excel file already open. Please close", vbExclamation + vbOKOnly, "Export Error"
   Case Else
      MsgBox "Excel file may be open. Please close", vbExclamation + vbOKOnly, "Export Error"
      Resume btnImport_Click_exit
End Select
End Sub

Obviously there are references to code and functions not included in this snippet but you should still get the dift of how to do it.

 

[Sandworm]

Thanks all for your advices.
Thought I'd try JANR's advie 1st, with the following results:
Public Function SendTQ2XLWbSheet(strTQName As String, strSheetName As String, strFilePath As String)
' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to
' strFilePath is the name and path of the file you want to send this data into.

In the variables of the Function name
1. Replaced strTQName with query name = had a few issues with the code not liking the query name because of a number format in the query name - didnt realise it wouldnt like that but changed the query name to enable the code ok
2. Replaced strSheetName with the Workbook Sheet Name = seemed to work fine
3. Replaced strFilePath with the file path of the excel workbook i.e (Z:\foldername_one\foldername_two\Workbookname.xls) giving

Public Function SendWBSHEET(queryname As String, sheetname As String, Z:\foldername_one\foldername_two\Workbookname.xls As String)

= code faulted and highlighted the colon in the address line (Z:\) with "Compile Error Expected list seperator"

4. Replaced strFilePath with the file path of the excel workbook i.e (Z:\foldername_one\foldername_two\Workbookname.xls) in the code line;

strPath = strFilePath giving
strPath = Z:\foldername_one\foldername_two\Workbookname.xls

= code faulted and highlighted the backslash in the address line (Z:\) with "Compile Error Expected line number or label or statement or end of statement"

[SIZE=+0]I presume I must be doing something wrong but am not sure if I have not substituted the correct named objects and/or the file address line is not what was required by strFilePath.[/SIZE]
[SIZE=+0]Anyone care to help this poor amateur?[/SIZE]