VBA Read Excel File

Here is a question I received

“Thanks for the video, I have a question.How to read from a group of closed excel files searching for a certain value in say column F then if that value is found then copy that row to a workbook and continue search for then next value ”

So I thought about it and answered in word and a video example:

“You could query the closed Excel file with SQL,
or you could do what I demonstrate in this video…”


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
'========================================================
'DESIGNED & CODED BY LOEBLCOM SERVICES
'ERIK LOEBL(713)409-7041
'EMAIL: erik@loeblcomservices.com
'WEB:   http://loeblcomservices.com
'========================================================


Public Function query_excel_file() As Boolean
   
    Dim strResults As String
    Dim strCarrier As String
   
    'PURPOSE: Query An Excel File
   
    '*************************************
    'clear 'Excel File Contents' worksheet
    '*************************************
    Worksheets("Excel File Contents").Select
    Range("A2:E65000").Clear
   
    '*************************************
    'now populate copy the orders over
    '*************************************
    Dim strSQL As String
    Dim strReadWkbk As String
    Dim intRow As Integer
   
    'Need to reference the:
    '   Microsoft ActiveX Data Objects 2.5 Library
    Dim s_rst As ADODB.Recordset
    Dim s_cnn As ADODB.Connection 's for sub connection
   
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
   
    strCarrier = Range("G9") 'get the carrier's name
   
    Set s_cnn = New ADODB.Connection

    strReadWkbk = "read_this_excel_file.xls"


    s_cnn.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
        "DBQ=" & ThisWorkbook.Path & "\" & strReadWkbk & "; ReadOnly=True;"
   

    Set s_rst = New ADODB.Recordset
   

    strSQL = "SELECT Employee,Customer,[Order Date],[Shipped Date],[Ship Via] FROM [Sheet1$] WHERE  [Ship Via] = '" & strCarrier & "'"
       
    s_rst.Open strSQL, s_cnn, adOpenStatic, adLockOptimistic
   
    intRow = 2 'start inserting values at this row
   
    If Not s_rst.EOF Then
        s_rst.MoveFirst
       
        Do Until s_rst.EOF
           
            Range("A" & intRow) = s_rst.Fields(0)
            Range("B" & intRow) = s_rst.Fields(1)
            Range("C" & intRow) = s_rst.Fields(2)
            Range("D" & intRow) = s_rst.Fields(3)
            Range("E" & intRow) = s_rst.Fields(4)
                   
            intRow = intRow + 1
            s_rst.MoveNext
        Loop
       
    Else
        MsgBox "No records"
    End If
   
    s_rst.Close
    Set s_rst = Nothing
   

    s_cnn.Close
    Set s_cnn = Nothing
       
    query_excel_file = True
   
    Exit Function

End Function

Click here for the file:
VBA Read Excel File.zip

For more Excel related posts, take a look at our sister site, http://www.vbastring.com

Offsite Related Information:

“Go Fund Me” Page


($5 suggested amount)

(…10% of your gift amount will go to charity)

Free! Subscribe To Our YouTube Channel!

Free MS Access VBA Programming Course

Facebooktwitterredditpinterestlinkedinmailby feather
Tags: , , , , ,