Posts by Aldowski

  • Visio Standard 2016 and Excel 365 VLookup

    Hello;


    Visio Standard 2016 has no Data Menu; my question is related to the populating part of Shape Data with details from Excel, using VLOOKUP function in Excel.


    Example (network admin implementation related):


    Having network details in the Excel file for more devices including hostname, IP_address, access_app (ssh or telnet, for example), access_port (22 for example), user_id, excel_filename, excel_table, excel_keyIP (this would be key for linking Excel data to Visio Shapes).


    So, in the Visio, having excel_keyIP as linking field and all other fields as well, being populated from Excel table.


    The function to be involved may be VLOOKUP, with the table in Excel with multiple network devices (in rows), searching by excel_keyIP from the Visio field.


    Even with no Data menu in the Visio (Standard 2016), there is a possibility to have link between Visio and Excel (I managed it, but I did not get the final and complete connection as I am trying to explain here).


    Question is related to the searching and requesting specific key IP address being entered to the Visio key field (in the Shape data) then populating the other fields from the same row in the Excel to the Visio fields in the Shape Data (those other linked, imported fields are to be, of course, read only in Visio, changes may be done in Excel itself).Visio should have fields for excel_filename and excel_table, so it will be possible to get data from specified sources.


    The VLOOKUP function would be great; it should allow searching of the specific IP address at the Excel table, then filling the proper fields with related inputs, linked from Excel.


    With right click, first menu option at the Shape will be, for example, ssh to the IPaddress, thru port, using specific userID, all details from Excel (this step is relatively easy, the challenge is to get linked details from Excel to Visio).


    I established Visio Excel link, it is working - without functions (VLOOKUP) being involved.


    Please, how to use specific function when linking, searching, transferring data from Excel to Visio?


    There may be VBA command line like:

    Code
    Application.WorksheetFunction.VLookup(strExcelFile, strExcelTable.Range("B:F"), 5, False)

    Unfortunately, even I tried different combinations, it did not work; getting different error messages. Looks that Visio implementation of (this, Excel platform relation) VBA is not full or it has different commands?


    Thanks a lot for suggestions.