Hi everyone!
I need to do two searches on an Excel spreadsheet from a different VB6 application. I first check if a barcode has already been entered. If not I then look for the next blank row so I can enter the data there. Having established an object relating to the Excel app and the particular worksheet, I then set a range object for the particular column I'm interested in. So far so good.
The first search looks for the occurrence of a 4-digit number in that column and the function simply returns false if it is found. If not found, an additional search on the same column for the first null string gives me the next row to use for subsequent data.
Here is the function:
Function TestBarcode() As Boolean
'declare a couple of Excel objects
Dim SearchRange, FoundCell As Excel.Range
Set FoundCell = Nothing
'xlApp already set up and the worksheet opened
Set SearchRange = xlApp.ActiveSheet.Columns(4) ' column D
'Barcode and CurrentRow are module-level variables
Set FoundCell = SearchRange.Find(Barcode, lookat:=xlwhole) 'i.e a match
If FoundCell Is Nothing Then 'not present already
CurrentRow = SearchRange.Find("", lookat:=xlwhole).Row
TestBarcode = True
Else 'duplicate - no good
CurrentRow = 0
TestBarcode = False
End If
End Function
This works fine most of the time but every so often I get an Excel error message saying the Find method has failed. It appears to be unreliable. Am I asking too much of Excel?
I need to do two searches on an Excel spreadsheet from a different VB6 application. I first check if a barcode has already been entered. If not I then look for the next blank row so I can enter the data there. Having established an object relating to the Excel app and the particular worksheet, I then set a range object for the particular column I'm interested in. So far so good.
The first search looks for the occurrence of a 4-digit number in that column and the function simply returns false if it is found. If not found, an additional search on the same column for the first null string gives me the next row to use for subsequent data.
Here is the function:
Function TestBarcode() As Boolean
'declare a couple of Excel objects
Dim SearchRange, FoundCell As Excel.Range
Set FoundCell = Nothing
'xlApp already set up and the worksheet opened
Set SearchRange = xlApp.ActiveSheet.Columns(4) ' column D
'Barcode and CurrentRow are module-level variables
Set FoundCell = SearchRange.Find(Barcode, lookat:=xlwhole) 'i.e a match
If FoundCell Is Nothing Then 'not present already
CurrentRow = SearchRange.Find("", lookat:=xlwhole).Row
TestBarcode = True
Else 'duplicate - no good
CurrentRow = 0
TestBarcode = False
End If
End Function
This works fine most of the time but every so often I get an Excel error message saying the Find method has failed. It appears to be unreliable. Am I asking too much of Excel?
