Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export To Excel (Excel 2K)

    Good Morning all,

    I use the following code to export my data to Excel, all works just fine.

    I've used code I'm looking for before but can't run across it .... where the data will export to a SPECIFIED spreadsheet.

    In other words, I want my data to be named "LFSum" and export to "LF022105".

    Dim strDCount As String

    strDCount = "LF Sum"

    Me.txtDCount = strDCount

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "mqryCountFische"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "etblLFDailyCount", "T:All_NHSOperatorIP" & Me!txtExportDate.Value & Me!txtDCount.Value & ".xls", False, 0
    DoCmd.SetWarnings True
    DoCmd.Close acForm, "frmExport"

    The above works fine...but I'd rather it be exported directly into an existing spreadsheet.

    Can someone please point me in the right direction.

    Thanks in advance.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Export To Excel (Excel 2K)

    See the thread starting at <post#=437462>post 437462</post#>.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export To Excel (Excel 2K)

    Hans,

    As always, Thank you.

    I've read the post and made the modifications, tested, tested made more changes and tested some more. I think I'm missing something....or .... from what I'm seeing I didn't make myself clearer in my post. The following is the code I have currently. It works with some exceptions:

    Private Sub cmdExp_Click()
    ' Declarations
    Dim strDataTable As String
    Dim strSumTable As String
    Dim strFile As String
    Dim strLFName As String
    Dim strFriendlyName As String
    Dim objXL As New Excel.Application
    Dim objWb As Excel.Workbook

    ' Error handling on
    On Error GoTo ErrHandler

    ' Set Declarations
    strDataTable = "etblLaserFische"
    strSumTable = "etblLFDailyCount"
    strFile = "T:All_NHSOperatorIP" & Me!txtExportDate.Value & ".xls"
    strFriendlyName = "Summary"

    'Prepare the Query's to update the temp tables
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "mtqExpLF", acNormal, acEdit

    ' Export the data from temp tables to Excel
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strDataTable, strFile, False, 0
    DoCmd.OpenQuery "mqryCountFische"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strSumTable, strFile, False, 0

    ' Open in Excel
    Set objWb = objXL.Workbooks.Open(strFile)

    ' Rename worksheet
    objWb.Worksheets(strSumTable).Name = strFriendlyName

    'Empty the temp tables
    DoCmd.OpenQuery "dqryExport", acNormal, acEdit
    DoCmd.SetWarnings True
    DoCmd.Close acForm, "frmExport"

    ExitHandler:
    On Error Resume Next
    ' Close and save
    objWb.Close SaveChanges:=True
    ' Quit Excel
    objXL.Quit
    ' Release object memory
    Set objWb = Nothing
    Set objXL = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler

    End Sub

    The exceptions:
    It should EXPORT 2 tables (the Data and Summary tables) The 1first one (The Data) actually creates the 1st Spreadsheet (once exported, should be named LF(and the date entered by the user)for simplicity LF020505). The 2nd table (the Summary) should actually EXPORT as a WORKSHEET into LF020505 and be named "Summary".

    Currently the only table EXPORTING is the summary, NOT renamed. It's my assumption that the 1st one is exporting but being overwritten by the 2nd one.

    I hope this time I'm more clear....I've over exerted by brain today with this...and there wasn't that much there to begin with....LOL

    Thanks again.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export To Excel (Excel 2K)

    Roberta,

    remove the ,0 at the end of the line
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strDataTable, strFile, False
    and use for the second export :
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strSumTable, strFile, False, strFriendlyName

    And you can remove all the code to rename the worksheet in Excel.
    Just test it and should work.
    Francois

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export To Excel (Excel 2K)

    Francois,

    PERFECT....I would never have tried that avenue....thanks so much!!!
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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