VBS SEARCH FOR SPECIFIC INFORMATION IN MULTIPLE SPREADSHEETS

I have multiple Excel spreadsheets on a server at
 S:\SVCDrive\ADQSR EP Project Docs\Site Surveys\Logged\Site Surveys

The names of the surveys are a  6 digit site  number and survey ######survey.xls

What I need to do is search in each of these surveys for the following fields

A84                                    B84
Register eprom level (from database)                  VARIABLE VALUE
A85                                    B85
Confirmed emprom level (from master terminal)            VARIABLE VALUE

On every sheet I need to grab A84 AND B84 , A85 AND B85

Once the search is complete I would like to have a report that looks like this

6 digit number= a84 b84
                        a 85 b85

6 digit number= a84 b84
                        a 85 b85

6 digit number= a84 b84
                        a 85 b85

There are several hundred surveys to open and grab info from.

Thanks



                           
irishmanjbAsked:
Who is Participating?
 
samopalConnect With a Mentor Commented:
Are you running this as a script? then you have to change all debug.print command s to your output commands.
0
 
samopalCommented:
  Dim fso as object, fold as object, fil as object, exl as object
   Set exl = CreateObject("Excel.Application")
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set fold = fso.GetFolder("S:\SVCDrive\ADQSR EP Project Docs\Site Surveys\Logged\Site Surveys")
   For Each fil in fold.Files
      if Right(fil.name,10) = "survey.xls" then
          exl.Workbooks.open "S:\SVCDrive\ADQSR EP Project Docs\Site Surveys\Logged\Site Surveys\" & fil.name
debug.print Left(fil.name,6)
debug.print exl.ActiveSheet.Range("A84"),exl.ActiveSheet.Range("B84")
debug.print exl.ActiveSheet.Range("A85"),exl.ActiveSheet.Range("B85")
          exl.ActiveWorkbook.Close
      end if
   Next
   exl.quit
   set exl=nothing
   set fso=nothing

D'Al
0
 
irishmanjbAuthor Commented:
This function seems to be opening and changing all of the surveys somehow because I am being prompted to save the changes in all surveys.

Also if it makes this any easier.  I can get by with the 6 digit number in one column and the value from b85 in the other for all of the spreadsheets.

thanks          
0
 
samopalCommented:
<<I am being prompted to save the changes in all surveys
Change line
 exl.ActiveWorkbook.Close
to
 exl.ActiveWorkbook.Close False
0
 
irishmanjbAuthor Commented:
Thanks
I made the change and ran it but nothing seems to happen.
Ther macro runs but I am not getting any results in the active workbook.
0
All Courses

From novice to tech pro — start learning today.