By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,283 Members | 1,676 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,283 IT Pros & Developers. It's quick & easy.

TransferSpreadsheet fails in simple reproducible example

P: n/a
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
TransferSpreadsheet 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.xls". 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 TransferSpreadsheet
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.xls"

Private Sub TestBothFails()
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open strTEST_XLS, ReadOnly:=True
DoCmd.TransferSpreadsheet _
TableName:="HelloWorld", Filename:=strTEST_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
Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.