Opening a workbook based on a cell value

T

Todd

Not sure if this is possible but here goes:

I need to be able to look through a column of cells and
open each workbook whose name corresponds to the value in
each cell one at a time. Example:

Column H
931C
941
921B
900B

In this example it would look at column H, open 931C.xls,
do some "stuff" (including closing the workbook after it's
done),open 941.XLS, do its "stuff", open 921B.xls....

The "stuff" I can handle, just have no clue how to loop
through the column and open the files like I described.
Any help would be GREATLY appreciated.
 
C

Chip Pearson

Todd,

Try something like

Dim Rng As Range
Dim WB As Workbook
For Each Rng In Range("H1:H100") ' << change range as needed
If Rng.Value <> "" Then
Set WB = Workbooks.Open(ThisWorkbook.Path & "\" &
Rng.Value & ".xls")
' do something with WB
WB.Close SaveChanges:=True
End If
Next Rng


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
T

Todd

Perfect...Thanks a million Chip
-----Original Message-----
Todd,

Try something like

Dim Rng As Range
Dim WB As Workbook
For Each Rng In Range("H1:H100") ' << change range as needed
If Rng.Value <> "" Then
Set WB = Workbooks.Open(ThisWorkbook.Path & "\" &
Rng.Value & ".xls")
' do something with WB
WB.Close SaveChanges:=True
End If
Next Rng


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com







.
Click to expand...
 
Top