excel - 将数据从 Excel 文件导入 Access 表
问题描述
我正在尝试从用户选择的 Excel 文件中导入数据,并将其数据导入到访问中的表中。
要求用户选择我使用此代码的文件
Private Function importarExcelTabla()
Dim excelMedi As Variant
Dim cuadroSeleccion As Office.FileDialog
Set cuadroSeleccion = Application.FileDialog(msoFileDialogFilePicker)
'Abre el cuadro de seleccion de ficheros
With cuadroSeleccion
.AllowMultiSelect = False
.Title = "Selecciona el archivo por favor"
.Filters.Clear
.Filters.Add "Todos los archivos", "*.*", 1
If .Show = True Then
excelMedi = cuadroSeleccion.SelectedItems(1)
选择后,我使用 transgerSpreadsheet 将 .xlsx 文件从一个范围导入到表中
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "MediPrueba",
excelMedi, False, "A2:L950"
End If
End With
End Function
但我的问题是该表没有填充 excel 数据,而且我将范围从一个文件中放入,但是:
¿ 可以选择没有第一行的所有文档,因此这将适用于其他长度的其他 excel 文件?
先感谢您
解决方案
实际上有从 www.accessmvp.com/KDSnell/EXCEL_Import.htm 查看此代码
此代码通过选择一个起点(右上角)来工作,并一直工作到遇到一个空白行,然后停止。要跳过第一行,请将起点设置为 A2
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean
blnEXCEL = False
' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = True
' Replace C:\Filename.xls with the actual path and filename
' of the EXCEL file from which you will read the data
Set xlw = xlx.Workbooks.Open("C:\Filename.xls", , True) ' opens in read-only mode
' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
Set xls = xlw.Worksheets("WorksheetName")
' Replace A1 with the cell reference from which the first data value
' (non-header information) is to be read
Set xlc = xls.Range("A1") ' this is the first cell that contains data
Set dbs = CurrentDb()
' Replace QueryOrTableName with the real name of the table or query
' that is to receive the data from the worksheet
Set rst = dbs.OpenRecordset("QueryOrTableName", dbOpenDynaset, dbAppendOnly)
' write data to the recordset
Do While xlc.Value <> ""
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 1
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1,0)
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
' Close the EXCEL file without saving the file, and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing
推荐阅读
- javascript - 为什么 vue.config.js 停止在服务器上工作?
- amazon-web-services - 无法 ping AWS EC2 实例
- php - 如何在 laravel 7 中播种一对多关系
- vue.js - d3.append 中未显示文本
- bash - 从具有重复行的文件中提取唯一行
- php - 对带有文本和整数的字符串的正确正则表达式?
- javascript - 为什么我在 Sequelize 中得到“sequelize.literal 不是函数”
- javascript - Firebase JS函数似乎将前导空格添加到变量
- laravel - Docker Laravel 运行端口暴露权限被拒绝
- windows - 如何将一个静态字符串和一个变量名组合成一个已定义的变量名?