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]

Anyone?

 

[xenou]

Try one of the alternatives as suggested here (opening the file to get the information - with screen updating set to false the user will probably not even notice that this is happening):
http://www.erlandsendata.no/english/index.php?d=envbawbreadfromclosedwb

 

[FRin323]

Thank you! I will look at this and see if I can figure something out!

 

[FRin323]

I don't think the issue is with the screen updating. I think it's with the return value of the RemoteVlookup function.

 

[xenou]

Step through your code and find out what you are getting then:
http://krgreenlee.blogspot.com/2006/04/programming-excel-vba-debugging-for.html

But your return value is a string and you should be able to assign pretty much anything to a string. There's something else going on. Still, stepping through your code and seeing what's happening at each line is the best way to find out - particularly examining the values of variables as they are assigned.

Also get rid of the On Error Goto ErrHander
It's better to crash on the line that errors - you'll know exactly when and where the error occurs.

 

[FRin323]

The problem seems to be when the value is returned and assigned to the cell. I get a type mismatch error. I've tried hard coding and assigning a string to the 'ReturnValue' variable in the RemoteVLookUp function but I still get an error of type mismatch. I removed the Error Handler as well.

 

[xenou]

Let's say you removed the error handlers ...then exactly what line does the error occur on? Also update us on whether this is in the original code you posted or new code you are trying now. Offhand, I'm also curious if you haven't tried just a normal VLookup() and whether that works. You don't need a macro to do a vlookup in another workbook (even a closed one). I personally would avoid a normal formula only if the vlookup was slowing down the workbook noticeably - and even then I might choose to speed it up simply by ensuring that the externally referenced workbook is open (which improves calc time).

ξ

 

[FRin323]

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     

ReturnedValue = "TEST..."
'  ExecuteExcel4Macro("VLOOKUP(""" & Code & _    
   """ ,' " & Path & _    
   "[" & WbName & "]" & _    
   ShName & " ' ! " _    
   & SourceRng & "," _    
   & ReturnColNum & ",FALSE)")        

RemoteVlookUp = ReturnedValue 
End Function

This is how the code was edited from the first post. The debugger reaches the 'End Function' line and as it is being returned it crashes. My guess is that the Cells(r,c).Value function can't have a string type assign to it?

 

[xenou]

When you step through the code does Returned Value have a value? Adding a msgbox might tell you (as well as stepping through the code as noted above).

RemoteVlookUp = ReturnedValue 
[COLOR="Red"]MsgBox ReturnedValue[/COLOR]
End Function