-
Open Excel and do vlookup.
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
-
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]
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules