Input Visio Drawing File from Excel Data

Started by oj2011, January 12, 2021, 08:59:24 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

oj2011

I understand this question may be a stretch, especially for someone as inexperienced as myself with VBA/Macros.

Is it possible to import the data and have it populate the file/drawing from the external data?

Example: Excel-Sheet 1- Column 1- Row 1 data equals TEST 1     
              File named TEST 1 has a corresponding drawing that represents TEST 1
              When the data is imported the drawing is placed on the page. 

Goal: Place a drawing with grouped shapes on the page based on excel data.


I apologize that this is rather vague. I hope that I'm not wasting anyone's time. Thank you for any help in advance. 

Croc

QuoteIs it possible to import the data and have it populate the file/drawing from the external data?
It is possible without a doubt.
For example, the well-known Data Vizualizer performs something like this.
See for example "Using the Visio Data Visualizer in Excel" https://bvisual.net/resources/excel_visio_data_visualizer/
Also, you can use macros instead of Data Vizualizer.
In similar cases, I prefer to develop special stencils containing the necessary shapes and macros. You can familiarize yourself with a similar approach in the following articles:
"Visio stencil for data import" https://visioport.ru/gum/6/importer_en.htm
"How to make a data visualizer for Visio Standard" https://visioport.ru/work/index.php/14-visualizer-standard
"Radar Graph stencil for Microsoft Visio" https://visioport.ru/work/index.php/vdvn/visdwnlsten/11-radar-graph
"Bubble Graph stencil for Microsoft Visio" https://visioport.ru/work/index.php/vdvn/visdwnlsten/12-bubble-graph

oj2011

Thank you!

I will start reading on all of that. I really appreciate the help.

Hey Ken

OJ:

   It's also possible to get at the contents of the Excel spreadsheet directly from Visio VBA.

   Here's the code that allows you to get full access to any spreadsheet:



Sub AccessExcelFromVisio()

Dim ExcelApplication As Variant
Dim YourWorkbook     As Workbook
Const FileName = "c:\Test.xlsx"

Set ExcelApplication = CreateObject("excel.application")
ExcelApplication.Visible = True
Set YourWorkbook = ExcelApplication.Workbooks.Open(FileName)

' do whatever you like, such as...
YourWorkbook.Sheets(1).Range("A1").Value = "Whatever"

YourWorkbook.Close
Set YourWorkbook = Nothing
ExcelApplication.Quit
Set ExcelApplication = Nothing

End Sub



   I tested this and it worked for me.  Just remember to first add a reference to any Excel Object Library using the debugger (alt-F11, then Tools, References).

   Hope this helps,

   - Ken

Ken V. Krawchuk
Author
No Dogs on Mars - A Starship Story
http://astarshipstory.com