PDA

View Full Version : Open Excel and do vlookup.



ssingh75
06-28-2012, 05:29 AM
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-12A1 B1 C2 D3

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
06-28-2012, 11:06 AM
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:
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

ssingh75
06-28-2012, 11:18 PM
Hi,

Thanks so much for the reply.

please see the example below.
Master file looks like below
DescriptionJanFebMarABC ADF DSADF FDSF

Jan-2012 file
DescriptionQtyAmtTotalADF 32DSADF 2313FDSF 23233FDSAF 3132GDSADG 212

Feb-2012 file
DescriptionQtyAmtTotalADF 32DSADF 326FDSF 2336FDSAF 212GDSADG 2135

Mar-2012 file
DescriptionQtyAmtTotalADF 562DSADF 326560FDSF 1554FDSAF 1156GDSADG 2135

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