当前位置：文江博客话题详情

将 Excel 文件读入 Access 数据库的最佳方法

发布于 2024-09-03 01:59:22 字数 197 浏览 3 评论 0原文

从 Access 2007 应用程序中读取（仅读取）Excel 文件的“最佳”方法是什么？我只想循环遍历行并将数据放入 Access 表中。

我不想手动导入（“获取外部数据”对话框），而是通过 VBA 进行导入。用户获得一个带有“浏览”按钮的表单，然后指向一个具有已定义内容/格式的 Excel 文件。之后，VBA 代码读取数据并将其放入 Access 数据库中。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

謸气贵蔟 2024-09-10 01:59:22

您可以尝试 DoCmd.TransferSpreadsheet 方法。

DoCmd.TransferSpreadsheet acImport, , "from_excel","C:\Access\demo.xls", True

这会将电子表格数据导入到名为 from_excel 的表中，并假设电子表格的第一行包含字段名称。请参阅 TransferSpreadsheet 的访问帮助或在线访问此处，了解更多详情。

You could try the DoCmd.TransferSpreadsheet method.

DoCmd.TransferSpreadsheet acImport, , "from_excel","C:\Access\demo.xls", True

That imports spreadsheet data into a table named from_excel, and assumes the first row of the spreadsheet contains field names. See Access help for TransferSpreadsheet or online here, for more details.

回复收藏 0 原文

债姬 2024-09-10 01:59:22

如果要读取整个电子表格，可以将 Excel 电子表格直接导入到 Access 中。请参阅此处或此处。

您还可以选择链接到 Excel 电子表格而不是导入它。这样，对 Excel 电子表格的任何更改都将反映在链接表中。但是，您将无法在 Access 中进行更改。

第三种选择是在 Access 中编写一些 VBA 代码来打开记录集并读取电子表格。请参阅 KeithG 在此线程。您可以执行以下操作在 VBA 中打开电子表格：

Dim xl As Excel.Application
Dim xlsht As Excel.Worksheet
Dim xlWrkBk As Excel.Workbook

Set xl = CreateObject("Excel.Application")
Set xlWrkBk = GetObject("H:/ggg.xls")
Set xlsht = xlWrkBk.Worksheets(1)

If you want to read the entire spreadsheet in, you can import an Excel spreadsheet directly into Access. See here or here.

You can also choose to link to the Excel spreadsheet instead of importing it. That way any changes to the Excel spreadsheet will be reflected in the linked table. However, you won't be able to make changes from within Access.

A third option is to write some VBA code within Access to open a recordset and read the spreadsheet in. See the answers from KeithG in this thread. You can do something like this to open the spreadsheet in VBA:

Dim xl As Excel.Application
Dim xlsht As Excel.Worksheet
Dim xlWrkBk As Excel.Workbook

Set xl = CreateObject("Excel.Application")
Set xlWrkBk = GetObject("H:/ggg.xls")
Set xlsht = xlWrkBk.Worksheets(1)

回复收藏 0 原文

南笙 2024-09-10 01:59:22

尝试这样的操作：

Dim excelApp As Excel.Application
Dim workbook As Excel.Workbook
Dim worksheet As Excel.Worksheet

Set excelApp = CreateObject("Excel.application")
Set workbook = excelApp.Open("C:\someFileName.xls")
Set worksheet = workbook.Worksheets(1)

然后循环遍历行和列，从单元格中提取数据，并将其插入数据库中。（您可以使用 worksheet.cells 方法。）尝试在 google 上搜索代码示例。

Try something like this:

Dim excelApp As Excel.Application
Dim workbook As Excel.Workbook
Dim worksheet As Excel.Worksheet

Set excelApp = CreateObject("Excel.application")
Set workbook = excelApp.Open("C:\someFileName.xls")
Set worksheet = workbook.Worksheets(1)

And then loop through the rows and columns, pull the data from the cells, and insert it into the database. (You can use the worksheet.cells method.) Try searching on google for code samples.

回复收藏 0 原文

影子是时光的心 2024-09-10 01:59:22

下面是我读取excel文件和所有工作表名称的方法：

Function listOfWorksheet(filename As String) As Collection

Set dbExcel = OpenDatabase(filename, False, True, "excel 8.0")
For Each TableDef In dbExcel.TableDefs
    Debug.Print TableDef.Name
Next

End Function

现在，您可以使用工作表的名称来读取整个内容：

Function ReadMyObjects(filename as String, wsName as String) As Collection
On Error GoTo label_error
Set results = New Collection
Dim countRows As Integer
Set dbExcel = OpenDatabase(filename, False, True, "excel 8.0")
Set excelRs = dbExcel.OpenRecordset(wsName, dbOpenSnapshot)

Do While Not excelRs.EOF
    'Data Rows
     Dim item As MyObject 'a custom object defined by you.
     Set item = New MyObject
     item.ABC = Nz(excelRs.Fields("COLUMN_ABC").Value, "")
     item.DEF = Nz(excelRs.Fields("COLUMN_DEF").Value, "")
     results.Add item
     excelRs.MoveNext
Loop
excelRs.Close
Set ReadMyObjects= results
GoTo label_exit
label_error:
    MsgBox "ReadMyObjects" & Err.Number & " " & Err.Description
label_exit:
End Function

Hereafter my method to read an excel file and all the worksheet names:

Function listOfWorksheet(filename As String) As Collection

Set dbExcel = OpenDatabase(filename, False, True, "excel 8.0")
For Each TableDef In dbExcel.TableDefs
    Debug.Print TableDef.Name
Next

End Function

Now, you can use the name of the worksheet to read the whole content:

Function ReadMyObjects(filename as String, wsName as String) As Collection
On Error GoTo label_error
Set results = New Collection
Dim countRows As Integer
Set dbExcel = OpenDatabase(filename, False, True, "excel 8.0")
Set excelRs = dbExcel.OpenRecordset(wsName, dbOpenSnapshot)

Do While Not excelRs.EOF
    'Data Rows
     Dim item As MyObject 'a custom object defined by you.
     Set item = New MyObject
     item.ABC = Nz(excelRs.Fields("COLUMN_ABC").Value, "")
     item.DEF = Nz(excelRs.Fields("COLUMN_DEF").Value, "")
     results.Add item
     excelRs.MoveNext
Loop
excelRs.Close
Set ReadMyObjects= results
GoTo label_exit
label_error:
    MsgBox "ReadMyObjects" & Err.Number & " " & Err.Description
label_exit:
End Function

回复收藏 0 原文

~没有更多了~