Run Visio Report From VBA

Started by phillip_141, November 29, 2016, 02:00:19 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

phillip_141

Hello all,
I have been working on some code to run a report from vba so that I can modify the report as it comes out. I found this bit of code on here (among other places):


Visio.Addons("VisRpt").Run ("/rptDefName=Report.vrd/rptOutput=EXCEL")


but I have found this code gives me an error. Specifically it says that the report definition is not valid. I have found by trial and error that if I remove the output bit like so:


Visio.Addons("VisRpt").Run ("/rptDefName=Report.vrd")


then it will run, but it makes the user choose which format to use. This is not a huge problem, but it would be nice to know why the output definition piece doesn't work so that I can skip interacting with the user

Thanks for any help you can provide,
Regards
Phillip




wapperdude

Not at my computer, but, as an observation, is there a missing space between the ".vrd" and the "/rptOutput"?

Wapperdude
Visio 2019 Pro

phillip_141

I thought that a space would help as well, but when I tried it the system acted at though "/rptOutput=EXCEL" was not even there. That's how I discovered that the output bit was causing the issue.

wapperdude

I think I found the reference that you used.  In it, initially there was no space, but then the original poster corrected his syntax, and problem went away.  For reference, this is what his final code line looks like:


Visio.Application.Addons("VisRpt").Run ("/rptDefName=Cable_akt /rptOutput=EXCEL")


I notice that the .vrd file type is missing from the statement.

There might be another setting that needs to be enabled...VBA probably needs access to the Excel library. Worth a shot.

Wapperdude
Visio 2019 Pro

wapperdude

At my computer...
1)  Definitely need the .vrd.
2)  Having or not having a space after the vrd doesn't make a difference.
3)  Don't need to set the Excel library reference to use runaddon
4)  If the rptDefName=xyz.vrd doesn't exist, or can't be found, you'll get an error:  report definition is invalid.

Here's example of working syntax:

    Visio.Application.Addons("VisRpt").Run ("/rptDefName=space.vrd/rptOutput=EXCEL")


This still gives a pop-up window that requires hitting OK button.

Wapperdude
Visio 2019 Pro

phillip_141

Odd. On my machine 1, 3, & 4 hold true, but 2 does not. Having the space causes the output choice to be ignored, though the report does still run, and not having the space causes the report definition is invalid  error. You say that even with your working code the OK window still pops up, so I suppose I'll just have to trust to the user to be sensible.

Now that I have moved on  from that issue, I have encountered a new issue. When I have no workbooks already open and I run the report I have no issues manipulating the resulting excel workbook from visio vba. However, if I  already have open workbooks the report opens a new instance of Excel which is makes it difficult to manipulate the report. Short of closing all workbooks beforehand, do you know of an efficient way to switch instances?

Some things I have tried:

Set xlApp = GetObject(, "excel.Application") this doesn't work because get object only seems able to get the first instance of excel

Set xlApp = GetObject("Book1.xlsx").Application This gives a syntax error
Run-time error '-2147221020 (800401e4)'
Automation error
Invalid syntax


Set xlApp = GetObject("Book1.xlsx", "excel.Application") This gives an invalid reference error
Run-time error '432'
Object reference not set to an instance of an object


Any help you could provide would be much appreciated, and thank you for your help so far.

wapperdude

For reference, as I don't know if this would make a difference, I'm using Win7, Visio2007, Office10.

When the pop-up menu appears, the Excel option is pre-selected...but users being users, that could get changed.

As for the multiple Excel Windows, unless they're needed, I'd use vba to close all the Excel Windows before running the report.

Wapperdude
Visio 2019 Pro

phillip_141

The pop-up I see also has Excel preselected, so the user doesn't really need to make a decision, but users do silly things sometimes so I just wanted to avoid that issue. As for closing all excel books beforehand, I considered that solution but I would rather not have to do that. My users will likely be interacting with other excel documents and forcing them to close everything before exporting the data would be a headache.

wapperdude

As far as having multiple Excel Windows go, you might try checking Excel forums.  I would think that the window the report creates is the active window...so the report processing ought to be valid, including report manipulation.  If you bounce between Windows, then that could create issues.  At times, there have to be reasonable restrictions/assumptions or the coding may become very involved to handle possible scenarios.

But explore his from the Excel perspective and maybe there's some tricks.  But it sounds like you're moving outside the vision of the Visio report tool.

Wapperdude
Visio 2019 Pro

phillip_141

You would indeed think that the report window becomes the active one, but it doesn't seem to. I've tried acting on the active excel window and it always defaults to the first window that was open for whatever reason.
Well I'll try to find some solutions and if I figure it out I'll update this post so others can benefit from my fumbling :)
Thanks for your help Wapper.

wapperdude

Since you're exploring options, here's a couple of links to investigate.

1) This link provides code to extract Shapedata, send it to Excel, format Excel, and then insert back into Visio.  It doesn't use the built-in capability.  The extraction criteria could be modified, as well  the formatting.  Inserting back into Visio can be omitted with minor edit.  http://visguy.com/vgforum/index.php?topic=7302.0

2)  This is a new topic.  I've not explored it at all, but has some nice convenience features.  http://visguy.com/vgforum/index.php?topic=7572.0

HTH
Wapperdude
Visio 2019 Pro