2010表自动导入不同的Excel文件到MS访问 [英] Import different excel files into MS Access 2010 tables automatically

问题描述

我想导入所有的Excel文件（具有不同的数据和列）从某些目录中进入的MS Access 2010数据库，创建新表为每个文件。我已经找到了code导入文件到一个表：

I'd like to import all Excel files (with different data and columns) from some directory into MS Access 2010 database, creating new table for each file. I've found the code to import files into one table:

Option Compare Database
Option Explicit

Function DoImport() 

Dim strPathFile As String, strFile As String, strPath As String
 Dim strTable As String
 Dim blnHasFieldNames As Boolean

 ' Change this next line to True if the first row in EXCEL worksheet
 ' has field names
 blnHasFieldNames = True

 ' Replace C:\Documents\ with the real path to the folder that
 ' contains the EXCEL files
 strPath = "C:\Documents and Settings\myName\My Documents\Access Test\"

 ' Replace tablename with the real name of the table into which
 ' the data are to be imported
 strTable = "tablename"

 strFile = Dir(strPath & "*.xls")
 Do While Len(strFile) > 0
       strPathFile = strPath & strFile
       DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
             strTable, strPathFile, blnHasFieldNames

 ' Uncomment out the next code step if you want to delete the
 ' EXCEL file after it's been imported
 '       Kill strPathFile

       strFile = Dir()
 Loop

End Function

不过，我需要创建新表各一次。是否有可能在VBA？

But I need to create new table each time. Is it possible in VBA?

推荐答案

我认为，所有你需要做的是改变目标表名（每对 strTable 的值）时间在这之前 DoCmd.TransferS preadsheet 。

I think all you need to do is change the destination table name (the value of strTable) each time before you do DoCmd.TransferSpreadsheet.

在评论你说你希望从工作簿文件名派生表名。而且，每一次通过你的循环，另一个变量（ strFile ）包含文件名。所以，我认为，你可以剥离的文件扩展名从文件名，并把它作为访问表的名称。

In a comment you said you want the table name to be derived from the workbook file name. And, each time through your loop, another variable (strFile) contains the file name. So I think you could strip the file extension from that file name and use it as the Access table name.

下面是演示了如何可以做一个即时窗口例子...

Here is an Immediate window example which demonstrate how that can be done ...

strFile = "foo.xls"
strTable = Left(strFile, Len(strFile) - 4)
? strTable
foo

如果这种做法是合适的，修改的环在VBA code像这样（未经测试）code片段...

If that approach is suitable, revise the loop in your VBA code like this (untested) code snippet ...

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
    strPathFile = strPath & strFile
    strTable = Left(strFile, Len(strFile) - 4)
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        strTable, strPathFile, blnHasFieldNames
    strFile = Dir()
Loop

这篇关于2010表自动导入不同的Excel文件到MS访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！