TransferSpreads heet fails in simple reproducible example

[Mick Hardy]

Hi,

Has anyone seen this weird behaviour or have any suggestions or can
anyone reproduce it?

The history:

I converted a large third party DB from 97 to XP and it uses the
TransferSpreads heet method. This method requires a somewhat
complicated spreadsheet to be open in order to work. A simple
spreadsheet does not have this pre-open requirement.

Access XP is on Citrix and Office 2000 is on the desktop - don't ask!
The users cannot open the spreadsheet locally (as they used to)
because Access and Excel cannot communicate across the Citrix
boundary. I decided to open the spreadsheet on Citrix using
automation and then let the existing code do its thing.

All testing is with local copies of Access XP (SP2) and Excel 2000
(SP3) with Jet4.0 (SP8) on WinXP (SP1). I ran into some weird errors
and managed to isolate them in a separate simple project.

The problem:

Create an Excel spreadsheet with some simple column headers and a
couple of rows of data and save it as "C:\Temp\Test.x ls". Create a
new DB and add the supplied code to a module.

Run the TestLots procedure once and all is good.

** Open the task manager and check for rogue copies of EXCEL.EXE -
this seems to be a critical step and usually there are no instances
but not always.

Run the procedure a second (or third) time and the TransferSpreads heet
method fails with a Run-Time error '3000' - as an aside, click the
Help button and get a blank screen.

Any help would be greatly appreciated. I'm going to test it on Office
2000 at home tonight. I highly suspect its the XP/2000 mix - or, with
some luck, something very simple like a property called "add random
errors". Restarting Access and killing any copies of Excel fixes the
problem for the first execution.

Const strTEST_XLS = "C:\Temp\Test.x ls"

Private Sub TestBothFails()
Dim objExcel As Object
Set objExcel = CreateObject("E xcel.Applicatio n")
objExcel.Workbo oks.Open strTEST_XLS, ReadOnly:=True
DoCmd.TransferS preadsheet _
TableName:="Hel loWorld", Filename:=strTE ST_XLS
objExcel.Quit
Set objExcel = Nothing
End Sub

Public Sub TestLots()
Dim intCount As Integer
For intCount = 1 To 5
Debug.Print "Pass number " & intCount
TestBothFails
Next
End Sub