GoExcel error when there is an empty cell

[Anonymous]

How to prevent the GoExcel error when there is an empty cell?

The code below is an example.

Dim TestItem As String = "Test Item 3"
GoExcel.FindRow("C:\Temp\Test.xlsx", "Sheet1", "Item", "=", TestItem)
 MsgBox(GoExcel.CurrentRowValue("Value"))

Item	Value
Test Item 1	Test Value 1
Test Item 2	Test Value 2
Test Item 3	Test Value 3

[Anonymous]

How to prevent the GoExcel error when there is an empty cell?

The code below is an example.

Dim TestItem As String = "Test Item 3"
GoExcel.FindRow("C:\Temp\Test.xlsx", "Sheet1", "Item", "=", TestItem)
 MsgBox(GoExcel.CurrentRowValue("Value"))

Item	Value
Test Item 1	Test Value 1
Test Item 2	Test Value 2
Test Item 3	Test Value 3

[Ralf_Krieg · Accepted solution]

Hello

Maybe one of the limitations of the integrated Excel engine. Try to use Excel itself instead:

Option Explicit On
AddReference "microsoft.office.interop.excel.dll"
Imports XL = Microsoft.Office.Interop.Excel

Private Sub Main
	Dim TestItem As String = "Test Item 3"
	MsgBox(FindValue("C:\Temp\Test.xlsx", "Sheet1", "Item", TestItem, "Value"),,"iLogic")
End Sub

Private Function FindValue(ByVal ExcelPath As String, ByVal ExcelSheet As String, ByVal SearchColumn As String, ByVal SearchString As String, ByVal ResultColumn As String) As String

    Dim oExcelApp As xl.Application = GetObject("","Excel.Application")
	If oExcelApp Is Nothing Then
		MsgBox("Unable to start Excel")
		Return Nothing
	End If
	
    Dim oWB As xl.Workbook = oExcelApp.Workbooks.Open(ExcelPath)
    Dim oWS As xl.Worksheet
    For Each oWS In oWB.Sheets
        If oWS.Name = ExcelSheet Then Exit For
    Next
    
	If oWS Is Nothing Then
		MsgBox("Sheet " & ExcelSheet & " not found. Abort")
		Return Nothing
	End If
	
	Dim colNo As Long=oExcelApp.WorksheetFunction.Match(SearchColumn, oWS.Rows(1), 0)
    Dim rowNo As Long=oExcelApp.WorksheetFunction.Match(SearchString, oWS.Columns(colNo), 0)
    colNo = oExcelApp.WorksheetFunction.Match(ResultColumn, oWS.Rows(1), 0)
	
    FindValue = oWS.Cells(rowNo, colNo).value
                
    oWB.Close (False)
    oExcelApp.quit
	
End Function

[Ralf_Krieg · Accepted solution]

Hello

Maybe one of the limitations of the integrated Excel engine. Try to use Excel itself instead:

Option Explicit On
AddReference "microsoft.office.interop.excel.dll"
Imports XL = Microsoft.Office.Interop.Excel

Private Sub Main
	Dim TestItem As String = "Test Item 3"
	MsgBox(FindValue("C:\Temp\Test.xlsx", "Sheet1", "Item", TestItem, "Value"),,"iLogic")
End Sub

Private Function FindValue(ByVal ExcelPath As String, ByVal ExcelSheet As String, ByVal SearchColumn As String, ByVal SearchString As String, ByVal ResultColumn As String) As String

    Dim oExcelApp As xl.Application = GetObject("","Excel.Application")
	If oExcelApp Is Nothing Then
		MsgBox("Unable to start Excel")
		Return Nothing
	End If
	
    Dim oWB As xl.Workbook = oExcelApp.Workbooks.Open(ExcelPath)
    Dim oWS As xl.Worksheet
    For Each oWS In oWB.Sheets
        If oWS.Name = ExcelSheet Then Exit For
    Next
    
	If oWS Is Nothing Then
		MsgBox("Sheet " & ExcelSheet & " not found. Abort")
		Return Nothing
	End If
	
	Dim colNo As Long=oExcelApp.WorksheetFunction.Match(SearchColumn, oWS.Rows(1), 0)
    Dim rowNo As Long=oExcelApp.WorksheetFunction.Match(SearchString, oWS.Columns(colNo), 0)
    colNo = oExcelApp.WorksheetFunction.Match(ResultColumn, oWS.Rows(1), 0)
	
    FindValue = oWS.Cells(rowNo, colNo).value
                
    oWB.Close (False)
    oExcelApp.quit
	
End Function