472,119 Members | 1,570 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,119 software developers and data experts.

TransferSpreadsheet fails in simple reproducible example


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
Set objExcel = Nothing
End Sub

Public Sub TestLots()
Dim intCount As Integer
For intCount = 1 To 5
Debug.Print "Pass number " & intCount
End Sub
Nov 12 '05 #1
0 2068

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Bill Agee | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.