Open Excel and do vlookup.

[ssingh75]

Hi All,
I am facing big challenge over here.
Open Excel file one by one from folder say in C or D drive and do vlookup based on criateria.
e.g.,
I've a folder say Input. in that folder i've 3 xlsx file named Jan-2012,Feb-2012,Mar-2012.
I need the code which can open each file and do vlookup from sheet1 using column A to C.

DataJan-12Feb-12Mar-12A1B1C2D3

But there is a condition.
While doing vlookup from Input file. first it will check the field "data" and do the vlookup by refering that column only.


Best Regards,
Susheel

[Kenneth Hobs]

Welcome to the forum! If you would post an example workbook or two or the formula, we can help a bit more.

In the Do loop, uncomment the parts to open and then add what else you need. You probably don't really need vLookup. A Range Find would probably work. In a Module:
[VBA]Sub DirFiles()
Dim FileName As String, FileSpec As String, FileFolder As String
Dim wb As Workbook

FileFolder = ThisWorkbook.Path & "\"
FileSpec = FileFolder & "*.xlsm"

FileName = Dir(FileSpec)
If FileName = "" Then Exit Sub

' Loop until no more matching files are found
Do While FileName <> ""
If IsWorkbookOpen(FileName) = False Then
'Set wb = Workbooks.Open(FileFolder & FileName)
'DoEvents
'wb.Close True
Debug.Print FileName
End If
FileName = Dir()
Loop

End Sub

Function IsWorkbookOpen(stName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next ' In Case it isn't Open
Set Wkb = Workbooks(stName)
If Not Wkb Is Nothing Then IsWorkbookOpen = True
'Boolean Function assumed To be False unless Set To True
End Function[/VBA]

[ssingh75]

Hi,

Thanks so much for the reply.

please see the example below.
Master file looks like below
DescriptionJanFebMarABCADFDSADFFDSF

Jan-2012 file
DescriptionQtyAmtTotalADF32DSADF2313FDSF23233FDSAF3132GDSADG212

Feb-2012 file
DescriptionQtyAmtTotalADF32DSADF326FDSF2336FDSAF212GDSADG2135

Mar-2012 file
DescriptionQtyAmtTotalADF562DSADF326560FDSF1554FDSAF1156GDSADG2135

In the master file the data from Column "Total" should be populate by month wise.

But "Total" column can be anywhere in the whole sheet.

and in next qtr in place of Jan,feb & mar we'll have file named as Apr-2012,May-2012 &Jun-2012 like that.

Hope its clear.

Best Regards,
Susheel