Consulting

Results 1 to 12 of 12

Thread: docmd.transferspreadsheet crashes access

  1. #1

    docmd.transferspreadsheet crashes access

    I am having some trouble with the docmd.transferspreadsheet function, everytime I run it, it crashes Access. I have used the docmd.outputto function and that works fine but I need the queries to be in the same excel file but in different sheet and the docmd.outputto open a new excel file for each query.

    If someone could look at my code and the docmd.transferspreadsheet and see if anyone could help I would greatly appreciate it. Thanks.


    Private Sub Auditor_Click()
    On Error GoTo Err_Auditor_Click
     Dim strReps As String
     Dim strReps2 As String
     Dim update As String
         Dim rstReps As New ADODB.Recordset
        Dim rstRepsOut As New ADODB.Recordset
        Dim test As Recordset
        Dim db As Database
        Dim qry As QueryDef
        Dim qry2 As QueryDef
        Dim i As Long
        Dim FileName As String
        
       
        If Me!List6.ItemsSelected.Count = 0 Then
        
            MsgBox "You must select Software.", vbCritical, "No records selected."
            GoTo Exit_Auditor_Click
            
        End If
        
        Set CnLoad = CurrentProject.Connection
        
        rstSoftware.Open "Software", CnLoad, _
            adOpenKeyset, adLockOptimistic, adCmdTable
        
        For Each varSelRecord In Me!List6.ItemsSelected
            lngSWIndexOI = CLng(Me!List6.ItemData(varSelRecord))
           
            strTarget = "Index = " & CStr(lngSWIndexOI)
            rstSoftware.Find strTarget
            strSoftwareNameOI = rstSoftware("Software Name")
            strVersionOI = rstSoftware("Version")
            strOSOI = rstSoftware("Operating System")
            FileName = strSoftwareNameOI
            'MsgBox strSoftwareNameOI + " " + strVersionOI + " " + strOSOI
          
            strReps = "SELECT Software.[Software Name], Software.Version, Software.[Operating System], " & _
                "Software.Status, Software.[Status Date], Software.[Approved Platforms], " & _
                "Software.[Code Type], CUsage.[Cost Center], CUsage.[Entered On] " & _
                "FROM Software INNER JOIN CUsage " & _
                "ON Software.[Software Name] = CUsage.[Software Name] " & _
                "AND Software.Version = CUsage.Version " & _
                "AND Software.[Operating System] = CUsage.[Operating System] " & _
                "WHERE Software.[Software Name] = '" & strSoftwareNameOI & "' " & _
                "AND Software.Version = '" & strVersionOI & "' " & _
                "AND Software.[Operating System] = '" & strOSOI & "'"
            
            strReps2 = "SELECT Software.[Software Name], Software.Version, Software.[Operating System], " & _
                "Software.Status, Software.[Status Date], Software.[Approved Platforms], " & _
                "Software.[Code Type], Space(30) AS [Cost Center], Space(30) AS [Entered On] " & _
                "FROM Software " & _
                "WHERE Software.[Software Name] = '" & strSoftwareNameOI & "' " & _
                "AND Software.Version = '" & strVersionOI & "' " & _
                "AND Software.[Operating System] = '" & strOSOI & "'"
                     
            DBEngine.BeginTrans
             Set db = CurrentDb()
             On Error Resume Next
             db.QueryDefs.Delete strSoftwareNameOI
             Set qry = db.CreateQueryDef(strSoftwareNameOI, strReps)
             CurrentDb.QueryDefs.Refresh
             DBEngine.CommitTrans
             
          If DCount("*", strSoftwareNameOI) = 0 Then
             DBEngine.BeginTrans
             Set db = CurrentDb()
             On Error Resume Next
             db.QueryDefs.Delete strSoftwareNameOI
             Set qry2 = db.CreateQueryDef(strSoftwareNameOI, strReps2)
             CurrentDb.QueryDefs.Refresh
             DBEngine.CommitTrans
             
             
               'DoCmd.OutputTo acOutputQuery, strSoftwareNameOI, acFormatXLS, strSoftwareNameOI & "_" & strVersionOI & "_" & strOSOI & ".xls", True     ' Works Perfect
    
      DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "strSoftwareNameOI", "C:\testing.xls", True, strSoftwareNameOI & "_" & strVersionOI & "_" & strOSOI        'Crashes Access everytime
            Else
            
          'DoCmd.OutputTo acOutputQuery, strSoftwareNameOI, acFormatXLS, strSoftwareNameOI & "_" & strVersionOI & "_" & strOSOI & ".xls", True   'Works Perfect
    
      DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "strSoftwareNameOI", "C:\testing.xls", True, strSoftwareNameOI & "_" & strVersionOI & "_" & strOSOI    'Crashes Access everytime 
          End If
          
    Next
        rstSoftware.Close
        Set rstSoftware = Nothing
        Set CnLoad = Nothing
        
    Exit_Auditor_Click:
        Exit Sub
    Err_Auditor_Click:
        MsgBox Err.Description
        Resume Exit_Auditor_Click
        
    End Sub

    I have tried to display an error message but Access just freezes and I get the program not responding message

    Anybody have any suggestions????? I dont know what the problem is

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,288
    Location
    From the Access help on TransferSpreadsheet where you have
    strSoftwareNameOI & "_" & strVersionOI & "_" & strOSOI
    is where the Cell reference usually goes.
    This is the format that I am used to
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tmpACK", Desktop & "Acknowledgements.xls"

  3. #3
    I tried that format and it still just freezes and gives me the program is not responding message.

    Do you have any other suggestions? Because I believe I have tried everything that I know.

    Thank you.

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,288
    Location
    Have you checked that the Path to the Excel workbook is correct?
    Can you post your database on here in Access 2003 format?

  5. #5
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,288
    Location
    Have you got the Library Reference to excel ticked?

  6. #6
    I would think the path to excel would be right because it works fine with the docmd.outputo but just not the docmd.transferspreadsheet..

    I'm wondering if its something to do with the format of the query strSoftwareNameOI and the format of that, because if I change it to something else Access will not freeze so I'm thinking its something to do with that.

    Do you see anything with the strSoftwareNameOI that could be wrong that I need to change?

    Thanks again

  7. #7
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,288
    Location
    Where does the strSoftwareNameOI come from and what is it's Format?

  8. #8
    strSoftwareNameOI stores the "Software Name" from the software table.

    Could the problem be because of the for each statement? That it loops through each selected item the user chooses out of the listbox?

    I dont understand why the strSoftwareNameOI will work with the docmd.outputto and not the docmd.transferspreadsheet though........its really confusing me.

  9. #9
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,288
    Location
    The syntax of the 2 functions is not quite the same.
    Can you test it by manually entering the Software name in to the transferspreadsheet command to test what does and doesn't work?

  10. #10
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,288
    Location
    You could even rem out the loop for now just to get the Transfer working.

  11. #11
    I tried to manually put the name of the software in and select just one software so it doesnt loop through and it still just freezes, I'm lost on this one........

  12. #12
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,288
    Location
    Can you give me an Access 2003 format version of your database?
    It doesn't need any data in it other than an example of the Software name and the name of the folder that you are trying to create the Spreadsheet in.

Posting Permissions

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