Run VBA Code in new instance of Excel

[tino_szczvrcic]

Hello,

I can export a report in Excel format from an application, but it opens the report with a new instance. I can then rejig the layout to my liking, but I'd like to be able to do this with VBA. What's the best way, if any, to do this, given it's a new instance of Excel, other than copying the code to the current file?

 

[Yongle]

I am not exactly sure what you really want

I am guessing that you want to use VBA to modify the workbook generated by your app (Workbook_App) without closing and reopening it (and without copying the data into another workbook)

Here is one way to achieve that:

1. Place this VBA in a standard module in a NEW workbook ( the one that will contain the VBA you require - Workbook_X )

Sub WorkInOtherWorkbook()
    Dim sh As Worksheet, wb As Workbook
    Set sh = GetSheet
    Set wb = sh.Parent
    MsgBox wb.Name & vbCr & sh.Name
End Sub

Function GetSheet()
    Dim cel As Range
    Const msg = "Activate sheet\select cell in app workbook\click OK"
    Set cel = Application.InputBox(msg, "Get Workbook", , , , , , 8)
    Set GetSheet = cel.Parent
End Function

Test like this

1. To avoid any confusion CLOSE Excel
2. Use your app to generate your report and Workbook_App
3. (With Workbook_App open) open Workbook_X
4. Use {ALT}{TAB} and make Workbook_App active
5. {ALT}{F8} brings up list of macros
6. Select and run WorkInOtherWorkbook
7. Do what the input box tells you to do
8. The message box should confirm what you already know

To do anything to Workbook_App
- qualify with sh or wb EITHER in WorkInOtherWorkbook OR pass those variables to another sub

sh.Range("A1").Value = "XX"
wb.Save

 

[BlueHornet]

You're reminding me of a setup that I created some years ago to handle a particular export from an Access database. The database contained the full collection of data that I needed to maintain, but from time to time I would export a particular query's result to a flat file "in Excel format". The Access macro then called up a different Excel workbook file, which was always maintained as a shell for the export data. The stand-alone workbook had an auto-open macro that would find and import the Access query results into the predefined format, and then run additional code to graph the results ... and the graph was the primary reason for the export to Excel in the first place.

So that would be my advice: Create the Excel file that you want, then use it to import the "export to Excel" file created by the other application, and always Save As some other file, to maintain the integrity of your frame file.

 

[Norie]

You could try using GetObject to get a reference to the other instance of Excel.

If that's successful then you should be able to rejig the workbook as though it was in the instance you are working from.