controlling instances of excel error

[alrodopial]

Hi,
From an instance of excel ( file "MasterMacro") I run the below macro that executes a macro in another opened instance of ecxel ( file "VinR" and macro name "SaveFiles")
The SaveFiles macro renames the file - I guess here lyes the problem -

from the "MasterMacro" file

Code: Select all

Sub VinR_H_SAVEfiles()
   
    'Dim xlApp As Object
    'Dim NameOfFile As String
   
    NameOfFile = "C:\Excel files\VinR-H.xlsm"
    Set xlApp = GetObject(NameOfFile) ' the file is already opened
   
    xlApp.Application.Run "'VinR-H.xlsm'!SAVEfiles"
    xlApp.Close
    xlApp.Quit '........ ERROR here ................
   
    Set xlApp = Nothing
   
End Sub

and the macro "SaveFiles" from the second excel instance

Code: Select all

ThisWorkbook.Save
    saveNAME = ThisWorkbook.Sheets("MyBets").Range("BP5").Value
    ThisWorkbook.SaveAs Filename:= _
        "C:\Excel files\" & saveNAME & ".xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

The error I get is:
Runtime error 2147417848(80010108)
Automation error
The object invoked has disconnected from its client

It may have to do with setting the variables correctly to excel application, workbooks etc ....
Any ideas?

[negapo]

Hi alrodopial

I dont know why the error comes up but why dont you run all the code from the first workbook like this:

Code: Select all


    Dim ExcelFile As Excel.Workbook, FileLocation As String, SaveName As String
   
    FileLocation = "C:\Excel files\VinR-H.xlsm"
    Set ExcelFile = GetObject(FileLocation)
    With ExcelFile
        SaveName = .Sheets("MyBets").Range("BP5").Value
        .Save
        .SaveAs "C:\Excel files\" & SaveName & ".xlsm", 52, False
        .Close
        .Quit
    End With
    Set ExcelFile = Nothing

 

Didn't test the code but it's just the same routine all done from the first workbook.
You could also improve the code by first checking if the workbook is already open (if you need it).

Hope it helps

[alrodopial]

Thanks for the idea,
I though it also too but is not good for what I want.

In brief I want with a click (macro)(from a new excel file or not it doesn't matter):

1.save an opened excel file (lets call it file1, now it contains new data stored during while opened),
2.rename it ,save it and close it
3.open the file1 (with the new data), run another macro (clear unnecessary data) ,save it and close it (it will be ready for next day)

Any ideas?

if i run a macro with steps 1 and 2 and before macros' end i place some code to run step 3 after 15secs (Application.OnTime) will it work?
The file will be closed at that time

[alrodopial]

if i run a macro with steps 1 and 2 and before macros' end i place some code to run step 3 after 15secs (Application.OnTime) will it work?
The file will be closed at that time

The file will have other name now, will it work?

[osknows]

The line Set xlApp = GetObject(NameOfFile) is probably not doing what you think. If the file is already open in ThisWorkbook then it will get the object that is already open. If the file is open in another instance of Excel it will not reach across Excel instances but will open it again in Thisworkbook.

Either way the xlApp object is a Workbook and not Application so that's why you get and error on line xlApp.Quit - you can't quit a workbook.

If you really need to reach across Excel instances then you will probably need to use the API function FindWindowEx.

Alternatives are to create another instance of Excel first using Set xlApp = CreateObject("excel.application") and open it in there or just open it in the current instance of Excel like you are doing already.

[osknows]

Does wb.SaveCopyAs do what you need?

Where wb is Thisworkbook/ActiveWorkbook/another workbook

It will save a copy of a workbook with a new filename in it's current state.

[negapo]

Missed that osknows, your right, it's an instance of a workbook, you cannot "quit" it.
alrodopial, you just want to close it after the save as and open a new one right?

[alrodopial]

The SaveCopyAs did the trick, the good thing is that the new file is copied/stored at the background , no new excel instances are been created.
I will search for a while the idea of a "master macro" file , it will be useful when you want many macros to be run in several excels files/instances of excel, at the end of the day when you "summarize"

Thanks os,
Thanks negapo