Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.

GoExcel error when there is an empty cell

Anonymous

GoExcel error when there is an empty cell

Anonymous
Not applicable

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"))

 

ItemValue
Test Item 1Test Value 1
Test Item 2Test Value 2
  
Test Item 3Test Value 3
0 Likes
Reply
Accepted solutions (1)
365 Views
1 Reply
Reply (1)

Ralf_Krieg
Advisor
Advisor
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

 


R. Krieg
RKW Solutions
www.rkw-solutions.com