Recent Posts

Pages: 1 2 [3] 4 5 ... 10
21
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
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!
22
General Visio / Re: Reference page by ID instead of name
« Last post by wapperdude on October 22, 2021, 12:34:38 AM »
I didn't have any particular link in mind.  I just remembered seeing several approaches.  Here are some additional...

http://visguy.com/vgforum/index.php?topic=8298.msg35873#msg35873
http://visguy.com/vgforum/index.php?topic=6250.0
http://visguy.com/vgforum/index.php?topic=8387.msg36295#msg36295
http://visguy.com/vgforum/index.php?topic=7393.msg31126#msg31126

@Surrogate:  shapesheet solution!?!   :o. I can believe it's hard to explain.  That required a lot of creative/resourceful thinking!!!
23
General Visio / Re: Reference page by ID instead of name
« Last post by Surrogate on October 21, 2021, 07:16:34 PM »
Search the forum for Table of Contents.  There are a variety of schemes that have been developed.  One might be adaptable for your task... saving you much work.
Mr. Dude you mean this TOC ?
http://visguy.com/vgforum/index.php?topic=8730

I usually have advice like this
1. don't abuse page renaming
2. when adding a page to a document, make it named at once!
---
I have solutiom which based on ShapeSheet, but I can't even explain it in Russian   ???
24
General Visio / Re: Reference page by ID instead of name
« Last post by dmac101 on October 21, 2021, 06:53:10 PM »
Thanks wrapperdude, I will search through forum for TOC and read through these posts.
25
General Visio / Re: Reference page by ID instead of name
« Last post by wapperdude on October 21, 2021, 06:44:21 PM »
A bit of work, yes.  A method could be
1) arrange the pages as desired
2) run the macro to re-sync the page na.rd as needed
3) to create index, then,
    A) loop thru document page collection, one page at atime
          I) get page name
         II) get page number
        III) store in an array
    B) for each page, loop thru shape collection, putting desired info in an array
    C) after all pages and shapes have been identified and info collected, pull info out of the array(s) and place on your index page.

Yes. It will take a bit of coding to do this.  Search the forum for Table of Contents.  There are a variety of schemes that have been developed.  One might be adaptable for your task... saving you much work.
26
General Visio / Re: Reference page by ID instead of name
« Last post by dmac101 on October 21, 2021, 05:24:51 PM »
Thanks Guys,
Looks like it is not going to be a simple solution to achieve the end goal which is to get page names and drawing id's which i have stored in a shape on each page and create an index page.
27
General Visio / Re: Reference page by ID instead of name
« Last post by wapperdude on October 21, 2021, 11:59:52 AM »
To supplement the reference provided by Surrogate, this reference, "Elements of Visio Formulas", provides bit more detail plus examples.
https://docs.microsoft.com/en-us/previous-versions/office/developer/office-xp/aa200961(v=office.10)
28
General Visio / Re: Reference page by ID instead of name
« Last post by wapperdude on October 21, 2021, 09:34:05 AM »
Here's small macro to reset the local and universal page names so that they match. 
Code
Sub fixPgNames()
'Call this sub if page names are creating an error and need fixing.
    Set pgsObj = ActiveDocument.Pages
    For Each pgObj In pgsObj
        pgObj.Name = pgObj.Name
        pgObj.NameU = pgObj.Name
    Next
End Sub
29
General Visio / Re: Reference page by ID instead of name
« Last post by Surrogate on October 20, 2021, 11:12:01 PM »
https://docs.microsoft.com/en-us/office/client-developer/visio/about-cell-references
There is article about references, i mean you can't use number for page identify

After renaming of page local (name as user can see) and universal (name as Visio engine know) page names are different !
English interfaceRussian interface
30
General Visio / Reference page by ID instead of name
« Last post by dmac101 on October 20, 2021, 08:34:10 PM »
Hi All, Just wondering if there is a way to reference pages by id or page number rather than by page name.
I have created shape data for each page, linked to the title block and was trying to access this from the main index page
Currently I am inserting a field to a table using the following formula.
=Pages[Site Layout]!Sheet.81!Prop.Title.
I would prefer to reference the page by ID as this will allow for changes in page names.
Is this possible?
Pages: 1 2 [3] 4 5 ... 10