Solved

Get Excel workbook as Object from Visio VBA

Posted on 2013-05-10
4
1,823 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Find word and 6 digit number 22 97
How to read BOM (Byte Order Mark) from csv file. 4 37
excel pivot question 4 40
Manually enter date in datepicker 24 35
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 …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

920 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

15 Experts available now in Live!

Get 1:1 Help Now