question

VBNETDeveloperVSTOmore-5406 avatar image
0 Votes"
VBNETDeveloperVSTOmore-5406 asked VikiJi-MSFT edited

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

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


 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!

office-vsto-com-dev
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @VBNETDeveloperVSTOmore-5406,
Welcome to Q&A forum!
According to your description, I will remove irrelevant tags. Thanks for your understanding.
Hope you can solve your issue as soon as possible.

1 Vote 1 ·

0 Answers