X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Vlookup To Closed Workbook    
post Jul 5 2018, 01:29 PM

Posts: 5,231
Joined: 2-November 04
From: Downey, CA

Informal poll: Am I the only one who just learned it's easy as pie to have a VLOOKUP to a closed workbook ? Wow - this is awesome.... I've been automating the process of completing PO requests - my macro puts the Zip Code in Cell I2 - there's a number that's needed, the CPR number, and I want the result of the VLOOKUP in cell F12 (the CPR#, which is now based on zip code)

1. In File Explorer, go to C:\TEMP
2. Create folder named “CPR_Lookup”
3. Save Zip code lookup (to CPR#) in newly-created folder

Code with Formula is as follows – no need to have file open

ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-10]C[3], 'C:\TEMP\CPR_Lookup\[CPR_Lookup_Central.xlsm]ZipCodes'!R2C1:R200C7,6,FALSE)"

What the macro does:
1. Goes to cell F12, where you want the CPR# (result of VLOOKUP)
2. Arguments in VLOOKUP formula:
a. Go up 10 rows, right 3 columns (to where the zip code is)
b. Range to lookup is filepath, name, worksheet name and absolute range
c. Sixth column over – column with CPR#
d. FALSE – exact match

Who knew ?

Go to the top of the page
post Jul 5 2018, 05:31 PM

Posts: 3,915
Joined: 19-October 10

Yes, you can even use evaluate to get the result of external formula's without actually writing any formula's to a cell. If you only need a couple of values from a workbook then using Evaluate and lookups is much quicker.
That being said I'd always propose using Match and Index over VLookup.
Go to the top of the page
post Jul 6 2018, 03:50 AM

Posts: 3,915
Joined: 19-October 10

Here is some more stuff for you.

Getting any single value from a closed workbook without opening it in VBA.
Function fnReturnValue(strPath As String, strFile As String, strSheet As String, strRng As String) As String
   '   Purpose: Returns the value from a closed workbook.
   '    Author: Internet Source, Updated by Jon Smith
   '      Date: 10/3/16
   'Parameters:  strPath - The path to the Excel File
   '             strFile - The filename of the Excel File
   '            strSheet - The sheetname we want to get a value from
   '              strRng - The range we want to get the value of.
   '   Returns: string - The value of the cell.
   'Example of useage
   'strPath = "C:\Users\jonasmith\Desktop\"
   'strFile = "Test.xlsx"
   'strSheet = "Sheet1"
   'strrng = Range("A1").Address(1, 1, xlR1C1)
   Dim strRef As String
   If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
   'Build a reference based on the parameters above
   strRef = "'" & strPath & "[" & strFile & "]" & strSheet & "'!" & strRng

   'If the reference returns an error value or a blank value exit the function so it also returns blank.
   If ExecuteExcel4Macro("IsError(" & strRef & ")") = True Then Exit Function
   If ExecuteExcel4Macro("IsBlank(" & strRef & ")") = True Then Exit Function
   'Set the function to the returned value.
   fnReturnValue = ExecuteExcel4Macro(strRef)
End Function

Here is an example of a method to find the matching row in a table of data using Match (which returns a row number). (I cant remember off the top off my head why I needed the +1).
iRow = shtClientList.Evaluate("=MATCH(""56"",tblClientList[IDClient],0)") + 1

Once you have the row you have a few options. If you were to select that cell you can then reference any other column on that row using the following.
        strClientName = shtClientList.Evaluate("=tblClientList[@ClientName]") 'Client Name
        strOptionGroup = shtClientList.Evaluate("=tblClientList[@Option]")  'Option
        strLanguage = shtClientList.Evaluate("=tblClientList[@Language]")  'Language
        boolSAF = shtClientList.Evaluate("=tblClientList[@SAFClient]")  'SAF Option

Alternatively with the row number you can do something like this without selecting the cell after you use Match
strClientName = shtClientList.Evaluate("=INDEX(tblClientList[ClientName]," & iRow & ")")

You can also combine them into one line.
strClientName = shtClientList.Evaluate("=INDEX(tblClientList[ClientName],MATCH(""56"",tblClientList[IDClient],0))")

Now as to why this is waaaaay better than VLookup.
Column order doesn't matter, you don't need to mess around putting the columns you are looking up to the far left of your data.
The lookup column is much easier to read and alot more stable, in the VLookup posted early its looking up the column 6 columns right of the one used for looking up a value. I have no clue what column that is though. I need to find the lookup and count the columns myself and work it out.
Furthermore if there are any structural changes to the table the formula can and will break and return the wrong values.

In my Index Match example, you can clearly see we are looking up a client ID and returning the value from the ClientName column etc etc. The column number doesn't matter at all as we use structured references so any changed to the table structure and column position are irrelevant, the code will still work correctly.

So yeah, abandon VLookup, get into Index and Match and hope you found my examples useful.

Forgot to mention, all of my examples are using a listobject table in Excel (go to Insert>Table and it turns your data range into a table). That automatically allows all the structured references I have used. I have not written a single Named Range or anything like that. Its all vanilla Excel functionality when you use tables, which when working with a table of data I also think you should.
This post has been edited by JonSmith: Jul 6 2018, 04:03 AM
Go to the top of the page
post Jul 6 2018, 03:57 AM

Posts: 386
Joined: 20-September 12
From: Cornwall UK

Well I sure found it interesting Jon. thumbup.gif
Go to the top of the page
post Jul 11 2018, 10:03 AM

Posts: 5,231
Joined: 2-November 04
From: Downey, CA

ahh... people are passionate about the INDEX MATCH thing, haha - hey, guess what? I "lost" this macro in my PERSONAL.xlxm - so I came back here to recover it ! Whoo hoo !!! cool.gif
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    20th October 2018 - 09:51 AM