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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.