TransferSpreadsheet problem

I am using the command DoCmd.TransferSpreadsheet to transfer data from
a named range in Excel to an Access table. This works ok for importing
from one Excel file but not from another which gives the following
error message:
"The Microsoft Jet Engine could not find the object "NamedRange". Make
sure the object exists and that you spell its name and path
correctly."

I have searched the news groups to understand what may cause the
problem. The explanations are:
1. The range name is too long - I have tested and this is not the case
2. The file is corrupted - I doubt this is the case.

For both Excel files TransferSpreadsheet works when the file is
already open. The Excel file that doesn't work has a number of
different characteristics from the working file:
1. It has macros
2. It has protection
3. The named range is grouped
4. There are a number of other worksheets
I've tried eliminating these to solve the problem and am struggling to
understand which causes
the issue.

I see two alternative approaches:
1. Open the file using CreateObject("Excel.Application"). Then
transfer the data using TransferSpreadsheet. Even when correctly
deleting the object this causes an instance of Excel to remain after
each file is opened.
2. Open the file using CreateObject("Excel.Application") and transfer
the data from the named range into a recordset.

I'm in the middle of trying to get option 2 to work (with a few
problems); however, for simplicity I would prefer to use
TransferSpreadsheet.

Can anyone shed any light onto what's causing the problem?

Thanks

Dan

0
danny
10/22/2007 11:11:13 PM
access 16762 articles. 3 followers. Follow

0 Replies
902 Views

Similar Articles

[PageSpeed] 14

Reply:

Similar Artilces:

Cell border formatting problem
Hi - In a simple, unprotected worksheet (Excel 2002), I cannot format the borders of some of the cells. If I click on the border button on the formatting toolbar, nothing happens. If I go to Format>Cells on the Menu Bar, nothing happens. I can, however, type in these cells. For some of these cells, I can copy and paste a formatted cell from another part of the worksheet successfully. For some cells, I cannot copy and paste a formatted cell. Sometimes if I select a range of cells around the offending cell, I can format all of them. But I still cannot format the offender by itself. ...

Problem with Hotmail?
I keep getting messages queued up to hotmail.com & msn.com, but no toehr domains. I'm using Exchange 2003 on Win2k3 w/ GFI Mail Essentials & Symantec Mail Security. Tracking center shows the the items get as far as "Routed and Queued for Remote Delivery". I've deleted the DNS Cache on our DNS Servers & on the Exchange Server, which didn't help. I rebooted the Exchange Server last night which cleared up the problem until this afternoon, when the problem returned. I can connect directly with telnet from our Exchange Server to mx1.hotmail.com 25. Any...

probleme
Voudrait savoir comment �a que je ne re�oit plue de messange par jnet@ntl.sympatico.ca en m�me temps que morin-alary-@hotmail.com ...

AppCrach Problem
This program Modio worked fine untill 2 days ago. It keeps shutting down before I get to use it. These are the deatails: * problem signature: *problem event name: appcrash application name: modio.exe application version: 1.0.0.0 application timestamp: 4a5a1505 fault module name: stackhash_27f2 fault module version: 0.0.0.0 fault module timestamp: 00000000 exception code: c0000005 exception offset: 0001136c os version: 6.0.6001.2.1.0.768.3 locale id: 1033 additional information 1: 27f2 additional information 2: 325055436168101a578479ab72a66d1a additional information 3: 21e2 ...

Sub-Report Problem
I have a sub-report that prints "exactly" as it should, but then repeats many, many times. The stand alone sub-report prints two pages. The sub-report in the main report prints 4226 pages (duplicates of two pages, 2113 times). The reports are NOT linked. The sub-report is located in the Detail section of the main report. I select to print this sub-report from a form, and then make the appropriate controls visible/not visible depending on the type of report I want. It works great, just too many pages on the sub-report. All other main reports (as selected from the Form, work fi...

Plam
I synchronise my Palm with MS Oulook 2002 (Windows XP) several times a day. For my calendar, I have set the sync on 'synchronise files'. It used to work fine until about a month ago (when I have installed the last Windows SP?). Now, instead of synchronizing, it simply adds up all the events. I end up having 50 events on the same day and same time (because I have done 50 synhronisations) ! Any solution or advice...?Thanks. ...

Problem in displaying the chart
Hi all, Good evening. I am facing one problem at creating chart. This is my coding. Charts.Add ActiveChart.ChartType = xlBarClustered ActiveChart.SetSourceData Source:=Sheets("Data").Range("A5:E6"), PlotBy:= _ xlRows * ActiveChart.Location Where:=xlLocationAsObject, Name:="Cost"* With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = _ "5 Time Predictability of Design and Construction" .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).Has...

