Visio Guy

Visio Discussions => Programming & Code => Topic started by: anico on November 08, 2018, 05:47:39 PM

Title: Pulling in data from Excel (without Data Import Utility)
Post by: anico on November 08, 2018, 05:47:39 PM
Hello world!

I have very little experience with Visio VBA and have a high level question.

Background
I have an Excel file (it's a questionnaire). It's formatted simply - Column A is the question, column B is the answer. Most are Yes/No although some a free-text. Additionally, some questions have multiple answers (for example the question in column A has answers in columns B, C, D and so on...)

What I want to know is how can import this data into Visio using VBA. I have Visio Standard 2016 so I do not have the data import utility that is included in Visio Professional. My goal is to use this data in macro functions (like IF/OR/Loop statements), as well as to populate labels & shape text on my diagram with these values.

The Goal
I am looking for a way to pull the data in from Excel, store it in an array or as variables, and then use that data to drive further macro functionality.

Is this possible? I would really appreciate the help :)
Title: Re: Pulling in data from Excel (without Data Import Utility)
Post by: Yacine on November 08, 2018, 07:22:24 PM
I found a funny link answering your exact question.
http://lmgtfy.com/?iie=1&q=import+excel+in+visio+vba+site%3Avisguy.com

No offence meant ;).
Cheers,
Y.
Title: Re: Pulling in data from Excel (without Data Import Utility)
Post by: anico on November 08, 2018, 10:47:04 PM
I appreciate the blunt answer  ;D
I promise I did my fair share of Googling beforehand though haha. I made this post hoping to learn more about the code that's used to do the import. I've read through a couple of posts with sample code (like the one from your search) and I don't follow along with the code - like I read it and don't know what's really happening. Is there perhaps a post out there that explains more? I have practically no experience with VBA (or any coding) and am jumping headfirst into this project!

I'd appreciate any light you may be able to shed on this  :)
Title: Re: Pulling in data from Excel (without Data Import Utility)
Post by: wapperdude on November 08, 2018, 11:00:04 PM
Since Yacine answered your question, I'll answer a different question or two...

Yes, with VBA, you go thru an Excel file and pull in data from designated rows and columns.  But, it takes some work.  (Note:  I'm talking from a V2007 Std perspective, but the overall process should be about the same for V2016 Std).

Yes, with VBA, the data you pull in can be stored in a Visio shape and/or, in an array (VBA structure). 

Yes, with VBA, you can copy portions of an Excel spreadsheet and store it on a Visio drawing page.

Finally, yes, with VBA, you can use these values in either a macro or in a specific Visio shapesheet cell, so that it's available for additional shapesheet processing.

I think Yacine was making two points:
1) your questions are very vague, so, they're difficult to answer.  There isn't any single instructional resource that covers Visio / VBA.  Not generally.  Not specifically.
2)  search.  You need to search.  Google can help.  You can somewhat narrow the field with Visio vba get excel spreadsheet values, or similar searches.
3) search the forum.  Here's a link that might help with your problem:  http://visguy.com/vgforum/index.php?topic=7964.0 (http://visguy.com/vgforum/index.php?topic=7964.0)

...your task is do-able, but, somewhat involved.

Wapperdude

Title: Re: Pulling in data from Excel (without Data Import Utility)
Post by: jaryszek on November 09, 2018, 08:22:39 AM
Hi,

i asked similar question in this topic:
http://visguy.com/vgforum/index.php?topic=8616.0 (http://visguy.com/vgforum/index.php?topic=8616.0)

I pin up to this topic,
Best,
Jacek