Solved: How can I create linked Vlookup data from multiple closed workbooks

[marshybid]

Hi All,

Recently MD and xld helped me to gather Vlookup data from an unopened file by linking the data to my active workbook (see code below)

Private Sub AddVlookUp() 
    Const FILEPATH As String = _ 
    "C:\Documents and Settings\francri\My Documents\Motorola\" & _ 
    "Billibg Reports\Timesheet Consolidation 19 May 2008\" 
    Dim i As Long 
    Dim k As Long 
    Dim LastRow As Long 
 
    With Sheets("Vlookup") 
        For i = 1 To 3 
 
            For k = 1 To 125 
 
                .Cells(k, i).FormulaR1C1 = "='" & FILEPATH & _ 
                "[Confirmation Required Final.xls]Vlookup Data'!R" & k & "C" & i 
            Next 
        Next 
    End With

I now have a slightly different query;

For another project, I would like to gather Vlookup data from multiple unopened files, so;

1) I will create a sheet called Vlookup in my active workbook (the one I have open and where the macro will be run)
2) I will create a FILEPATH as in the code above
3) Then I need to say for each workbook (*.xls) in the directory (FILEPATH) get data from Approved Timesheets (this will always be the sheet name containing the data I need) I will need the data to include only 3 columns, Column 2, 49, 50 (but all rows down to last row of data)
4) Link this data to my activeworskeet (Vlookup) then continue to next workbook in FILEPATH capture sames range of data and link to next blank row in Vlookup
5) I then need the vlookup formula to interrogate 2 pieces of data, If data from Column 3 is found then find out if data in column 49 is found, if yes to both then return value of column 50

I hope this makes sense.

Can anyone help.

Thanks,

Marshybid

[Bob Phillips]

[vba]

Sub LoopFiles()
Const FILEPATH As String = _
"C:\Documents and Settings\francri\My Documents\Motorola\" & _
"Billibg Reports\Timesheet Consolidation 19 May 2008\"

Dim FSO
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim wb As Workbook

Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder(sPath)

For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then
Set wb = Workbooks.Open(Filename:=file.Path)

'do your stuff

wb.Close savechanges:=False
End If
Next file

Set oFSO = Nothing

End Sub
[/vba]

[marshybid]

[vba]

Sub LoopFiles()
Const FILEPATH As String = _
"C:\Documents and Settings\francri\My Documents\Motorola\" & _
"Billibg Reports\Timesheet Consolidation 19 May 2008\"

Dim FSO
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim wb As Workbook

Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder(sPath)

For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then
Set wb = Workbooks.Open(Filename:=file.Path)

'do your stuff

wb.Close savechanges:=False
End If
Next file

Set oFSO = Nothing

End Sub
[/vba]

Hi Bob,

I'm afraid you have lost me a little here

In the area 'do your stuff

I don't understand how I will only extract the 3 columns of data that I am looking for, then write the vlookup formula to interrogate the data??

Sorry, it's Monday and I'm feeling a little dense

Marshybid

[Bob Phillips]

Marshy,

I had assumed that you had copde to extract from one particular workbook, and you need code to loop through a complete folder to process all Excel workbooks. That is what I provided, the other bit has to be integrated.

[marshybid]

Marshy,

I had assumed that you had copde to extract from one particular workbook, and you need code to loop through a complete folder to process all Excel workbooks. That is what I provided, the other bit has to be integrated.

Hi xld,

You are right, I have code that extracts from one workbook currently (provided by MD) but this specifies

 
Dim i as Long
Dim k as Long
 
For i = 1 To 2
For k = 2 To 126

I can't work out how to say that I want all data in the 3 columns 2, 49, 50 down to last row of data?? Sorry, I should have been more specific. Also, once I have the data it is copied into my active worksheet (Vlookup) How do I get it to post the data continuosly down this sheet for each file??

Marshybid

[Bob Phillips]

You calculate the LastRow of the incoming data, millions of examples of such code everywhere, and copy from row n to that LastRow for m columns, using another variable say NextRow to point to the next free row on your output worksheet, updating NextRow by the number of rows added per workbook.

[marshybid]

You calculate the LastRow of the incoming data, millions of examples of such code everywhere, and copy from row n to that LastRow for m columns, using another variable say NextRow to point to the next free row on your output worksheet, updating NextRow by the number of rows added per workbook.

Hi xld, I'll put my mind to it and come up with what I can

Thanks for the input.

I'll mark this thread as solved

Marshybid

[HeatherA]

Hi All,

Recently MD and xld helped me to gather Vlookup data from an unopened file by linking the data to my active workbook (see code below)

Private Sub AddVlookUp() 
    Const FILEPATH As String = _ 
    "C:\Documents and Settings\francri\My Documents\Motorola\" & _ 
    "Billibg Reports\Timesheet Consolidation 19 May 2008\" 
    Dim i As Long 
    Dim k As Long 
    Dim LastRow As Long 
 
    With Sheets("Vlookup") 
        For i = 1 To 3 
 
            For k = 1 To 125 
 
                .Cells(k, i).FormulaR1C1 = "='" & FILEPATH & _ 
                "[Confirmation Required Final.xls]Vlookup Data'!R" & k & "C" & i 
            Next 
        Next 
    End With

I now have a slightly different query;

For another project, I would like to gather Vlookup data from multiple unopened files, so;

1) I will create a sheet called Vlookup in my active workbook (the one I have open and where the macro will be run)
2) I will create a FILEPATH as in the code above
3) Then I need to say for each workbook (*.xls) in the directory (FILEPATH) get data from Approved Timesheets (this will always be the sheet name containing the data I need) I will need the data to include only 3 columns, Column 2, 49, 50 (but all rows down to last row of data)
4) Link this data to my activeworskeet (Vlookup) then continue to next workbook in FILEPATH capture sames range of data and link to next blank row in Vlookup
5) I then need the vlookup formula to interrogate 2 pieces of data, If data from Column 3 is found then find out if data in column 49 is found, if yes to both then return value of column 50

I hope this makes sense.

Can anyone help.

Thanks,

Marshybid

[Bob Phillips]

Hw, what doesn't work? A bit more detail would help.

[HeatherA]

The VLOOKUP on the data in the closed workbook doesn't work. My open wookbook that references a closed workbook isn't pulling data from the closed workbook.

Thanks for your help!

[Aussiebear]

Heather, have you ensured the correctness of the pathname to the correct file, sheet name of the active workbook, and the name of the active workbook?