Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2004
    Posts
    115

    Unanswered: Export list box to excel

    I would like to export selected records (from list box) to .xls

    I'm attaching a small sample DB, so you can see what I have so far

    however, it does not export, can you please see what is missing?

    Thanks

    Private Sub cmdExport_Click()
    Dim i As Integer
    Dim str As String

    str = "SELECT tblOrder.InternalID, tblOrder.TitleCoID FROM tblOrder where tblOrder.InternalID in ("
    For i = 0 To List144.ItemsSelected.Count - 1
    str = str & List144.ItemData(List144.ItemsSelected(i))

    If i = List144.ItemsSelected.Count - 1 Then
    str = str & ")"
    Else
    str = str & ","
    End If
    Next i

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, str, "C:\temp\tttt.xls", True

    End Sub
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    And what exactly is the value of str. I can see the VB code that creates the text but not the actual value
    ..either put a message box after the loop (or during the loop if you fancy seeing it develop.
    or better yet use the debugger
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2004
    Posts
    115
    Value of atr is
    SELECT tblOrder.InternalID, tblOrder.TitleCoID FROM tblOrder where tblOrder.InternalID in (433,435,687,668)

    The value is based on the selection of the list box.

  4. #4
    Join Date
    Jan 2004
    Posts
    115
    i get run-time error

    MS engin cant find the object 'SELECT tblOrder.InternalID, tblOrder.TitleCoID FROM tblOrder where tblOrder.InternalID in (433,435,687,668)' make sure the object exist .....

    what is wrong?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    http://msdn.microsoft.com/en-us/libr...ice.15%29.aspx suggests that you cannot use a SQl statement for the TransferSpreadsheet method.
    ...oops sorry mis reqada the reference, seems you can use a SQL statement to do an export.


    so the reported error message is:-
    "MS engine cant find the object"
    so that suggests:-
    an invalid file path (could be mispellt path)
    ...wonder how the macro handles file already exists error?
    ...could it be a permissions issue say not allowed to write to that location

    is it possible its a missing or corrupt reference to the version of Excel you want to export to.
    Last edited by healdem; 12-28-14 at 14:17.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    Tom,

    I think that the 3rd parameter has to be a table or query.

    If so, you will need a DAO.CreateQueryDef.
    You already have the SQL (which looks good by the way).

    hth,
    Wayne

  7. #7
    Join Date
    Jan 2004
    Posts
    115
    I have same error when calling actual query query1
    I spent a lot of time on this. I need any solution. Even it will require extra steps. Thanks

  8. #8
    Join Date
    Jan 2004
    Posts
    115
    I have tried again searching for some options. However I'm not good at it.
    I will very appreciate if you can look at the actual file to check if you can see the problem.
    Should I expert to csv? Thanks

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Wayne is right: you need to use a QueryDef to create a Query that can be exported (the TransferSpreasheet method does not accept a SQL expression, it must be an existing query). Try this:
    Code:
    Private Sub cmdExport_Click()
    
        Dim qdf As dao.QueryDef
        Dim i As Integer
        Dim str As String
    
        str = "SELECT tblOrder.InternalID, tblOrder.TitleCoID FROM tblOrder where tblOrder.InternalID in ("
        For i = 0 To List144.ItemsSelected.Count - 1
            str = str & List144.ItemData(List144.ItemsSelected(i))
            
            If i = List144.ItemsSelected.Count - 1 Then
                str = str & ")"
            Else
                str = str & ","
            End If
        Next i
        Set qdf = CurrentDb.CreateQueryDef("Temp", str)
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, qdf.Name, "C:\temp\tttt.xls", True
        Set qdf = Nothing
        CurrentDb.QueryDefs.Delete "Temp"
    
    End Sub
    Have a nice day!

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Have you identified what is causing the error
    It could be any one element that is failing.
    so id suggest going back to the default values for the method, either as per a manual page or a de,o snippet of code.
    use a table not a select statement
    make sure its not your choice of other parameters passed to the nethod that are not triggering the error
    once the code is working, change indidual elements from the working code till you find what is causing the error.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jan 2004
    Posts
    115
    Thank you.
    i used QueryDef and it works

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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