Link to home
Create AccountLog in
Avatar of Roger
RogerFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Get Excel workbook as Object from Visio VBA

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!
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Roger

ASKER

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
Correct - version makes no difference.
Avatar of Roger

ASKER

More great help from EE!
Bye