Solved

Get Excel workbook as Object from Visio VBA

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
My experience with Windows 10 over a one year period and suggestions for smooth operation
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

810 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