PDA

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



marshybid
06-16-2008, 04:49 AM
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:bug:

Bob Phillips
06-16-2008, 04:58 AM
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

marshybid
06-16-2008, 05:28 AM
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


Hi Bob,

I'm afraid you have lost me a little here :banghead:

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 :dunno

Marshybid

Bob Phillips
06-16-2008, 05:35 AM
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
06-16-2008, 05:39 AM
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
06-16-2008, 06:17 AM
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
06-16-2008, 07:18 AM
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 :rotlaugh:

Thanks for the input.

I'll mark this thread as solved

Marshybid

HeatherA
07-16-2008, 12:59 PM
:help
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:bug:

Bob Phillips
07-16-2008, 02:57 PM
Hw, what doesn't work? A bit more detail would help.

HeatherA
07-17-2008, 10:17 AM
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
07-18-2008, 10:36 PM
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?