Extracting data from workbooks. Help.

[Tom Weston]

hello
I have a situation where there are several workbooks, of variable name,
contained within a folder on my hard drive. I have a master workbook in a
folder higher up the same directory tree that I want to use to gather
information from the other workbooks of variable names. The cell, A1, that I
am interested in is on sheet "OnCall" in each of the workbooks of variable
name. Is there any way that I can open the master workbook and then
automatically extract the data from cell A1 in each of the other variable
name workbooks? I hope that as clearer than mud?

hopefully

Tom

 

[Dave Peterson]

Here's a macro that will show you the File|Open dialog. Go to your directory
and select as many (or as few workbooks) that you want to consolidate.

Option Explicit
Sub testme01()

Application.ScreenUpdating = False

Dim curWks As Worksheet
Dim testWks As Worksheet
Dim myFileNames As Variant
Dim fCtr As Long
Dim nextWkbk As Workbook
Dim destCell As Range

Set curWks = Workbooks.Add(1).Worksheets(1)

myFileNames = Application.GetOpenFilename _
(FileFilter:="Excel files, *.xls", _
MultiSelect:=True)

If IsArray(myFileNames) Then
Application.ScreenUpdating = False
With curWks
Application.EnableEvents = False
Set destCell = .Range("a1")
For fCtr = LBound(myFileNames) To UBound(myFileNames)
Set nextWkbk = Workbooks.Open(Filename:=myFileNames(fCtr), _
ReadOnly:=True, UpdateLinks:=0)

destCell.Value = nextWkbk.FullName
Set testWks = Nothing
On Error Resume Next
Set testWks = nextWkbk.Worksheets("onCall")
On Error GoTo 0

If testWks Is Nothing Then
destCell.Offset(0, 1).Value = "Missing OnCall Worksheet!"
Else
destCell.Offset(0, 1).Value = testWks.Range("a1").Value
End If
Set destCell = destCell.Offset(1, 0)

nextWkbk.Close savechanges:=False
Next fCtr
End With
Application.EnableEvents = True
Else
MsgBox "Ok, Quitting"
End If

Application.ScreenUpdating = True

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Tom Weston]

Dave
Thanks for your help, it works really well.

Tom