PDA

View Full Version : Open multiple workbooks by loop through cells



crujazz
06-05-2013, 12:13 PM
Good evening all,

I'm looking for a VBA code that will opens multiple workbooks, hide them, extract data within the hidden workbooks and close them. Let me start explaining my plan.

In a project workbook i have in colomn A Januari etc etc
In the same workbook in column B i've merged different cells to create filename.

Jan 00071.xls
Feb 00072.xls

What I want is to by using a vba to open the .xls files, get the data and close them. The vba has to looop through all the cells and open the xls files.

Any suggestion would be great.

Sub RunTotals()
If Sheets("cCode").Range("$b$17") = True Then

PathNameContact = Range("$E$1").Value
ConJan = Range("G2").Value
ConFeb = Range("G3").Value


Workbooks.Open Filename:=PathNameContact & "\" & ConJan
Workbooks.Open Filename:=PathNameContact & "\" & ConFeb



End If
End Sub

SamT
06-05-2013, 03:46 PM
Workbooks.Open Name and Workbooks(Name).Close use different values for Name.
Assuming Cel.Offset(0,1) holds the serial number( 0001, 00072)
For each Cel in Range("A2:A13")
Workbooks.Open Filename:=PathNameContact & "\" & Cel & Cel.Offset(0,1) & ".xls"
Workdooks(Cel & Cel.Offset(0,1)).Close
Next Cel