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

 

[xenou]

It can be tricky and frustrating dealing with "text" codes that have only numeric digits in them (it's always a danger spot).

Can you confirm you have text and not numbers in both files? You can do this by summing the column using the Sum() formula. If it is text, the sum of the column will be zero. Try it in both files.

Note: the cell you put the sum() formula in should not be formatted as text or it won't work either ...

 

[FRin323]

I need to make sure that the look up values are text? Or the return value and the cells in which they are placed is text?

I changed the look up and returned values on both workbooks from general to text type and still no luck. Also, i had a very embarrassing mistake and did not realize that the SchoolCodes workbook is in a spreadsheet named 'Baseline' but I did make the proper adjustments to the code. Now, I only get the '#NA' error wither I run the macro with the SchoolCode workbook opened or closed. I ran thru the code with the debugger and it seems like it can't find any of the school codes in the SchoolCodes workbook. So confusing?

 

[xenou]

Well, another clue might be to open both workbooks and get it working with a normal formula first - can you get that to work?

 

[FRin323]

As in putting the SchoolCodes spread sheet in the same workbook and try it that way?

 

[xenou]

Well, more as in just using a normal formula, not vba code:

=VLOOKUP(A1,OtherBook.xls!A1:B100,2,False)

Will it work this way?

 

[FRin323]

It says '#NAME?'.

How would the VLookUp function look if it was a spreadsheet in this directory: Macintosh HD:Users:frin323:Desktop:Internship:SchoolCode.xls
The range is A2:F6000 in the 'Baseline' sheet and the return column is 3?

 

[xenou]

It says '#NAME?'.

What is the exact formula you used?
I don't have a mac so I don't know if it would look different or not. Do look in your Excel help file under VLOOKUP for an example.

 

[FRin323]

=VLOOKUP(C47,'Macintosh HD:Users:frin323:Desktop:Internship:[SchoolCodes.xls]Baseline'!$A$1:$C$600,3,FALSE)

is what I'm using but it's giving me the '#NA' now. I was getting the '#NAME?' because the directory wasn't correct so I fixed it now.

If it means anything I'm using Excel 2011 with the .xlsx extension.

 

[FRin323]

Update: This code gives me the '#NAME?' output:
=VLOOKUP(C47,'Macintosh HD:Users:frin323:Desktop:Internship:[SchoolCodes.xls]Baseline'!BA:C7236,3,FALSE)

 

[xenou]

Okay, so what's in C47 (the code you are looking up)? And use the version that gives you the #N/A error - that's the better try.