0

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.

I tried several solutions that are given in the answers here on Stackoverflow, 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 the selected Excel cells into Visio? Thank you for sharing your insights and experience!

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
DotNET Afficionado
  • 159
  • 8
  • 1
    What are you want to happen when you paste in Visio? Do you want a Excel object in Visio or the contents of the Excel cell to be a text in a shape in Visio? –  Nov 05 '21 at 16:25
  • HappyMoose yes getting the contents of the Excel cell as a string is what I am looking for thank you! – DotNET Afficionado Nov 06 '21 at 17:28

1 Answers1

1

Pasting into Visio will be difficult because Visio looses focus when you make Excel visible. You can, however, easily add a new shape to the active page in Visio and set the text on the shape to be the text you got from the first cell:

Dim newShape As Visio.Shape

Set newShape = Visio.ActivePage.DrawRectangle(1, 1, 2, 0.5)
newShape.Text = FirstValue2
Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
  • The weird thing is it works fine with a copy-paste solution, but with your approach Jeremy, which makes much more sense as the clipboard is a lot slower anyway, I am still getting the exception. – DotNET Afficionado Nov 08 '21 at 08:37
  • @DotNETAfficionado Which line are you getting the exception on? –  Nov 08 '21 at 16:15