如何使用过滤器导入多个Excel文件的数据 [英] How do I Import Multiple Excel Files' Data Using Filters

问题描述

嗨。


我在一个目录中有数千个excel文件。我想从excel文件构建Access表。

每个excel文件都有一个工作表，但我不需要每列和低工作表。


我试图使用可视化基本代码将该目录中每个excel文件的FILTERED Excel工作表导入访问表。


我能够构建up（有人帮我）视觉基本代码将多个excel文件导入一个Access表而不用FILTERING。


有人可以说我只能导入而不进行过滤并在Access中进行查询。

问题是每个excel工作表都有太多的行和列，我不需要它，我需要将数千个excel文件导入一个访问表。最终我将面临Access表大小问题。


我需要改进我的可视化基本代码，以便只导入我要导入到Access表中的选定行和列。 br />

我的工作表格式如下


ID月份日开放高低...... VOL ..... SETTLE TradeDate。

OD 7 2011 15 ................... 546 7/15/2011

OD 8 2011 18 .. ................ 658 7/15/2011

..

FD 7 2011 15 ...... ............... 987 7/15/2011

..

..

SM 7 2011年15 ................... 632 7/15/2011

...

KW 7 2011 15 ................... 838 7/15/2011

....


我需要导入的只是找到FD。和SM在第一列（ID）中获取行的其余部分（或者甚至更好的是在该行中仅获取ID，月，年，日，结算和交易数据）


任何人都可以帮我过滤工作表中的数据并导入Access表吗？

（我使用Access 2007版本）


非常感谢你。



仅供参考，以下是用于将多个Excel文件导入到一个Access表而无需过滤的WORKS的可视化基本代码。

展开 | 选择 | Wrap | 行号

解方案

你有没有考虑过一个链接表链接到Excel工作簿，其链接你每次需要一个新的工作簿时间而改变。这样，您可以使用基于链接的Excel表格的APPEND查询将数据复制到适当的表格，并过滤为仅包括FD表格。 &安培; " SM"类型记录。您也可以在该场景中指定您想要/需要的列。

我同意NeoPa。我想我会假设输入Excel文件的常量名称，而不是为每个Excel工作表创建一个新的链接。


要进行此设置，请将一个Excel文件复制到文件名ExcelFile.xls。然后在.mdb中创建该文件的ODBC链接。然后，将代码的第27/28行替换为：

展开 | 选择 | Wrap | 行号

我相信For / Next loop可以替换为

展开 | 选择 | Wrap | 行号

Hi.

I have thousands of excel files in one directory. I want to build up the Access table from excel files.
Each excel file has one worksheet, but I don''t need every column and low of the worksheet.

I am trying to import a FILTERED Excel worksheet of every excel files in that directory into an access table using visual basic code.


I was able to build up (someone helped me) visual basic code to import multiple excel files to one single Access table without FILTERING.

Someone can say that I can just import without filtering and do query in Access.
The problem is that each excel worksheet has too many row and columns that I don''t need it, and I need to import thousands of excel files to one access table. Eventually I will face Access table size problem.


I need to improve my visual basic code to import only selected rows and columns that I want to import into Access table.

my worksheet is in the following format

ID Month Year Day OPEN HIGH LOW ...VOL..... SETTLE TradeDate.
OD 7 2011 15 ................... 546 7/15/2011
OD 8 2011 18 ..................658 7/15/2011
..
FD 7 2011 15 .................... 987 7/15/2011
..
..
SM 7 2011 15 .................... 632 7/15/2011
...
KW 7 2011 15 .................... 838 7/15/2011
....

All I need to import is to find "FD" and "SM" in the 1st column (ID) and get the rest of the row (or even better one is to get only ID, Month, Year, Day, Settle, and Tradedate data in that row)

Anyone could help me to filter the data in worksheet and import into Access table?
(I use Access 2007 version)

thank you very much.


FYI, the following is the visual basic code that WORKS for importing multiple excel files to one single Access table without FILTERING.

Expand|Select|Wrap|Line Numbers

解决方案

Have you considered a linked table that links to an Excel workbook and whose link you change each time a new workbook is required. That way you can copy the data across to your proper table using an APPEND query based on the linked Excel table and filtered to include only "FD" & "SM" type records. You can specify whichever columns you want/need in that scenario too.

I agree with NeoPa. I think I would assume a constant name for the input Excel file rather than make a new link for every Excel sheet.

To set this up, copy one of your Excel files to file name ExcelFile.xls. Then Create an ODBC link in your .mdb to that file. Then, replace line 27/28 of your code with this:

Expand|Select|Wrap|Line Numbers

I believe that For/Next loop could be replaced with

Expand|Select|Wrap|Line Numbers

这篇关于如何使用过滤器导入多个Excel文件的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！