CODEWITHSUNDEEP
vbaexcel
whitesox130
whitesox130

Reputation: 13

Searching multiple Excel workbooks for value in same cell and same sheet

I'm learning VBA at a new job and was assigned a project that involves checking a value in around 100 workbooks. The value is contained in the same cell (I12) in a sheet with the same name in every one of the 100 workbooks. Is it possible to use SQL to somehow pass the value from just that one cell in each workbook to a variable so I can compare them? My current code opens up each of the workbooks to compare the values, which works but is quite time-consuming.

Upvotes: 1

Views: 177

Answers (1)

L42
L42

Reputation: 19737

If it's just one Cell, you might want to try ExecuteExcel4Macro?
Something like:

Sub Test()
    Dim fexcel
    Dim fpath As String, fval As String, aval
    fpath = "C:\Users\User.Name\FolderName\" '~~> change to suit
    fexcel = Dir(fpath, vbNormal)
    Do While fexcel <> ""
        '~~> Sheet1 should be the sheet name, R12C9 is I12 in R1C1 notation
        fval = "'" & fpath & "[" & fexcel & "]Sheet1'!R12C9"
        aval = ExecuteExcel4Macro(fval)
        '~~> Do your comparison here or dump information in an array
        fexcel = Dir
    Loop
End Sub

Upvotes: 1

Related Questions