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