将Excel工作表范围导入到MS Access表 [英] Importing Excel worksheet range to Ms Access Table

问题描述

下午好，

我创建了一个宏，该宏将数据上传到Access数据库(均在我的桌面上).问题是，当我尝试扩大范围时，我总是收到错误消息.

I have created a Macro that uploads data to a access database ( both on my desktop). The problem is it I keep getting errors when I try to expand the range.

我以为这很简单，但似乎是我所忽略的东西.

I presumed it would be something simple but seems to be something I am overlooking.

这是代码-基本上我想包括该列或将其设置为动态范围?你能帮忙吗?

here is the code - basically I would like to include the column or set it to a dynamic range? can you please help?

Sub AccessCode()

    Application.ScreenUpdating = False

    Dim db As Database
    Dim rs As DAO.Recordset

    Set db = OpenDatabase("C:\Users\user\Desktop\Test Copy.accdb")
    Set rs = db.OpenRecordset("Fact Table", dbOpenTable)

    rs.AddNew
    rs.Fields("GUID") = Range("g2").Value
    rs.Fields("StageID") = Range("h2").Value
    rs.Fields("Sync Date") = Range("i2").Value
    rs.Fields("Forecast HP") = Range("j2").Value
    rs.Fields("Owner Id") = Range("k2").Value
    rs.Fields("Recent Modified Flag") = Range("L2").Value
    rs.Fields("Upload Date") = Range("M2").Value

    rs.Update
    rs.Close
    db.Close

    Application.ScreenUpdating = True
    MsgBox " Upload To PMO Database Successful."

End Sub

推荐答案

您可以使用查询而不是遍历记录集:

You can use a query instead of iterating through a recordset:

Sub AccessCode()
    Application.ScreenUpdating = False
    Dim db As Database
    Dim rs As DAO.Recordset

    Set db = OpenDatabase("C:\Users\user\Desktop\Test Copy.accdb")
    db.Execute "INSERT INTO [Fact Table] ([GUID], [StageID], etc) " & _
    "SELECT * FROM [SheetName$G:M] " & _
    "IN """ & ActiveWorkbook.FullName & """'Excel 12.0 Macro;HDR=No;'"
End Sub

这具有许多优点，例如通常更快，因为您不必遍历所有字段.

This has numerous advantages, such as often being faster because you don't have to iterate through all the fields.

如果要从Access而不是Excel触发导入，则甚至不需要VBA来执行查询.

If you would trigger the import from Access instead of Excel, you wouldn't even need VBA to execute the query.

这篇关于将Excel工作表范围导入到MS Access表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！