Retrieving Data From Closed Workbook

F

FRin323

New Member
Joined
May 23, 2011
Messages
21
Hey guys!

Really hope I can get some help with this one! I've been up and down and all over for the last week and a half and no luck yet.

Anyway, basically I have an form I made using Excel 2011 for Mac. At the end of this form there is a section for School Codes that I enter (@ Cell 'C47'). Once all school codes are entered, a macro goes thru each School Code until a blank is reached. It gets each School Code and looks for it in another Excel Workbook (which is closed) one at at time, and when it finds the matching school code in the closed workbook, it grabs the name that that School Code was matched to and sets in the next cell over in the form.

The error seems to be when I assign the value to the 'ReturnValue' in the RemoteVlookUp function. Nothing is being stored and the error handler is being executed. I get a Run Time Error '13' - Type Mismatch. Any idea what is going wrong?

I appreciate any help you guys can offer. Thanks in advance!


Code: 
Sub Button()

'School ID Location on form workbook
   Const SchoolCodeColumn As String = "C"
'Path to School Code workbook
  Const PathToClosedRefWorkBk As String = "Macintosh HD:Users:frin323:Desktop:Internship:"    
'School Code woorkbook file name
   Const ClosedRefWorkBkName As String = "SchoolCodes.xlsx" 
'School Code Sheet within location
   Const ClosedRefSheetName As String = "Sheet1" 
 'Cells in School Code being referenced
   Const ClosedRefRange As String = "B2:C7236"
'School Name to be stored in Cell
   Const ClosedRefReturnColumn As Integer = 2 
   
   For currCell = 47 To Cells(Rows.Count, SchoolCodeColumn).End(xlUp)
      SchoolCode = Cells(currCell, SchoolCodeColumn).Value
            
      
     Cells(currCell, SchoolCodeColumn + 1).Value = _
     RemoteVlookUp(SchoolCode, PathToClosedRefWorkBk, _
 ClosedRefWorkBkName, ClosedRefSheetName, ClosedRefRange, _
 ClosedRefReturnColumn)
   
   Next currCell

End Sub

Private Function RemoteVlookUp(Code, Path, WbName, ShName, SourceRng, ReturnColNum) As String
Dim ReturnedValue As String

