Retrieving Data From Closed Workbook

[FRin323]

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!

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

 

[FRin323]

Yes, it's the school code in a Text format. And the school codes in the other excel workbook is in Text format as well.

I looked it up and a '#NAME?' output usually occurs when excel doesn't recognize the function that is being used. So the '#NA' one is my best bet.

 

[xenou]

Try it both ways for kicks:

=VLOOKUP(VALUE(C47), ...
=VLOOKUP(TEXT(C47,"@"), ...

 

[FRin323]

Same '#NA' output for both ways. I'm so stuck!


Thank you for your patience by the way!

 

[xenou]

What a sample value you are looking up (i.e., a school code) that returns NA?

 

[FRin323]

'001003'

Do I have to enable macros is the other workbook as well? The schoolcodes.xls file?

 

[xenou]

Same '#NA' output for both ways. I'm so stuck!

Well, I can only suggest you provide sample data and the exact formula you used to test this. It should match as a number, and if that doesn't work, it should match as text. If both don't work, the only logical conclusion is that there is no match. But you have to be very careful with text that looks like numbers - they are tricky beasts.

We are (or I thought we were) using only normal formulas in this test so macros don't matter.

 

[FRin323]

SchoolCodes.xls

Application.xls

You can see the 2nd school entry in the SchoolCodes.xls entry is '001003'.

Yes, you're right! Sorry about that! Just typing in the formula into the box. No macros.

"=VLOOKUP(C47,'Macintosh HD:Users:frin323:Desktop:Internship:[SchoolCodes.xls]Baseline'!A1:C7236,3,FALSE)"

 

[FRin323]

Can people see the images? I can see them on the laptop at home but my work computer wouldn't display the images...