如何将Excel 2003中命名范围的数据导入Access？ [英] How to import data from named ranges in Excel 2003 into Access?

问题描述

我没有留下这个问题！我只是尝试将Excel 2003中命名范围的数据导入Access 2003中的同名表。我发现除非我有要导入的电子表格，OPEN，否则无法找到命名范围。我甚至看不到他们试图手动导入数据，除非电子表格是开放的！


我不希望电子表格打开，因为这导致Excel的实例保持活动状态，如Windows任务管理器/进程中所述（另一个问题，但是开始这个​​漫长的噩梦）。


多年来我多次使用此代码的变体，但是现在它不起作用。我已经安装了Office 2007转换器，我想知道是否导致了这个问题。这是失败的代码行：

[DoCmd.TransferSpreadsheet acImport，acSpreadsheetTypeExcel9，" tmp" &安培; tbl，FileName，True，rng]


tbl =一个字符串，它是Access中表的名称，也是Excel中的命名范围。 " TMP" &安培; tbl是一个保存导入的临时文件。电子表格和Access表格中的所有字段名称都相同。

文件名是电子表格的路径和名称。


请帮助!!

解决方案

以下代码将Excel电子表格中的命名范围导入现有的访问表。电子表格需要不为'打开''才能成功完成此过程。

展开 | 选择 | Wrap | 行号

ADezii，该代码与我发布的代码没有什么不同。我得到了与我一样的错误。然而，当我打开电子表格时，它运行得很好。这就是问题！


感谢您的努力，

Laurel

1. 变量rng的范围分配是什么？
2. 电子表格的第一行是否有字段名称？
3. 出于好奇，您可以上传吗？电子表格中没有任何机密信息？

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.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "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!!

解决方案

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

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,
Laurel

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?

这篇关于如何将Excel 2003中命名范围的数据导入Access？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！