On Error GoTo ErrHandler

   ReturnedValue = ExecuteExcel4Macro("VLOOKUP(""" & Code & _
   """ ,' " & Path & _
   "[" & WbName & "]" & _
   ShName & " ' ! " _
   & SourceRng & "," _
   & ReturnColNum & ",FALSE)")
   
   RemoteVlookUp = ReturnedValue
   Exit Function
ErrHandler:
   RemoteVlookUp = VlookupNA
End Function
 
Sort by date Sort by votes

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Even when I try and hard code a string assignment in the main function to the Cells(C,R).Value line, I still get a mismatch type error. Not sure what's going wrong?
 
Upvote 0
Here's revised code that *should* work. Sorry - I should have looked closer (mainly I'm unfamiliar with excel4macros and thought it was some peculiarity of these, but it's really just ordinary vba syntax type stuff):

Code: 
[COLOR="Navy"]Sub[/COLOR] Button()

[COLOR="Navy"]Dim[/COLOR] currCell [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] SchoolCode [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

[COLOR="Navy"]Const[/COLOR] SchoolCodeColumn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR] = 3 [COLOR="SeaGreen"]'School ID Location on form workbook ( 3 --> Column C )[/COLOR]
[COLOR="Navy"]Const[/COLOR] PathToClosedRefWorkBk [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR] = "Macintosh HD:Users:frin323:Desktop:Internship:"
[COLOR="Navy"]Const[/COLOR] ClosedRefWorkBkName [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR] = "SchoolCodes.xlsx"
[COLOR="Navy"]Const[/COLOR] ClosedRefSheetName [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR] = "Sheet1"
[COLOR="Navy"]Const[/COLOR] ClosedRefRangeR1C1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR] = "R2C2:R7236C3"
[COLOR="Navy"]Const[/COLOR] ClosedRefReturnColumn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR] = 2

    [COLOR="Navy"]With[/COLOR] ThisWorkbook.ActiveSheet

        [COLOR="Navy"]For[/COLOR] currCell = 47 [COLOR="Navy"]To[/COLOR] .Cells(Rows.Count, SchoolCodeColumn).End(xlUp).Row
            SchoolCode = .Cells(currCell, SchoolCodeColumn).Value
                .Cells(currCell, SchoolCodeColumn + 1).Value = _
                        RemoteVlookUp(SchoolCode, PathToClosedRefWorkBk, _
                        ClosedRefWorkBkName, ClosedRefSheetName, ClosedRefRangeR1C1, _
                        ClosedRefReturnColumn)
        [COLOR="Navy"]Next[/COLOR] currCell

    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Function[/COLOR] RemoteVlookUp(Code, Path, WbName, ShName, SourceRng, ReturnColNum) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ReturnedValue [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
[COLOR="Navy"]Dim[/COLOR] myFormula [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

[COLOR="SeaGreen"]'Note - This formula construction assumes[/COLOR]
[COLOR="SeaGreen"]'       that the lookup value is TEXT or[/COLOR]
[COLOR="SeaGreen"]'       numbers stored as text.[/COLOR]

    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] ErrHandler
    
    myFormula = "VLOOKUP(" & Chr(34) & Code & Chr(34) & _
        ",'" & Path & _
        "[" & WbName & "]" & _
        ShName & "'!" _
        & SourceRng & "," _
        & ReturnColNum & ",FALSE)"
    [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] myFormula
    ReturnedValue = ExecuteExcel4Macro(myFormula)

    RemoteVlookUp = ReturnedValue

[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Function[/COLOR]

ErrHandler:
   RemoteVlookUp = "#ERROR#"

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

Mainly, corrections are:
  • Using a numeric long data type for the column variable (3), rather than a letter (C), so we can add to it (+ 1)
  • Using a variant data type for the function so it can handle error values being returned
  • Using R1C1 style notation for range addresses passed to the remote vlookup function

I think that should work - I also cleaned up the formula concatenation to remove unnecessary spaces that were present in the resulting formula. I also used a "custom" error return value for the RemoteVlookup error handler so that you can distinguish global errors from "normal" errors actually returned by the inner vlookup function itself.
 
Last edited:
Upvote 0
It didn't crash! :)

But for some reason when I have the SchoolCodes.xlsx spreadsheet open it goes into the costume error handler and the RemoteVlookUp function returns '#ERROR#'. When the 'ReturnValue = ExecuteExcel4Macro()' line is executed the 'ReturnValue' stays empty.

However, when the spreadsheet is closed the function returns '#NA'. The '#NA' is returned in the 'ReturnValue = ExexuteExcel4Macro()' line.

Thank you btw for your help! Really appreciate it!
 
Upvote 0
Interesting. I don't have any difference whether the external reference is closed or open (both work). #N/A values suggest that the lookup fails to find a match. #ERROR# values mean more trouble with RemoteVlookup function (oh well). I'm afraid I can't speak for Excel on Mac, though - not my world.

What is some sample data on your end:

1. Values (school codes) being looked up
2. Values in the lookup table in SchoolCodes.xlsx
 
Upvote 0
I did some testing and got the same result as FRin323, works fine if the text is found, if the text is not found, it returns the error value #N/A rather than the string #ERROR#

I figure that that is because there is no VB error, ExecuteExcel4Macro returns a value (of data type Error) to the variant ReturnedValue which becomes the value of the (variant) RemoteLookup, which is put in the cell.

I don't know what would be triggering the VB error handler in a non-Mac situation.
 
Upvote 0
<img src="http://a8.sphotos.ak.fbcdn.net/hphotos-ak-snc6/252167_527680220356_290500940_746120_2642946_n.jpg"/>

<img src="http://a2.sphotos.ak.fbcdn.net/hphotos-ak-snc6/248983_527680235326_290500940_746121_982197_n.jpg"/>

The school codes in the first image are valid codes in the schoolcodes spreadsheet.

Really mikerickson? I couldn't get it to find the the text even? Are you using Windows?
 
Upvote 0
Try using this version of the function RemoteVLookup, which adapts to numeric or text values.


Code: 
Private Function RemoteVlookUp(Code, Path, WbName, ShName, SourceRng, ReturnColNum) As Variant
Dim ReturnedValue As Variant
Dim myFormula As String

'Note - This formula construction assumes
'       that the lookup value is TEXT or
'       numbers stored as text.

    On Error GoTo ErrHandler

    If Not IsNumeric(Code) Then Code = Chr(34) & Code & Chr(34)
    
    myFormula = "VLOOKUP(" & Code & _
        ",'" & Path & _
        "[" & WbName & "]" & _
        ShName & "'!" _
        & SourceRng & "," _
        & ReturnColNum & ",FALSE)"
    Debug.Print myFormula
    ReturnedValue = ExecuteExcel4Macro(myFormula)

    RemoteVlookUp = ReturnedValue

Exit Function

ErrHandler:
   RemoteVlookUp = "#ERROR#"

End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

KlausW
  • Solved
copy data from several closed workbooks to one workbook.
Replies
5
Views
1K
KlausW
KlausW
K
  • Question Question
Copying Data to Workbook sitting at Sharepoint
Replies
1
Views
405
kshitij_dch
K
C
  • Question Question
Error help - Get data from a closed workbook (without opening)
Replies
7
Views
2K
GWteB
GWteB
N
  • Question Question
VBA code to copy data from multiple workbooks to a sheet in a master wb.
2
Replies
12
Views
2K
Marc L
Marc L
B
  • Question Question
While Consolidating the Data from different workbooks, Required Folder Selection as well
Replies
5
Views
439
Fluff
Fluff

Forum statistics

Threads
1,224,590
Messages
6,179,750
Members
452,940
Latest member
rootytrip

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top