Aaargh. Stupid Visio Reports

[wapperdude]

Well, what seemed like a simple task has developed into the most immoveable object.  The goal is to loop thru multiple pages, run a simple shapes report on each page, let the report sort the values, and ship off to Excel.  Easy peasy.  But the 2nd half is to read each pages report and import some values from the Excel file.  Yikes!  Can it be thaaaat hard?  So, the code goes thru the first page, generates the report, and imports result from Excel (debug.print).  Goes to 2nd page, runs report, and code bombs when trying to access results from 2nd Excel file.

I've tried deleting the Excel files/workbook or not.  Tried naming.  Tried activating.  My head hurts.  My fingers ache.  The code is posted below.  Rather simple.  There's a comment line where it bombs on 2nd loop thru.  The Visio file is simple too.  Two pages.  Each page has 2 or more shapes.  Preferably differing amounts so that you can tell which page is analyzed.  I'm including the VRD file.  Put is somewhere, and change the code to point to it.

The code only has my latest attempts.  It would be very confusing to have the myriad of versions ... even commented out.

And YES, it would have been easier to generate the code from scratch and not bother with the Reports (non)-functionality.

Code Select Expand


Sub FetchExcelData()
    Dim XlApp As Object
    Dim XlWrkbook As Excel.Workbook
    Dim XlSheet As Excel.Worksheet
    Dim myUsedRng As Range
    Dim LastRow As Long
   
'    Dim vsoCharacters1 As Visio.Characters
'    Dim visSel As Visio.Shape
   
'    i = 1
       
    For Each pg In Visio.ActiveDocument.Pages
        Visio.ActiveWindow.Page = pg
        Visio.Application.Addons("VisRpt").Run ("/rptDefName=D:\Reports\ShapePos.vrd /rptOutput=EXCEL")

        Set XlApp = GetObject(, "Excel.Application")
       
        Set XlSheet = Excel.Workbooks("Book1").Sheets("Sheet1") 'always fails at this step
        Debug.Print XlSheet.Name
   
        Set myUsedRng = XlSheet.UsedRange
        LastRow = myUsedRng.Rows.Count                      'This is last populated row.
       
        With XlSheet
            For iRow = 3 To LastRow
                vsoShpID = .Cells(iRow, 1).Value
                Debug.Print vsoShpID
            Next
        End With
       
'        i = i + 1
'        ActiveWorkbook.Close savechanges:=False
'        XlApp.Quit
    Next
   
'    ActiveWorkbook.Close savechanges:=False
'    XlApp.Quit
'    Set XlSheet = Nothing                              'Release worksheet
'    Set XlWrkbook = Nothing                            'Release workbook
'    Set XlApp = Nothing                                'Release Excel
   
End Sub


[Croc]

This version works for me:

Code Select Expand

Sub FetchExcelData()
    Dim XlApp As Object
    Dim XlWrkbook As Excel.Workbook
    Dim XlSheet As Excel.Worksheet
    Dim myUsedRng As Range
    Dim LastRow As Long
   
    For Each pg In Visio.ActiveDocument.Pages
        Visio.ActiveWindow.Page = pg
        ComStr = "/rptDefName=G:\Temp\Rpt\ShapePos.vrd /rptOutput=EXCEL " & _
                 "/rptOutputFilename=G:\Temp\Rpt\Ex.xls /rptSilent=True"
        Visio.Application.Addons("VisRpt").Run (ComStr)

        Set XlApp = GetObject(, "Excel.Application")
        Set XlWrkbook = XlApp.Workbooks.Open("G:\Temp\Rpt\Ex.xls")
       
        Set XlSheet = XlWrkbook.Sheets("Sheet1")
        Debug.Print XlSheet.Name
   
        Set myUsedRng = XlSheet.UsedRange
        LastRow = myUsedRng.Rows.Count                      'This is last populated row.
       
        With XlSheet
            For iRow = 3 To LastRow
                vsoShpID = .Cells(iRow, 1).Value
                Debug.Print vsoShpID
            Next
        End With
       
        XlWrkbook.Close
        XlApp.Quit
        Set XlApp = Nothing
    Next
    MsgBox "Done"
End Sub

[Hey Ken]

Mr. Dude:

   Didn't try to run it, but if you say...

Code Select Expand


Set XlApp = GetObject(, "Excel.Application")
Set XlSheet = Excel.Workbooks("Book1").Sheets("Sheet1") 'always fails at this step


   ...shouldn't the second line say "XlApp" instead of "Excel"?

   Maybe.

   - Ken
       
       

[wapperdude]

@Croc:  while I'm not at my computer, looking at the code it strikes me as brilliant and a wonderful solution.  I had previously concluded that the issue was the way the Radeon was being invoked.  I tried to find info, but was unable to.  Where did you learn about the "switches" used in "comstr"?  Never would have thought of some of those.  Can't wait to try this.

@Hey Ken:  Yep.  That was my first formulation.  Unfortunately, neither made a difference, and both worked successfully 1st loop thru and failed 2nd loop thru.  So I left "Excel" in.  After many tries, it became obvious too me...a very slow, frustrating realisation, that the 2nd report execution, the Excel window wasn't being recognized by the code.  Had to be an invocation issue.  That's why Croc's code strikes me as correct.  It explicitly refers to output file, and I think the rptSilent may suppress the "phantom" window.

I think this can help a lot of code writers who want to run the Visio Reports via code.  Will confirm a little later.  In the meantime, additional thoughts are welcome and encouraged.

Wapperdude

[Croc]

Where did you learn about the "switches" used in "comstr"?

I do not remember exactly. First I searched the Internet. Then in dll.
Everything that I managed to find is given in my article https://visioport.ru/blog/index.php/2-otchety-visio
But it is in Russian.
Below I have translated a fragment in which it is told about switches:

Unfortunately, the possibility of a program call is not properly documented. Therefore, information about keys and valid values ​​will be somewhat incomplete:
/rptDefName - The report name assigned in the report builder, or the full name of the external report definition file.
/rptOutput - the form of presentation of the result. Valid values are:
    HTML - result in HTML format;
    EXCEL_SHAPE - the result is displayed in the shape, directly in the Visio document;
    XML - the result in the XML file;
    EXCEL - result in Excel file.
/rptOutputFilename - (optional) the name of the output file (including the path). Used when HTML, XML or Excel are specified as the output format.
/rptSilent - (optional) an indication that you do not need to show the report wizard.
By the way, the "empty" call Visio.Application.Addons ("VisRpt").Run ("") results in opening the report builder form without further action. The report is not generated, the form is waiting for a choice or generation of a new report. If data output to a shape is specified, the wizard does not open. And when outputting to a file, the wizard opens by default and this can only be prevented by using the /rptSilent switch.