load vlookup results from another (closed) workbook into an open workbook using vba

[twinwings]

Hello,

So I am pretty sure this issue has come up before, but I can't seem to solve it using any of the past answers regarding similar situations.

What I would *like* Excel to do is.

vlookup data in cell A3, from a currently open workbook ---- and match it against data in a closed workbook in the following path, and return me the results as values.


The path for the workbook that has the data is this:
S:\a\b\c\d\e\f\g\warrantyL12.xlsx

Then, I would like excel to paste the result on my currently open workbook as values. So vlookup cell a3, from my current open workbook against data in warrantyL12.

Here's my code so far

Sub lookuptest()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Range1, myValue
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("S:\a\b\c\d\e\f\g\warrantyL12.xlsx")


Range1 = wb2.Sheets("data").Range("A:K")
ActiveCell = Application.WorksheetFunction.VLookup(A3, Range1, 11, False)


End Sub

On this code, I end up opening the file and the macro gets stuck at "ActiveCell".
I guess I never specified where I would like the results to be pasted, although as far as I can understand, ActiveCell is the cell I have last selected (and the cell I have last selected is where I would like my data to be pasted).


Eventually, I will end up having a worksheet that has about 100 unique cells in column A, and columns B to Z have data that are the results of vlookups of cells from Column A, but obviously each column has a different kind data.

Currently, all I do is select B3 to Z3, and double click on the corner upon which the formulas are effectively pasted down to the last row.

But sometimes I have several thousand rows, and it takes a long time for all the columns with all the vlookups to fill through. Furthermore, I just need the data as values so vlookups also take up space just by being active, untilI paste over as values.

In the past, I have seen macro enabled spreadsheets that with a click of a button, a bunch of data is filled out. The data is filled out in the same logic as a vlookup would fill it out based on data from the leftest column, but they are all pasted as values.


I understand this post is pretty convoluted, but I hope you guys get the idea.

Thank you for your help,
tw

 

[JoeMo]

Because you "end up opening the file", I would guess that your active workbook is the wb2 file, so the Activecell is not what you expect. Here's something you can try. This will open your "wb2" but you will not see it (it remains hidden until closed) and it will not become the active workbook when it is opened.

Sub lookuptest()
Dim wb1 As Workbook
Dim sFilename As String
Dim Range1, myValue
Set wb1 = ThisWorkbook
sFilename = "S:\a\b\c\d\e\f\g\warrantyL12.xlsx"

With GetObject(sFilename)
    Range1 = .Sheets("data").Range("A:K")
    ActiveCell = Application.WorksheetFunction.VLookup(A3, Range1, 11, False)
    .Close
End With
End Sub

 

[twinwings]

Hi Joe,

Thanks for your help.

Unfortunately, the code gets stuck at ActiveCell = Application.WorksheetFunction.VLookup(A3, Range1, 11, False)

I tried selecting the cell where I wanted the result to be pasted but nothing. I know that for cell A3, the code should return #N/A. Do you think this could break it? Other cells in column A, may or may not have a valid number.

Because you "end up opening the file", I would guess that your active workbook is the wb2 file, so the Activecell is not what you expect. Here's something you can try. This will open your "wb2" but you will not see it (it remains hidden until closed) and it will not become the active workbook when it is opened.

Sub lookuptest()
Dim wb1 As Workbook
Dim sFilename As String
Dim Range1, myValue
Set wb1 = ThisWorkbook
sFilename = "S:\a\b\c\d\e\f\g\warrantyL12.xlsx"

With GetObject(sFilename)
    Range1 = .Sheets("data").Range("A:K")
    ActiveCell = Application.WorksheetFunction.VLookup(A3, Range1, 11, False)
    .Close
End With
End Sub

 

[JoeMo]

Hi Joe,

Thanks for your help.

Unfortunately, the code gets stuck at ActiveCell = Application.WorksheetFunction.VLookup(A3, Range1, 11, False)

I tried selecting the cell where I wanted the result to be pasted but nothing. I know that for cell A3, the code should return #N/A. Do you think this could break it? Other cells in column A, may or may not have a valid number.

What do you mean by "the code gets stuck"? Are you getting an error message? If so what is the message?

EDIT: Looking more closely at your code, this line

Application.WorksheetFunction.VLookup(A3, Range1, 11, False)

should be cahnged to this:

Application.WorksheetFunction.VLookup(Range("A3").Value, Range1, 11, False)

 

[twinwings]

What do you mean by "the code gets stuck"? Are you getting an error message? If so what is the message?

Sorry ...it says "Type Mismatch"

 

[JoeMo]

Sorry ...it says "Type Mismatch"

You replied to my last post while I was editing it.
Try this:

Sub lookuptest()
Dim wb1 As Workbook
Dim sFilename As String
Dim Range1, myValue
Set wb1 = ThisWorkbook
sFilename = "S:\a\b\c\d\e\f\g\warrantyL12.xlsx"

With GetObject(sFilename)
    Set Range1 = .Sheets("data").Range("A:K")
    ActiveCell = Application.WorksheetFunction.VLookup(Range("A3").Value, Range1, 11, False)
    .Close
End With
End Sub

 

[mahmed1]

Hi JoeMo

Based on the code above, are you saying that by using getobject(passing filepath and filename)
this will reference the workbook and there i can manipulate it without opening it?

i didnt know that. I though that was only used if an application is already open

 

[JoeMo]

Hi JoeMo

Based on the code above, are you saying that by using getobject(passing filepath and filename)
this will reference the workbook and there i can manipulate it without opening it?

i didnt know that. I though that was only used if an application is already open

GetObject opens the file if it is not open, but keeps it hidden (until the code closes it) so the user never sees it. Should the file already be open, it remains open and GetObject does not cause an error.

 

[mahmed1]

Thank you

iv only ever seen it being used with other applications like word/powerpoint where if those apps are already open then we use getobject

i have known it to be used like this

thank you for the info

 

[twinwings]

GetObject opens the file if it is not open, but keeps it hidden (until the code closes it) so the user never sees it. Should the file already be open, it remains open and GetObject does not cause an error.

Hi Joe,

Haven't had a chance to look at this until now.

Maybe I can explain again...


In my case

Workbook A, column A has fruits (think apples, oranges, mango).
Workbook B has the whole universe of foods including fruits (so meat, vegetables, fruits, etc).
Now column B in workbook B has the market price for each food.
However, I am only concerned for the foods (or fruits in this case) in column A workbook A.

I can easily do a vlookup where it returns me the price for each food in column A, workbook A, based on workbook B. However, if I have too many foods that I need to pull data for..Excel gets slow.

So is there a way, where I essentially accomplish the same result as I do now with vlookups, but without ever opening workbook B , and pasting the corresponding values in workbook A, as paste special values.



So far, iv managed to macro vlookup/paste-values until the last row --- but this is too ugly….



Any ideas?