2

First-time poster here. I've written VBA within a Visio Document, which runs a ShapeReport and exports it as an Excel Workbook. In this code, I need to then activate that newly created workbook in order to run a separate macro on the exported data, but I can't figure out how to activate the workbook since I don't consistently know the name or file order (Workbooks(1) for example), and I can't set the export line as a variable. In the code below, I need the ActiveWorkbook.Name to print out the workbook that opens in the prior line. Best case would be to figure out how to assign that workbook to the ObjXLBook Variable to be able to easily reference it.

I've tried declaring a Workbook Variable and setting it equal to the line of code which exports/creates the Excel Workbook and I've tried doing nothing and structuring the macro under the assumption that the newly created Workbook would already be activated, but neither option worked.

Current Code:

Sub testExport()
    Dim objXLApp As Object
    Dim objXLBook As Excel.Workbook

    Set objXLApp = CreateObject("Excel.Application")

    'create new workbook via report export
    'this is the workbook that I need to activate and/or assign
    Visio.Application.Addons("VisRpt").Run ("/rptDefName=VarianceRep.vrd")

    Debug.Print (ActiveWorkbook.Name)
    Debug.Print (objXLBook.Name)
End Sub

Attempt 1: Assign Variable

Set objXLBook = Visio.Application.Addons("VisRpt").Run("/rptDefName=VarianceRep.vrd")

Attempting to set the variable results in "Compile Error: Expected Function or Variable". Leaving it blank results in "Run-time error '91': Object Variable or With block variable not set" and the debug printing nothing.

I need the debug to print out the name of the newly created workbook which contains the exported data. The second debug will only apply if I can assign the workbook to the objXLBook Variable as well.

Dan Hintz
  • 21
  • 2
  • Untested (I'm not super familiar with Visio), but maybe try `Debug.Print objXLApp.ActiveWorkbook.Name`. A bit of a shot in the dark, so I won't be surprised if it fails. – BigBen Jul 31 '19 at 01:11
  • Btw, note that you have a mix of early- and late-binding. It probably makes sense to be consistent. – BigBen Jul 31 '19 at 01:11
  • 1
    No dice, I still get the 'Object Variable not set error' and a blank printout. Thanks for the comment about binding though, I wasn't aware of the 2 options - never done cross application VBA before - I'll be going through my other macro to apply that, although it did not help in this particular situation. – Dan Hintz Jul 31 '19 at 02:03
  • When you run the Script, Does the Visio save the File somewhere or the Excel Workbook opens up in the Background ? – Mikku Jul 31 '19 at 03:03
  • As far as I can see the `addon("xxx").run` will not return an excel file/object. The duplicate link above includes a different method to get a reference to the generated excel file. – L8n Jul 31 '19 at 08:36
  • @L8n - that post held the key: ```Set AppExcel = GetObject(, "excel.application")``` placed just AFTER the line exporting the data & creating the new application instance successfully selects the just-opened workbook. Thanks for pointing me in that direction, I really thought I'd seen every relevant post on the internet :). – Dan Hintz Jul 31 '19 at 17:29

0 Answers0