Separate Instance of Excel

[WarsEagle]

Hi everyone,

Long, long time dweller on the site but this is my first post. (I know a lot of people say that but then ask quickly found answer.)
I have always been able to find answers or answers adaptable to my issues.

I currently have a personal macro-book that contains roughly 10k lines of code for various reports and automations.
However, I have recently stumbled across a situation that I cannot seem to remedy.

The company I work for uses Sage Software. It has the ability to export data to excel in the .xlsx format.
The problem is when these reports are generate, and they open automatically, they open in a separate "instance" of excel.
This means that they are unable to be manipulated with my personal macro-book. This happens even if I already have excel open and other workbooks open.

How can I stop the report from opening in a new "instance" of excel?
I have searched around the web and this has been asked several times, but there is not an answer that I have found as of yet.

I do have the ability to save and close the report and then reopen to have access to the macro-book, but this defeats the purpose of automation of the macros.

Any help would be greatly appreciated.

Thanks

 

[jasonb75]

Welcome to Mr Excel, WarsEagle

export data to excel

As you're exporting from Sage rather than importing to excel, I think that the change will need to be in the Sage software. As such you would need to refer to dedicated Sage help to find out if this is actually possible via a setting, or if it would need a change to code that is not accessible by the end user.

 

[NdNoviceHlp]

Hi WarsEagle and Welcome to the Board (finally)! The only way that I can suggest is for U to control the operation of Excel from either VB6 or Word.... or get Sage to change (yeah right). Similar to operating Word from XL, you could open the Sage wb which will create an XL instance then run this code to set your XL instance to the same Sage instance to open your wb… not ideal but possible. HTH. Dave

Public ExlObj As Object

Public Sub OpenExcel()
'open Word application
On Error Resume Next
Set ExlObj = GetObject(, "excel.application")
If Err.Number <> 0 Then
On Error GoTo 0
Set ExlObj = CreateObject("excel.Application")
End If
End Sub

 

[Jaafar Tribak]

How about using an addin ... The addin will automatically be opened in each instance... The addin will have code that detects if the sage workbook is being opened. If so, the code in the addin will temporarly save the adding to disk and reopen it as ReadOnly in the macro-book excel instance... finally the addin will close and delete the temp sage workbook file that was previously saved to disk . (You can always use the handy ChangeFileAccess to make the xlsx workbook Read & Write again if need be.

In order for this to work, you will first need to check if the addin is loaded in the sage instance as that is not always the case when software automates excel.

 

[WarsEagle]

Hey everyone,

I was able to create an add-in and then have access to my custom macros.

Thanks for the replies.
I used Jaafar Tribak's method.

Sorry for the delayed response.