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屋！