Exchange server 2003 installation problem
Hi, I am new in Exchange Server. I am trying to insatll exchange server 2003. I download trial version of software and Exchange Server 2003 guide.doc from microsoft. I used Win 2003 server for OS, make the server as a domain controller with Active directory because it is testing so that i do not want to join our domain. Install all the services which the guide recommended. Run the Exchange 2003 ForestPrep and DoaminPrep from CD which I downloaded . wehn I try to install actual Exchange server the window looks just like ForestPrep, not the pictures guide shows. It is all greyed out and ...

printing problems #3
We have an HP Laser Printer (1300) with plenty of ram.. and yet I get an error whenever I send something of legal size to the printer. Is anyone else having compatibility issues between Publisher and HP? I called HP support and they indicate it must be application problem.. i.e. issues with Publisher. Word docs print fine. Werner I forgot to mention. The printer is on a server. After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Werner <wernerno@spamon.aibn.com>... > I called HP support and they indicate it must be application > problem.. i.e. issu...

Problems with Holidays
Hi there. I am using Outlook 2000 for Windows XP - Home Edition. I have followed the instructions to add holidays to my calendar - in this case, Canadian and Christian holidays. Once I complete the process, there are no holidays there. I have been reading on this group about "OutHol.exe". What is this file? Do I need it? If so, how can I get it? When you reply, can you please reply by email to llimbo72@yahoo.com? For some reason my hotmail account randomly decides not to deliver mail from some people, so I can't rely on it. Limbo. If you have Outlook 2000 or an earlier ...

Pivot Table Problem #3
I have just found out that Pivot Tables keep a history of old data and includes them with the new ones in the list for that particular data field, and because I only want the new data - I have to manually go in the drop down list for that data field and uncheck the old data so that it won't appear in the table. Does anyone know how to stop the old data from appearing in the drop down list so that it only shows the new data as options? This would help me a lot. Thanks in advance =) There's information here on clearing old items in a pivot table: http://www.contextures.com/xlP...

cached password problem
I have a user who whenever she changes her network password it breaks the authentication in outlook and IM. I am running xp machines and exchange 2000. She can change her password fine and authenticate to the domain and have full network access but both her IM and outlook break. Her outlook promptes for a password everytime it connects but works, her IM prompts for username password and domain everytime she connects. The problem can be fixed by deleting her local profile and relogging allowing a new profile to be built. But then recurs the next password change cycle. Is there a way to force s...

Gmail problem in Outlook 2010
I can sometimes send to my POP gmail account, but haven't been able to receive mail from that account at all. Gmail is the only acct. I use for email, and have had no problems in setting up gmail with its various settings in OL 2000. A little context: a few days ago I got a new computer, I installed Win 7, I then installed my old Office Pro 2000 suite including OL 2000. OL 2000 was generally able to send/receive from my gmail acct., but as is common, OL 2000 kept having problems so I decided to buy Office Pro 2010. I uninstalled Office 2000, and installed Office 2010. OL 2010 incorpor...

Outlook 2002 connector: problem connecting with Domino server because of NAMELookup2 problem
Hi, I'm trying to use Outlook 2002 Connector to connect with a Domino R5 server, but I can get no connection. From the log file I see that the connector uses a NAMELookup2 with the correct Domino server to get the name of the mail file, but the name its gets back is corrupted. There are spurious characters at the end of the mail file name. From the MSOCLog.txt file <<67c 25 16:37>> User name : : CN=Jean-Paul Smeets/O=Someorg <<4f8 25 16:37>> NAMELookup2 called with m_pServerName=someserver Flags=0 pNameSpaces=0x13646F4 NumNameSpaces=1 pNames=0x12E6CC NumNames=1 ...

Problem with product customisation
I really need to have suuplie name in the main list of products but for some reason its doesnt appear in the allowable options with all the other fields you can add. I really need this to be there. I know it can be added to the drop down for the product but it really doesn't suit our business to have to click on the product everytime we want to see who the supplier is. Surely theres a way to do this. Anyone? You can add Vendor Name to the Main List of Products, is this what you were trying to do? Regards, Chris "George" wrote: > I really need to have suuplie name...

