Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > Search and Extract values from closed Excel files into new Excel file

Thread Tools Display Modes
Old 11-17-2009, 06:31 AM
begemotas76 begemotas76 is offline
Join Date: Nov 2009
Posts: 3
Default Search and Extract values from closed Excel files into new Excel file

Hello to everyone,
I have just registered and I am already posting for some hints.
I will try to explain which my problem is:

there are several excel files containing various info with different sheet layouts.

I should search string (such as "Total Apples") in all of them and then locate the cell containing the value (for example "34").

All these extracted data should be inserted in new excel file.

I am thinking about writing something in Visual Basic for Application.

Could anyone help me implementing the routine?

Thanks in advance for your help.

Reply With Quote
Old 11-17-2009, 07:26 AM
KiltedNinja's Avatar
KiltedNinja KiltedNinja is offline
Join Date: May 2009
Posts: 118

Hi there,
I can give you a hand with this. I'm working on a piece of code which can maybe achieve something like this. I'll see if I can figure out what the requirements would be and post some more ideas later.

In the meantime, some ideas first of all ;

You'll need to know the filenames of the workbooks. These should get stored in an array.
You'll need a loop which will go through the workbooks,
A Loop which will go through each sheet in the workbook,
A Variable which will contain the address of the "Total Apples" cell when it's found,
Using the address variable, locate the desired cell which contains the value you want, using .Offset. This value should be added to an array also.

Finish the loops, and your end result should be an array of information which tells you the value, the workbook it was found on, and in which sheet it was found.

Infact, here you go - just wrote this for you. Rip it apart and have a look at it, try to figure out how it works. Does a complex action, but it's quite simple to see how it works once you look at it and follow it through.

Sub Extract_Totals()

    SEARCHSTRING = "Total Apples"

Dim mWorkbooks() As String, mNumberOfWorkbooks As Long, mWorkbookCounter As Long

Dim mValuesFound() As Double, mValueFoundCounter As Long, mCounter As Long
Dim mWorkbookFoundIn() As String
Dim mWorksheetFoundIn() As String

Dim ValueOFFSET_Row As Long
Dim ValueOFFSET_Col As Long
Dim mSheetCount As Long, mSheetCounter As Long
Dim mFoundAddress As String

    mNumberOfWorkbooks = 3
    ReDim mWorkbooks(1 To mNumberOfWorkbooks)
        mWorkbooks(1) = "C:\Temp\wb1.xls"
        mWorkbooks(2) = "C:\Temp\wb2.xls"
        mWorkbooks(3) = "C:\Temp\wb3.xls"

    ValueOFFSET_Row = 0
    ValueOFFSET_Col = 1

mWorkbookCounter = 1
    For mWorkbookCounter = 1 To 3
    Workbooks.Open Filename:=mWorkbooks(mWorkbookCounter)
    mSheetCount = ActiveWorkbook.Sheets.Count
    For mSheetCounter = 1 To mSheetCount
    mFoundAddress = ""
        On Error Resume Next
        mFoundAddress = Cells.Find(What:=SEARCHSTRING, After:=ActiveCell, LookIn:=xlFormulas, _
                        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=True, SearchFormat:=False).Address
        On Error GoTo 0

    If mFoundAddress <> "" Then
        mValueFoundCounter = mValueFoundCounter + 1

        ReDim Preserve mValuesFound(1 To mValueFoundCounter)
        mValuesFound(mValueFoundCounter) = ActiveSheet.Range(mFoundAddress).Offset(ValueOFFSET_Row, ValueOFFSET_Col).Value

        ReDim Preserve mWorkbookFoundIn(1 To mValueFoundCounter)
        mWorkbookFoundIn(mValueFoundCounter) = mWorkbooks(mWorkbookCounter)

        ReDim Preserve mWorksheetFoundIn(1 To mValueFoundCounter)
        mWorksheetFoundIn(mValueFoundCounter) = ActiveSheet.Name
    End If
    Next mSheetCounter

    Next mWorkbookCounter

Dim mMSG As String
    mMSG = "Values :" & vbCr & vbCr
    If mValueFoundCounter = 0 Then mMSG = mMSG & "NO VALUES FOUND"
    For mCounter = 1 To mValueFoundCounter
        mMSG = mMSG & mWorkbookFoundIn(mCounter) & vbTab
        mMSG = mMSG & mWorksheetFoundIn(mCounter) & vbTab
        mMSG = mMSG & mValuesFound(mCounter)
        mMSG = mMSG & vbCr
    Next mCounter

        MsgBox mMSG
End Sub

Last edited by KiltedNinja; 11-17-2009 at 07:37 AM.
Reply With Quote
Old 11-18-2009, 10:20 AM
begemotas76 begemotas76 is offline
Join Date: Nov 2009
Posts: 3

Many thanks KiltedNinja, I am trying to adapt it to my needs.
Anyway, I may need to make the code independent from files locations and let the code span through a fix directory containing all .xls files (continuously increasing in number). The sheet containing the value is always the same, thus I don't need to go through all sheets within the workbook.
Moreover, I may also need to change the string to be searched time by time.
If you could help me (more than you've already done) I would appreciate it very much.
Thanks again for the help.
Reply With Quote
Old 11-19-2009, 03:47 AM
begemotas76 begemotas76 is offline
Join Date: Nov 2009
Posts: 3

I tried it out and it works at first glance!!!!
Just a minimal fix:
mSheetCount = ActiveWorkbook.Sheets.Count
For mSheetCounter = 1 To mSheetCount
Sheets(mSheetCounter).Activate (not Select)
Now I have just to implement the aother features.
Thanks again KiltedNinja.
Reply With Quote

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..