How to read an Excel cell value as string from a Visio add-in (VB.NET)

[VisioExcel]

I am trying to read a single cell value from a Vision add-in from an Excel file the user selects with a File Dialog, then selects an Excel cell in Excel.

The user can select a range of more than one cell. however I will read only the first cell.

The below is VBA code from Visio Guy adapted for VB.NET:

Original code: Excel to Visio via VBA

Sub GetExcelCellValue()

    Dim XlApp As Object
    Dim XlWrkbook As Excel.Workbook
    Dim XlSheet As Excel.Worksheet
    Dim rng As Excel.Range
    Dim docPath As String

    docPath = Application.ActiveDocument.Path
    XlApp = CreateObject("Excel.Application")

    ' msoFileDialogFilePicker = 3
    With XlApp.FileDialog(3)
        .Filters.Clear
        .Filters.Add("Excel Files", "*.xls, *.xlsx, *.xlsm")
        .InitialFileName = docPath
        .Show
        XlApp.Workbooks.Open(FileName:= .SelectedItems(1))
    End With

    XlWrkbook = XlApp.Workbooks(1)
    XlSheet = XlWrkbook.Worksheets("Sheet1")
    XlApp.Visible = True

    rng = XlApp.InputBox("Select a single cell", "Obtain Range Object", Type:=8)

    Dim FirstRow As String
    Dim FirstCol As String
    Dim FirstValue2 As String


    FirstRow = rng.Row
    FirstCol = rng.Column

    rng.Copy()

    'FirstValue2 = Convert.ToString(rng.Cells(FirstRow, FirstCol))
    FirstValue2 = Convert.ToString(rng.Worksheet.Cells(FirstRow, FirstCol).Value2)

    'Transfer Excel contents to Visio shapes on active page
    MsgBox("This is the first cell: " & FirstValue2)


    XlApp.Quit


End Sub

I tried several solutions that are given in the answers here on Stackoverflow: How can I extract a string from an excel cell?

,unfortunately those solutions get me these kind of errors:

Exception thrown: 'System.Reflection.TargetInvocationException' in mscorlib.dll Exception thrown: 'System.Runtime.InteropServices.COMException' in mscorlib.dll Exception thrown: 'System.Reflection.TargetInvocationException' in Microsoft.Office.Tools.Common.Implementation.dll

To test I did add a Range.Copy() which clearly shows the right Range is selected and copied, if I do a paste the cell content(s) show up as selected via the code as shown here.

How can I reliably read Excel cell values from a selected Excel cells into Visio? Thank you for sharing your insights and experience!

(The question below has been asked to Visio experts in the Visguy.com forum, but it is clear more Excel VSTO add-in knowledge is required to answer this question, so I am hopeful someone here can help me with this!)

 

[VisioExcel]

Ok I found some VB.NET code here:

How to Read an Excel file in Windows Forms Application using C# and Vb.Net

Here in this post, I am sharing few examples on how to read an Excel file or import data from an Excel file in your Windows Forms application using C# and Vb.Net and in how many ways you can use the data.

www.encodedna.com

and made some changes that are useful for my (test) purposes, and this code works fine for reading an Excel file into a Visio add-in:

    Function ReadFirstExcelRowOnSheet1(ByVal sFile As String) As List(Of String)
        Dim TheList As New List(Of String)
        Dim xlApp = New Excel.Application
        Dim xlWorkBook = xlApp.Workbooks.Open(sFile)            ' WORKBOOK TO OPEN THE EXCEL FILE.
        Dim xlWorkSheet = xlWorkBook.Worksheets("Sheet1")       ' NAME OF THE WORK SHEET.

        Dim iRow As Integer
        For iRow = 2 To xlWorkSheet.Rows.Count
            If Trim(xlWorkSheet.Cells(iRow, 1).value) = "" Then
                Exit For        ' BAIL OUT IF REACHED THE LAST ROW.
            Else
                TheList.Add(Trim(xlWorkSheet.Cells(iRow, 1).value))
            End If
        Next

        xlWorkBook.Close() : xlApp.Quit()

        ' CLEAN UP. (CLOSE INSTANCES OF EXCEL OBJECTS.)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp) : xlApp = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook) : xlWorkBook = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet) : xlWorkSheet = Nothing

        Return TheList

    End Function

Notice the extra COM cleanup in this code, not sure if necessary but left it in (the experts at Add-in Express strongly advice extra cleanup I understand). Will change this further for my purposes but good to know it works well.