Exporting query using docmd.transferspreadsheet yields corrupt workbook

[Muscat.Angelo]

I've run into this problem numerous times, using office 2000. I'd
like
to export 5 queries to different worksheets in an excel file. I've
been using the "docmd.transferspreadsheet" command but when I export
even just one query, I can't open the workbook as it errors out. One
of my coworkers said he's heard of this problem before, so I'm
assuming others have too.
Anyone have a fix for this? I searched but can't find a thing, not
even in the groups. I'd like to stay away from writing a recordset
because it's much more labor intensive for me and there is only one
other person who has any experience with VBA in the office, so
troubleshooting would be difficult.

 

[Pete]

Access doesn't release the conection to the workbook. Goto
http://support.microsoft.com/search/?adv=1

and search articales for Does Not Close Microsoft Excel one of them should
solve your problem.

 

[Muscat.Angelo]

Thanks for the link, I tried searching but was unable to come up with
a solution. Creating an object and releasing it after the exports
yields the same results, leading me to believe that the object doesn't
affect the export. Any other ideas?

 

[Pete D.]

How large is the file your outputing, rows and columns. Also do you get any
specific error message when you open the workbook?

 

[Muscat.Angelo]

The queries vary in size, but I've been testing with only 2 of them.
The first is around 20k rows. The second is only about 250 rows. Both
have 5 columns or less. They used to be summarized greatly, but to
appease the auditors I am forced to add more detail.

When I open the workbook, I get the standard "Microsoft Excel for
Windows has encountered a problem and needs to close. We are sorry
for the inconvenience." message.

I'm running the function now with just the second, smaller query. Your
comments concerning the size of the output appear hint at the problem.

 

[Pete D.]

Okay,
Can you post your code? If it is a macro right click on it and save as
module give it a name open it in code window copy and paste here.

 

[Muscat.Angelo]

Running it with just the 250 row query, I still got the error.
** This code I just tried based on my search results but don't seem to
help
I don't know if code tags work, but just in case: <code>

Option Compare Database
Option Explicit
Public Function Balancing(dBegin As Date, dEnd As Date, BC1 As
Integer, BC2 As Integer)
' This is the main function that calls subroutines using specs from
the userform
'Dim dBegin As Date, dEnd As Date
'Dim BC1 As Integer, BC2 As Integer
dBegin = #6/1/2007#
dEnd = #6/29/2007#
Dim sFilePath As String
Dim sFilePath2 As String
sFilePath = "I:\ASVD\A-R\ERM Monthly\"
sFilePath2 = "FY" & FiscalYearAcctText(dEnd) & "\ERM Balancing " &
Format(dEnd, "yyyy-mm-dd") & ".xls"

**Dim objExcel As Excel.Application
**Set objExcel = CreateObject("Excel.Application")
CreateNewFile sFilePath, sFilePath2

ERMbalances sFilePath, sFilePath2
SameDC sFilePath, sFilePath2

**objExcel.Quit
**Set objExcel = Nothing
End Function

Public Function CreateNewFile(sFilePath As String, sFilePath2 As
String)
'This function creates a new file from the template.
On Error GoTo NewFileError
Dim fso As FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
MsgBox (sFilePath & sFilePath2)
fso.CopyFile sFilePath & "Template.xls", sFilePath & sFilePath2,
False
Set fso = Nothing
Exit Function
NewFileError:
If Err.Number = 58 Then
'This means that the file already exists. We will just
replace the data.
Resume Next
Else
MsgBox "An error has occurred" & vbCr & Err.Number & " - " &
Err.Description
End If
End Function

Public Function ERMbalances(sFilePath As String, sFilePath2 As String)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "1
Balances", sFilePath & sFilePath2, True
'DoCmd.OutputTo acOutputQuery, "1 Balances", acFormatXLS,
sFilePath & sFilePath2
End Function

</code>

 

[Muscat.Angelo]

I didn't include the second query function, on accident, but it's the
same as the ERMbalances function with a different query name. I chose
to creat a separate function for each query in hopes it would help the
corruption problem.

 

[Pete D.]

what version of excel do you have?

 

[Muscat.Angelo]

Excel 2000. Do you think upgrading to 2003 might help solve the
problem? I'm one of the few around here who still have the old
version, IT has been going around upgrading.

 

[Pete D.]

No, change acExport, 9 to acExport, 8

 

[Muscat.Angelo]

That didn't seem to work.

 

[Pete D.]

Okay, just checking the export filter as your message also occurs when older
version trys to open a newer one but 2000 would seamlessly open older
versions. Try export of normal query with just the export portion of your
code just to eliminate access excel as the problem and the references.

 

[Muscat.Angelo]

Sorry for taking so long... we've been busy with the auditors here
lately...

I tried the following code on a database on my desktop (no linked
tables) and it worked just fine:

Option Explicit
Function test()
DoCmd.TransferSpreadsheet acExport, , "Payment Cancellations", "C:
\Documents and Settings\All Users\Desktop\test.xls"
DoCmd.TransferSpreadsheet acExport, , "Adjustments", "C:\Documents and
Settings\All Users\Desktop\test.xls"
End Function

Do you think it's a problem with the queries being large, long-
running, and linked?