Excel Data Extraction with VB

One common method in VB for extracting Excel data is by using ADO (ActiveX Data Objects) to connect to an Excel workbook and execute SQL queries. Below is an example code showcasing how to extract data from an Excel workbook.

Sub ExtractDataFromExcel()

    Dim conn As Object
    Dim rs As Object
    Dim strConn As String
    Dim strSQL As String
    Dim i As Integer

    '创建ADO连接
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    '连接到Excel工作簿
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\to\your\excel\file.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;"";"
    conn.Open strConn

    '执行SQL查询
    strSQL = "SELECT * FROM [Sheet1$]"
    rs.Open strSQL, conn

    '提取数据并输出到Immediate窗口
    Do While Not rs.EOF
        For i = 0 To rs.Fields.Count - 1
            Debug.Print rs.Fields(i).Value
        Next i
        rs.MoveNext
    Loop

    '关闭连接
    rs.Close
    conn.Close

    '释放对象
    Set rs = Nothing
    Set conn = Nothing

End Sub

In the code above, we begin by creating an ADO connection object and a recordset object. Next, we use the CreateObject method to create an ADO connection object and connect it to the Excel workbook. We then execute an SQL query to select all data in the workbook. Finally, we use a Do While loop to iterate through the data in the recordset and output it to the Immediate window.

Please note that you need to replace C:\path\to\your\excel\file.xlsx with the actual file path to your Excel file. You also need to modify the SQL query statement according to your specific needs to select the particular data you want to extract.