How to read an Excel cell value as string from a Visio add-in

[Visisthebest]

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: http://visguy.com/vgforum/index.php?topic=7450.msg31415#msg31415

Code Select Expand

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: https://stackoverflow.com/questions/5646145/how-can-i-extract-a-string-from-an-excel-cell/41127000

,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!

[Surrogate]

How can I reliably read Excel cell values from a selected Excel cells into Visio?

What do you want to do with the values from the range ?

[Visisthebest]

Let users select a value from various statistical tables for various statistical distributions: https://home.ubalt.edu/ntsbarsh/business-stat/StatistialTables.pdf

They only have to do this very rarely, so doing it this way is fine and they can use their own custom statistical table Excel file if they want this way.

[wapperdude]

Not at my computer... cannot test direct approach, but what ought to work...

In my original code, the Excel contents are placed into Visio shape as text (string).  Once pasted, use resultstr to retrieve and assign to a variable.  Delete the shape. 

[Surrogate]

With this code you can itterate all cells in selected range

Code Select Expand

Sub xls_query()
Dim oExcel As Object 'Excel.Application
Set oExcel = CreateObject("Excel.Application")
Dim sp As Object 'Excel.Workbook
Dim sht As Object 'Excel.Sheets
Dim tr As Object
Dim tc As Object
Dim qx As Integer
Dim qy As Integer
pth = Visio.ActiveDocument.Path
Dim ffs As FileDialogFilters
Dim sFileName As String
oExcel.Visible = True
Dim fd As FileDialog
Set fd = oExcel.FileDialog(msoFileDialogOpen)
With fd
.AllowMultiSelect = False
.InitialFileName = pth
Set ffs = .Filters
    With ffs
        .Clear
        .Add "Excel", "*.xls*"
    End With
oExcel.FileDialog(msoFileDialogOpen).Show
End With
sFileName = oExcel.FileDialog(msoFileDialogOpen).SelectedItems(1)
Set sp = oExcel.workbooks.Open(sFileName)
sp.Activate
Dim UserRange As Object ' Excel.Range
Dim Total As Object 'Excel.Range
oExcel.GoTo Reference:=sp.Worksheets(1).Range("A2")
oExcel.ActiveCell.Select
Set UserRange = oExcel.InputBox _
(Prompt:="Please select range", _
Title:="Select range", _
Type:=8)
Set Total = UserRange
For Each tr In Total.Rows
    rc = rc + 1
    sc = 0
    For Each tc In Total.Rows.Columns
        sc = sc + 1
    Next tc
Next tr
ReDim arr(rc, sc) As Variant
For qx = 1 To rc
For qy = 1 To sc
Debug.Print Total.Cells(qx, qy) '
Next qy
Next qx
sp.Close SaveChanges:=False
oExcel.Application.Quit
End Sub

[Visisthebest]

Thank you Wapperdude that is also a possibility using the clipboard, copy from Excel, paste into a Visio shape's text then retrieve the text from the shape.

Thank you Surrogate I will give that code a try, need to change it a bit for VB.NET in particular the code using Variants to copy the Excel Cell contents into a Visio array, as we don't get to use these o-so-convenient-to-use Variants anymore in VB.NET.

(actually I use Option Strict as much as possible, which requires more effort programming but does make debugging simpler:).

[Visisthebest]

Ok I found some VB.NET code here:

https://www.encodedna.com/windows-forms/read-an-excel-file-in-windows-forms-application-using-csharp-vbdotnet.htm

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:

Code Select Expand

    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.