把另外的excel的数据导进来

最新推荐文章于 2025-12-12 12:12:20 发布

原创最新推荐文章于 2025-12-12 12:12:20 发布 · 711 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #string #properties #integer #object

此博客提供了一段VBA代码，用于从Excel文件中读取数据。代码通过ADODB连接Excel文件，执行SQL查询获取数据，并将数据逐行写入当前工作表。涉及数据库连接、记录集操作等，是信息技术领域中Excel数据处理的实用代码。

Sub new_load()
    Dim dbConnection As New ADODB.Connection
    Dim rsADO As New ADODB.Recordset
    Dim cat As Object
    Dim tbl As Object
    Dim lRow As Long
    Dim szBookName, mycCMD As String
    Dim szConnect As String
    Dim szTableName As String

    szBookName = ThisWorkbook.Path & "/data.xls"

    szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & szBookName & ";" & _
                "Extended Properties=Excel 8.0;"
    Set dbConnection = New ADODB.Connection
    dbConnection.Open szConnect
    myCmd = "SELECT master,sub,sales FROM [Sheet1$] "
    rsADO.Open myCmd, dbConnection, adOpenStatic
    Dim i As Integer
'    i = rsADO.recordcount
rsADO.MoveFirst
    For i = 1 To rsADO.recordcount

        If Not rsADO.EOF Then
            Cells(i, 1) = rsADO.Fields(0)
            Cells(i, 2) = rsADO.Fields(1)
            Cells(i, 3) = rsADO.Fields(2)
        rsADO.MoveNext
        End If

    Next

    dbConnection.Close
    Set cat = Nothing
    Set dbConnection = Nothing
End Sub