Excel多个工作表来访问表(Excel Multiple worksheets to Access tables)

Excel多个工作表来访问表(Excel Multiple worksheets to Access tables)

我收到了一个Excel文件.xlsx中某人的数据库转储，其中包含50多个工作表。 为了让我理解它，我将不得不将其上传到数据库并开始一起提取一些信息。

我更熟悉Access女士，所以现在就待在这里。

如何在Access数据库中将包含所有50个工作表的单个文件分别导入到50个表中？

有人请帮忙，因为我在Access中使用简单的外部数据选项时收到错误消息。

I have received a database dump from someone in a single Excel file .xlsx that has over 50 worksheets in it. In order for me to make sense of it, I will have to upload it to a database and start pulling some information together.

I am more comfortable with Ms Access so staying with it for now.

How can I import this single file with all 50 worksheets inside into 50 tables separately in Access db?

Can someone please help as I am getting error message when using a simple External data option in Access.

最满意答案

使用Excel.Application对象，从Excel Workbook中获取Worksheets集合中的所有名称，并在工作簿中注册Worksheets所有名称：

Dim App As Object 'Excel interface Dim File As Object 'Your file Dim ws As Variant 'Your worksheet Set App = CreateObject("Excel.Application") 'This opens excel application windows Set File = App.Workbooks.Open(fileName) 'This opens your particular excel file (or workbook) For each ws In File 'Register Name in a table named tblSheetNames, Containing a field named 'ShtNm': CurrentDb.Execute "INSERT INTO tblSheetNames(ShtNm) VALUES ('" & ws.Name & "')" Next 'Close the App to allow link to the Excel file App.Close: Set App = Nothing

然后导入所有这些工作表：

'Import all these worksheets: Dim rst as DAO.Recordset Set rst = CurrentDb.OpenRecordset("tblSheetNames") Do While Not rst.Eof 'Link to SpreadSheet, **make sure you add the '!' to the spread-sheet-name for the *Range* parameter** DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, "Temp", fileName, rst!ShtNm & "!" 'Dump sheet into table 'MyTable' and then Delete Link: CurrenDb.TableDefs.Delete("Temp") rst.MoveNext: Loop

添加'！' 到Range参数的spread-sheet-name

Use Excel.Application Object, to get from the Excel Workbook all the names in the Worksheets Collection, and register all names of sheets in the workbook:

Dim App As Object 'Excel interface Dim File As Object 'Your file Dim ws As Variant 'Your worksheet Set App = CreateObject("Excel.Application") 'This opens excel application windows Set File = App.Workbooks.Open(fileName) 'This opens your particular excel file (or workbook) For each ws In File 'Register Name in a table named tblSheetNames, Containing a field named 'ShtNm': CurrentDb.Execute "INSERT INTO tblSheetNames(ShtNm) VALUES ('" & ws.Name & "')" Next 'Close the App to allow link to the Excel file App.Close: Set App = Nothing

Then import all these worksheets:

'Import all these worksheets: Dim rst as DAO.Recordset Set rst = CurrentDb.OpenRecordset("tblSheetNames") Do While Not rst.Eof 'Link to SpreadSheet, **make sure you add the '!' to the spread-sheet-name for the *Range* parameter** DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, "Temp", fileName, rst!ShtNm & "!" 'Dump sheet into table 'MyTable' and then Delete Link: CurrenDb.TableDefs.Delete("Temp") rst.MoveNext: Loop

add the '!' to the spread-sheet-name for the Range parameter