Solved

Get Excel workbook as Object from Visio VBA

Posted on 2013-05-10
4
1,799 Views
Last Modified: 2013-05-10
Background:
I can open and close a visio file from code in MasterExcel.xlsm.
The MasterExcel and visio files cross-refer to each other's object models, via Tools>References, and MasterExcel.xlsm controls the creation and order of visio pages.

The MasterExcel code:
Set visioApp = New Visio.Application 'early binding    
Set visQDoc = visioApp.Documents.Open(masterVisioQFile)

Question:
I want visio VBA modules to use some variables created in the Excel environment.
How do I write code in the Visio file to:
- find out if the MasterExcel file is open?
- Get the MasterExcel file as an object  (if it is open).

My first attempts at code in Visio.document code failed. Here are two examples with error reports:

----------------------------------------------------------------------------------------------------------------
Private Sub Document_DocumentOpened(ByVal Doc As IVDocument)

Set xlAppFromVis = GetObject("C:\Users\Roger\Desktop\VisAuthDev_Current.xlsm").Excel.Application
'error 438 does not support property or method

'Set xlAppFromVis = GetObject(, "VisAuthDev_Current.xlsm").Application
'Set xlAppFromVis = GetObject(, "VisAuthDev_Current.xlsm").Excel.Application
'activeX cant create object Error 429

end sub
-------------------------------------------------------------------------------------------------------------------------

I understand GetObject should return VisAuthDev_Current.xlsm as an object, even if other excel workbooks/applications are open, and that getObject is not dependent on the version of Excel. Is this true?

Thanks!
0
Comment
Question by:Kelvin4
  • 2
  • 2
4 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 39154767
The first one should be
Set xlAppFromVis = GetObject("C:\Users\Roger\Desktop\VisAuthDev_Current.xlsm").Application

Open in new window

0
 

Author Closing Comment

by:Kelvin4
ID: 39155882
Thanks, Rorya: Its now working. Re my rider Questions:

With your code solution I confirmed:    "I understand GetObject should return VisAuthDev_Current.xlsm as an object, even if other excel workbooks/applications are open?"

"..getObject is not dependent on the version of Excel. Is this true?"

Could you confirm that getObject as used here is not dependent on the MS Office version of Excel?

Appreciately,
Kelvin
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39155921
Correct - version makes no difference.
0
 

Author Comment

by:Kelvin4
ID: 39155930
More great help from EE!
Bye
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article will show you how to use shortcut menus in the Access run-time environment.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now