Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I would like to export a query to a spreadsheet using code.

    I found some code on the web that works but does more than I want. I don't want it to post the file on my desktop

    DoCmd.TransferSpreadsheet acExport, _
    acSpreadsheetTypeExcel9, "qryMakeExcelSpreadsheet", _
    "C:\Documents and Settings\pbien\Desktop\Test.xls", , "MyName"

    I just want Excel to open with the data from the query "qryMake ExcelSpeadsheet"

    I'm not haveing much luck taking out parts of the above code to make it work the way I want. Can anyone help?

    Thanks.

    Paul

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='pbien' post='768653' date='01-Apr-2009 16:27']I just want Excel to open with the data from the query "qryMake ExcelSpeadsheet"[/quote]
    The DoCmd.TransferSpreadsheet command will always create a file. It will not open Excel.
    To open Excel with VBA, you should use automation.
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Francois' post='768655' date='01-Apr-2009 14:37']The DoCmd.TransferSpreadsheet command will always create a file. It will not open Excel.
    To open Excel with VBA, you should use automation.[/quote]

    Can you explain further what you mean by automation.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='pbien' post='768656' date='01-Apr-2009 16:41']Can you explain further what you mean by automation.[/quote]
    See WendellB's website for a tutorial about automation.
    the Access Experts: Custom Database Solutions
    Francois

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='pbien' post='768656' date='01-Apr-2009 16:41']Can you explain further what you mean by automation.[/quote]
    The code should be something like this:
    Code:
    Dim excelApp As Object   'Declare object variable
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", "D:\Table1.xls", True
    Set excelApp = CreateObject("excel.application")  'Start excel
    excelApp.Visible = True  'make the excel instance visible
    excelApp.workbooks.Open ("D:\Table1.xls") 'open the workbook
    Set excelApp = Nothing 'set the excel instance to nothing
    Francois

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    An alternative would be to use OutputTo, which has an AutoStart argument:

    DoCmd.OutputTo acOutputQuery, "qryMakeExcelSpreadsheet", acFormatXLS, "C:\Documents and Settings\pbien\Desktop\Test.xls", True

    But (and this is a BIG but), OutputTo always creates a worksheet in Excel 5.0/95 format, you can't specify the Excel version as with TransferSpreadsheet. So Automation may well be a better option.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Francois' post='768668' date='01-Apr-2009 16:06']The code should be something like this:
    Code:
    Dim excelApp As Object   'Declare object variable
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", "D:\Table1.xls", True
    Set excelApp = CreateObject("excel.application")  'Start excel
    excelApp.Visible = True  'make the excel instance visible
    excelApp.workbooks.Open ("D:\Table1.xls") 'open the workbook
    Set excelApp = Nothing 'set the excel instance to nothing
    [/quote]

    This code works great but now I have one more question about it. I want it to creat the file on the desktop of whoever is the user.
    C:\Documents and Settings\pbien\Desktop\Table1.xls works for me. How do I write it for any user?

    Thanks.

    Paul

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='pbien' post='768688' date='01-Apr-2009 18:24']This code works great but now I have one more question about it. I want it to creat the file on the desktop of whoever is the user.
    C:\Documents and Settings\pbien\Desktop\Table1.xls works for me. How do I write it for any user?

    Thanks.

    Paul[/quote]
    Use the All Users directory.
    C:\Documents and Settings\All Users\Desktop\Table1.xls
    Francois

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can copy the following code to the top of a module:

    [codebox]Public Const CSIDL_DESKTOP = &H0
    Public Const NOERROR = 0

    Private Declare Function SHGetSpecialFolderLocation Lib "shell32" _
    (ByVal hwndOwner As Long, _
    ByVal nFolder As Long, _
    ppidl As Long) As Long

    Private Declare Function SHGetPathFromIDList Lib "shell32" _
    (pidl As Long, _
    ByVal pszPath As String) As Long

    Private Declare Sub CoTaskMemFree Lib "ole32" _
    (ByVal pv As Long)

    Private Const MAX_PATH = 260

    Function GetSpecialFolderLocation(ByVal lngCSIDL As Long) As String
    Dim lngRet As Long
    Dim strLocation As String
    Dim pidl As Long
    lngRet = SHGetSpecialFolderLocation(hWndAccessApp, lngCSIDL, pidl)
    If lngRet = NOERROR Then
    strLocation = Space(MAX_PATH)
    lngRet = SHGetPathFromIDList(ByVal pidl, strLocation)
    If Not lngRet = 0 Then
    GetSpecialFolderLocation = Left(strLocation, _
    InStr(strLocation, vbNullChar) - 1)
    End If
    CoTaskMemFree pidl
    End If
    End Function
    [/codebox]
    To retrieve the path of the user's desktop:

    Dim strPath As String
    strPath = GetSpecialFolderLocation(CSIDL_DESKTOP)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Or, if scripting is enabled, a much shorter function:

    Code:
    Function GetDesktop() As String
      Dim WSHShell As Object
      Set WSHShell = CreateObject("WScript.Shell")
      GetDesktop = WSHShell.SpecialFolders("Desktop")
      Set WSHShell = Nothing
    End Function
    Use like this:

    Dim strPath As String
    strPath = GetDesktop

  11. #11
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Francois' post='768689' date='01-Apr-2009 16:32']Use the All Users directory.
    C:\Documents and Settings\All Users\Desktop\Table1.xls[/quote]


    That works, Thanks.

Posting Permissions

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