导入几十XLS文件到访问 [英] Importing dozens of xls files into Access

问题描述

我有一个Excel US preadsheet，有5个不同的选项卡。我想创建这个S preadsheet在获得重新由5个不同的表psented $ P $。

I have an excel spreadsheet that has 5 different tabs. I would like to create this spreadsheet in access represented by 5 different tables.

目前我的第一次尝试是在要导入的文件。我有以下的code

My first attempt at this is to import in the files. I have the following code

Function ImportFiles()
Dim myfile
Dim myDir


mypath = "C:\Data\Dashboard\"
myDir = Directory
myfile = Dir(mypath & "*.xlsx")
While myfile <> ""
Debug.Print "importing" & myfile
DoCmd.TransferSpreadsheet acImport, , "Data", myfile, True
myfile = Dir()
Wend

End Function

我在访问数据库五个表。我是否需要写一些Excel的VBA来创建5个文件，它删除每个5选项卡，然后将这些5个选项卡导入到在我访问数据库中的相应的表？或者有没有什么办法，以适应我的上述code将数据的第一个选项卡中的每个表中插入第一个表，第二个选项卡进入第二个表等？

I have five tables in my access database. Do I need to write some excel vba to create 5 files that strips out each of the 5 tabs and then import those 5 tabs into the corresponding tables within my access database? Or is there any way to adapt my above code to put the 1st tab of data within each sheet into the first table, the second tab into the 2nd table etc ?

推荐答案

您可以用code previously写：的 http://www.accessmvp.com/kdsnell/EXCEL_Import.htm#ImpAllWktsSepTbl

You can use the code previously written : http://www.accessmvp.com/kdsnell/EXCEL_Import.htm#ImpAllWktsSepTbl

这篇关于导入几十XLS文件到访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！