Looking up a value in excel

Business Central/NAV Developers Forum
1

Hi all, I have a problem reading from excel or rather looking up a value in a worksheet. The following code works well as long as the value I’m looking for exists in the worksheet else it crashes. Here’s the code xlRange := xlWorksheet.Range(‘c5:c900’); bFound := xlRange.Find(aNumber).Activate; IF bFound THEN tNumber := FORMAT(xlApplication.ActiveCell.Offset(0, -2).Value); ELSE tNumber := ‘’ ; As I said when bFound is true it works otherwhise it crashes. Is there a way to change this code so that it works when aNumber does not exist in the worksheet ?? I have tried every way I can think of so I’ll would be very happy if anyone can give me some help. Jan Karlsson

2

Hi, in Excel a messagebox appears if find is False. With VBA I can avoid the messagebox with


...
On Error Resume Next
  find
On Error Goto 0
...

I don’t know if this possible with automation. I wonder if somebody has solved this problem. Here is a work - around (not so proper, but it should works):


found:= False;
i :=1;
repeat
xlRange:= xlSheet.Range('C'+format(i));
xlRange.Activate;
if xlRange.Value = format(aNumber) then begin
 tNumber := FORMAT(xlRange.Offset(0, -2).Value); 
 found:= True;
end else begin
 if i < 600 then begin
   i:= i + 1
 end else begin 
   found:= True;
   tNumber := '';
 end;
end;

bye Andre

3

According to the Excel VBA docs, the Find method returns a reference to the cell containing the search value, if a match was found. This cell can then be activated. Please note that no Boolean value gets returned from any of the functions! If no match is found, however, the Find method returns the value used for null objects, i.e. Nothing. You are therefore trying to Activate an undefined cell reference, which causes the crash. This crash also happens in VBA, because you are trying to call methods on an uninitialized object variable. Unfortunately, I don’t know how to check for the “Nothing”-value from within C/AL [:(] I suggest using the workaround posted by Andre. Anybody else got any ideas?

4

Hi guys, thank you for your help and time. Yes I’m using a modified version of the work-around, but I’m still very curious about the “excel-way” to use the find method. It would be nice if there was the ability, from within c/al, to check wether an automation method succeeds or not just like when you use the create function. Once again, thankx a lot Jan Karlsson