473,856 Members | 1,439 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to import data from named ranges in Excel 2003 into Access?

7 New Member
I have no hair left with this issue! I'm simply trying to import data from named ranges in Excel 2003, into tables of the same name in Access 2003. I have found that unless I have the spreadsheet from which I want to import, OPEN, the named ranges cannot be found. I can't even see them trying to manually import the data, unless the spreadsheet is open!

I don't want the spreadsheet open because that causes an instance of Excel to remain active, as viewed in Windows Task Manager/Processes (another issue, but what started this whole long nightmare).

I have used variations of this code many times over the years, but now it doesn't work. I have installed Office 2007 converters, which I'm wondering if is causing this issue. Here's the line of code that fails:
[DoCmd.TransferS preadsheet acImport, acSpreadsheetTy peExcel9, "tmp" & tbl, FileName, True, rng]

tbl = a string that is the name of a table in Access, and also a named range in Excel. "tmp" & tbl is a temporary file which holds the import. All field names are identical in the spreadsheet and the Access tables.
Filename is the path and name of the spreadsheet.

Please help!!
Jan 24 '11 #1
11 10072
8,834 Recognized Expert Expert
The following Code will Import a Named Range, in an Excel Spreadsheet, into an existing Access Table. The Spreadsheet need not be 'Open' for this process to occur successfully.
Expand|Select|Wrap|Line Numbers
  1. '************* Define Your Own Constants *************
  2. Const conFILE_PATH As String = "C:\Test\Test.xls"
  3. Const conRANGE_NAME As String = "Test_Range"
  4. Const conTABLE_NAME As String = "Table1"
  5. '*****************************************************
  7. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, conTABLE_NAME, conFILE_PATH, True, conRANGE_NAME
Jan 25 '11 #2
Laurel Eppstein
7 New Member
ADezii, that code is no different than what I had posted. I get the same error running it as I did with mine. Yet when I have the spreadsheet open, it runs just fine. THAT is the problem!

Thanks for trying,
Jan 25 '11 #3
8,834 Recognized Expert Expert
  1. What is the Range Assignment for the Variable rng?
  2. Does the 1st Row of the Spreadsheet have Field Names?
  3. Out of curiosity, can you Upload the Spreadsheet stripped of any confidential information?
Jan 25 '11 #4
Laurel Eppstein
7 New Member
1. Range assignment is a string, identical to the table to which it will ultimately be appending in Access. An example is "tblFacilit y". The transferspreads heet method creates a temporary table of the same name, but with the 'tmp' prefix.
2. The first row of each named range has field names
3. I would upload the spreadsheet except that I see no way to do so: the allowed extensions are picture-types or docs or txt.
Jan 25 '11 #5
8,834 Recognized Expert Expert
.zip the Spreadsheet, then Upload it. I'll have a look later today.
Jan 25 '11 #6
Laurel Eppstein
7 New Member
Thanks for staying with me on this. BTW, I'm using Office 2003, but with Office 2007 converters loaded.
Jan 25 '11 #7
Laurel Eppstein
7 New Member
Another piece of info: this works seamlessly in Office 2010, if I save the file first as a .xlsm type, and then specify the spreadsheet type as 12. However, if I try to do the import in Access 2010 of spreadsheet type 8 or 9, with the older version of the Excel spreadsheet, it can't find the named range!
Jan 25 '11 #8
8,834 Recognized Expert Expert
Very strange behavior, Laurel.
Jan 25 '11 #9
Laurel Eppstein
7 New Member
It seems as though it's an Access problem. When I imported my entire database into Access 2010, the same thing occurred: could see named ranges of .xlsm files, but not .xls files. For various backward-compatibility issues, I do not choose to develop in 2010. ARGHH!
Jan 25 '11 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Noozer | last post by:
We've created a new database and need to import the data from our old database. Both are MS Access databases and each have multiple tables. The table structure of each table is completely different from each other, but the actual fields of the data we need to move exist in both tables. From what I see in the MS Access help I can only find how to import a complete table from another Access database. How can I import the data from table...
by: S. van Beek | last post by:
Dear reader, Is there somebody who can tell me the VBA code for the following action? To import data from an Excel sheet by referring to a cell name in the Excel sheet.
by: Schultz | last post by:
I would like to know if it is possible to import data from MS Excel 2000 to a dataset using asp.net. The excel file would need to be uploaded to the server from a web page first, before the loaded into a dataset. After the data is stored in the dataset, i would need to write the information to an access db. If it is possible to do this, where can I find help on how to import excel data to a dataset? Thanks!
by: greg | last post by:
Hi, I have a read only access file with a linked table that connects to a sybase database. So I can still add data to the table even though its read only since the table is really in sybase. However, I want to import data from an excel file (either via a macro in excel or access) but I always get an "Error 3051" because the access file is read only. Does anyone know a good workaround to this? I've been using the TransferSpreadsheet...
by: naresh090183 | last post by:
hi to all Am new to this community. can anyone suggest me how to export data from oracle to Ms-Access using forms6i.I have exported data from oracle to Excel and Ms-Word but I cannot able to find a solution for this problem.I do searched a lot please do needful... Regards, Naresh Kumar Murali
by: MarcGA | last post by:
(Excel 2003, Access 2003, XP, novice user here) I can't get Access to accept multiple Excel files to the same table. I can import the spreadsheets to a new table, but I need to import 23 spreadsheets to the same table. I'm not getting a formatting error, just a "file did not import" message. Also, when I do import one-to-one, Access inserts thousands of blank lines before the first line of imported data. How do I keep Access from doing...
by: booboo | last post by:
If someone would tell me if there is a simple or any way to import data from only one field in an Excel spreadsheet to a table field ?
by: tacofinger | last post by:
Hi, How to import MS Query result to Access Table? I have one MS query result from oracle ODBC and total record is 100ku records. I have tried to use link table from Access -import function but it takes a long time to retrieve or refresh data. (because the database is in US and i am working in Asia. Any suggestion would be highly appreciated. Thanks!
by: yemyem | last post by:
Hi Guys, im new to the c# language and im liking it so far. What im currently trying to do is write a windows form application so that when the user clicks on a button the application will read the csv and then import it into an MS Access database. What i can do is read the csv file using the SteamReader and displaying it on a DataGrid. I can also connect and read from the access database ok but im not quite sure how to import the data into...
by: Kelly Armstrong | last post by:
Hi, I am very new to VBA and am trying to set up an import from excel into access. Both are the 2010 version. I pulled the code I am using from a previous thread posted by a user trying to do what I want to do. The problem I am having is that every time I try to run the code I get a message saying "Compile Error: Invalid Outside Procedure" and the file path is highlighted. Any advice on what I am doing wrong would be greatly appreciated. ...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.