docmd.transferspreadsheet crashes access

[blammo04]

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

[OBP]

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"

[blammo04]

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.

[OBP]

Have you checked that the Path to the Excel workbook is correct?
Can you post your database on here in Access 2003 format?

[OBP]

Have you got the Library Reference to excel ticked?

[blammo04]

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

[OBP]

Where does the strSoftwareNameOI come from and what is it's Format?

[blammo04]

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.

[OBP]

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?

[OBP]

You could even rem out the loop for now just to get the Transfer working.

[blammo04]

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........

[OBP]

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.