Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jul 2003
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export Queries to Excel (2002)

    I want to Export several Querys to Excel, each to it's own worksheeet within a single workbook, then open the workbook. Any Ideas?

    Thanks,
    keith

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Export Queries to Excel (2002)

    What have you tried so far?
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Jul 2003
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Queries to Excel (2002)

    DoCmd.TransferSpreadsheet, Then cutting and pasting.

    I did also set Excel as a reference and type "excel." to browse the options that poped-up, but I did not wade any further into this abyss...

    I guess I was hoping there was an easy solution where I could name a workbook, then a worksheet that I choose for a destination outlet of my querys.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Export Queries to Excel (2002)

    If you use DoCmd.TransferSpreadsheet to export queries to the same workbook, each query will be exported to a worksheet in that workbook with the same name as the query. You can then open the workbook using Application.FollowHyperlink, or using ShellExecute:

    The following declaration should be at the top of a module, after Option Explicit and Option Compare Database, but before all procedures and functions:

    Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
    (ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long

    Public Const SW_SHOWMAXIMIZED As Long = 3

    Use like this:

    Dim lngResult As Long

    lngResult = ShellExecute(hWndAccessApp, "Open", "C:ExcelExport.xls", 0&, 0&, SW_SHOWMAXIMIZED)
    If lngResult <= 32 Then
    MsgBox "Couldn't open workbook.", vbExclamation
    End If

  5. #5
    Star Lounger
    Join Date
    Jul 2003
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Queries to Excel (2002)

    Thanks Hans! I will give this a try today.

  6. #6
    Star Lounger
    Join Date
    Jul 2003
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Queries to Excel (2002)

    OK, here is what I have on the OnClick event of a butyon on a pop-up form:

    Private Sub CmdExcel_Click()
    Dim lngResult As Long

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QryStatusFCR", "C:AppsDBNextStep.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QryStatusFIP", "C:AppsDBNextStep.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QryStatusFIR", "C:AppsDBNextStep.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QryStatusDESIGN", "C:AppsDBNextStep.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QryStatusASSEMBLY", "C:AppsDBNextStep.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QryStatusHTR", "C:AppsDBNextStep.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QryStatusInstall", "C:AppsDBNextStep.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QryStatusPAY", "C:AppsDBNextStep.xls"

    'Open Excel Spreadsheet


    lngResult = ShellExecute(hWndAccessApp, "Open", "C:AppsDBNextStep.xls", 0&, 0&, SW_SHOWMAXIMIZED)
    If lngResult <= 32 Then
    MsgBox "Couldn't open workbook!", vbExclamation
    End If

    End Sub


    And here is my module:

    Option Explicit
    Option Compare Database

    Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
    (ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long

    Public Const SW_SHOWMAXIMIZED As Long = 3


    Everything works fine, except it brings up Excel with no sheets displayed, but with the database window still displayed. The file name shows in the title bar of excel, and when I switch to full-screen the worksheets appear. I can then turn off full screen and retain visibility. Any idea why this is happening? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    Thanks!!!

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Export Queries to Excel (2002)

    Sorry, no idea. I just tested your code, replacing the names of the queries with those in my test database, and the path of the workbook to an existing path on my PC. It works as intended.
    1. Do you have a slow PC, or limited RAM?
    2. Does the behavior persist after a restart of your PC?

  8. #8
    Star Lounger
    Join Date
    Jul 2003
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Queries to Excel (2002)

    Well, it works now after a reboot.

    Thanks a million for your help!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Keith

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •