[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Get Excel workbook as Object from Visio VBA

Posted on 2013-05-10
4
Medium Priority
?
2,016 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 2000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

873 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