sp3 install/uninstall problem
Hi all I need to revert back to ie6 from ie7 but have to uninstall sp3 first. Here is my problem Winver and add or remove programs both tell me that SP3 is installed, but when I attempt to uninstall SP3 the computer tells me it cannot be unnstalled because it is NOT INSTALLED. Appreciate any helpful response. TIA. OS Winxp Pro, Media Center Edition franktee KWV What problems are you having that you think uninstalled IE7 might resolve? What anti-virus application or security suite is installed and is your subscription current? What anti-spyware applications (other than Defe...

POP3 problem with Exchange 2000 #2
Hi folks I am really hoping someone can help me out here I am running a windows 2000 server with exchange 2000. The server is partitioned as follows; C:\ 5.13GB, D:\ Exchange 49.2, E:\ 49.7, 32.2GB unallocated. I had the AV originally on C:\ but moved it to E:\ this morning. There is also a plug-in for exchange installed as part of the AV suite. Users can send ok, the problem is in receiving email. I looked at ESM and in POP3 current connections it shows a list of users trying to send email If I reboot the server, everything is fine for a while and then the problem recurs. There doesnt s...

Email Problem
When we attempt to send a sales document via email, once Outlook opens and users select "To:" to select whom to send the email to, Outlook loses focus. You have to minimize Great Plains to get back to Outlook. This started only after we upgraded to ver 7.5. Any suggestions? Install Service Pack 5. "Mike W" wrote: > When we attempt to send a sales document via email, once > Outlook opens and users select "To:" to select whom to > send the email to, Outlook loses focus. You have to > minimize Great Plains to get back to Outlook. This >...

TransferSpreadsheet Error
I am using the DoCmd.TransferSpreadsheet method in an Access Form to export Data to an Excel workbook. Sometimes the method works, sometimes I get the following error: Error Number: 3310 Error Description: This property is not supported for external data sources or for databases created with a previous version of Microsoft Jet. The table being exported is actually a query, and the database has been created with the current version of Jet (4.0). Can anyone help me with this issue? Thanks, DJ ...

Outlook Address Book problem using Windows 7
I just got a new laptop with Wiondows 7, and MS Outlook. My Contacts imported fine, but my address book is blank. I have tried just about everything in outlook to straighten it out, but suspect its a problem with Windows 7 User account settings. I created a separate user in Windows 7, and can get the address book to work fine, but this would be a problem to implement becauase of all the customization for other programs. Question, in previous versions of Windows you could change the User preferences and profiles for Mail settings. Can anyone tell me how to do it in Windows ...

Outlook problem #34
I am having some trouble getting my outlook to work with my pop3 account these are the errors that I have been getting. Has any one ever seen these error codes before; &#61623; Task 'Microsoft Exchange Server' reported error (0x8007000E) : 'Out of memory or system resources. Close some windows or programs and try again.' No other programs running when outlook was. &#61623; Task 'pop3.microconstants.com - Sending' reported error (0x80070057) : 'Could not complete the operation. One or more parameter values are not valid.' Does anyone have an ide...

mail; problems
bonjour. Mes boites de reception m'indiqueent que j'ai 80 messages lus et 3 messages en attente de lecture. Quand j'ouvre la boite il est indiqu� qu'elle est vide.Au t�l�phone l'assistance technique numericable me dit qu'elle n'y peut rien faire. Avez-vous une solution? merci Good morning. My boxes reception m'indiqueent I read 80 messages and 3 messages waiting to be read. When I open the box indicated it is vide.Au numericable telephone technical support told me she can do anything. Do you have a solution? thank you Robert Gostanian a �cr...

CryptAcquireContext problem in Win98
Hi, I created CryptAcquireContext in win2000 and it is working successfully and when i installed in Win98 (Fresh system) it fails, i gote the error "Provider's public key is invalid " any one tell me how can i able to avoide this error. I invoked the functions as follows CryptAcquireContext (&hProv, NULL, NULL, PROV_RSA_FULL, 0) Thanks in advance Rgds Dinil, ...

Is there a PSR (Problem Steps Recorder) for Windows XP?
Is there a PSR (Problem Steps Recorder) for Windows XP? If there isn't then, can PSR be compatible with Windows XP? -Ryan BICSI Certified IT installor I PSR (Problem Steps Recorder) wrote: > Is there a PSR (Problem Steps Recorder) for Windows XP? If there > isn't then, can PSR be compatible with Windows XP? > > -Ryan > BICSI Certified IT installor I No. No. Print Screen --> Paste into Word. SnagIt. FastStone (can run from a portable drive.) Camtasia. TeamViewer to help the customer remotely even through firewalls with minimal config...

problem on UpdateData()
Dear, Folks, I used updatedata() on my program. I found the program had problem when the updatedata() was executed , I found the PROBLEM : unhandled exception...access violation... I don't know why, I called updatedata()several time on the same program, but only one calling cann't get through Also,I found if I remove the function call String2ByteArray(str,buf); so updatedata() work well. my code : ----------------------- int String2ByteArray(CString str, BYTE* buf) { str.Remove(' '); str.MakeUpper(); if (str.Left(2) == "0X") str = str.Mid(2); ...