VBA:DoCmd 传输电子表格 [英] VBA: DoCmd Transferspreadsheet
问题描述
我正在尝试将 Excel 工作表导入 Access 表中.这是我的代码:
Im trying to import an excel worksheet into Access table. Here is my code:
Sub test2()
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
Dim fd As Object
Set fd = xlApp.Application.FileDialog(msoFileDialogFilePicker)
Dim selectedItem As Variant
If fd.Show = -1 Then
For Each selectedItem In fd.SelectedItems
Debug.Print selectedItem
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "POR", selectedItem, True, "POR Plan!A1:Z100"
Next
End If
Set fd = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub
我在 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "POR", selectedItem, True, "POR Plan!A1:Z100" 行出现错误.当我不指定范围并省略POR 计划!A1:Z100"时,它需要我的 excel 工作簿中的第一个工作表并且工作正常.但我想得到 POR 计划工作表.
I get an error at the DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "POR", selectedItem, True, "POR Plan!A1:Z100" line. When I dont specify the range and omit "POR Plan!A1:Z100", it takes the very first worksheet in my excel workbook and works fine. But I would like to get the POR Plan worksheet.
推荐答案
您可以从 Excel 导入到 Access(在 Access 中运行).
You can import from Excel into Access (this runs in Access).
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 = False
' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Documents\"
' 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
或者,将数据从 Excel 推送到 Access(在 Excel 中运行).
Or, push data from Excel to Access (this runs in Excel).
Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\FolderName\DataBaseName.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
这两个示例脚本还有其他几种变体.做一些谷歌搜索,你会得到各种各样的想法.
There are several other variations of these two sample scripts. Do some Googling and you will get all kinds of ideas for all kinds of things.
这篇关于VBA:DoCmd 传输电子表格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!