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