Open Excel with MANUAL calc on

[CedarTree]

Hello,
Trying to open an Excel worksbook from Access, but I don't want Excel to open it up with Automatic Calc mode on. Excel object won't let me set calculation mode to manual or automatic unless I have workbook open. So I open a temporary workbook, set calc mode to manual, then try to open the real workbook. But it still keeps auto calc mode on. Suggestions?

Here's my current code:

        Set gobjExcel = CreateObject("Excel.Application")
        gobjExcel.Visible = True
        Set gobjBook = gobjExcel.Workbooks.Open("C:\Users\Cedar\Desktop\ManualCalc.xlsx", False, False)
        gobjExcel.Calculation = -4135 'xlManual
        gobjBook.Close
        Set gobjBook = gobjExcel.Workbooks.Open(sFullFileName, False, False)
        Set gobjSheet = gobjBook.ActiveSheet

 

[CedarTree]

P.S. When I close the temp workbook, I can SEE the calc setting in Excel = automatic or manual. But I can't change it. And it remembers what I set it to if I save the temp Excel file, but as soon as I open the Excel file I'm really interested in, it goes back to Auto calc.

 

[Dreamweaver]

I'm not a Excel guy but shouldn't gobjExcel.Calculation relate to with workbook and not the excel object.

mick

 

[Minty]

I think you would have to save the existing file in manual calculation mode?
Then turn it back on when required and save it in the state you need.

This https://excelribbon.tips.net/T011577_Forcing_Manual_Calculation_For_a_Workbook.html seems to suggest that it's systemwide setting so probably not what you need to read...

 

[Dreamweaver]

it's systemwide setting

Thanks @Minty noted for future

 

[Gasman]

Leave the manual file open and open your required xl file?
Then close the manual file.

That works, I just tested it.

 

[Minty]

@Gasman - Nice cunning stunt!

 

[Gasman]

@Gasman - Nice cunning stunt!

Strange thing is though, if I open one excel file it is left as Manual. If I open another it goes back to Automatic.?

 

[Dreamweaver]

Just found this https://stackoverflow.com/questions...ion-mode-to-manual-when-opening-an-excel-file

 

[CedarTree]

Thanks everyone. Yeah I just found https://stackoverflow.com/questions...ion-mode-to-manual-when-opening-an-excel-file as well. However, that uses an Excel macro to open the 2nd TARGET file... but how can I then take over that TARGET file in Access VBA to manipulate it? There must be a way I presume to grab an already open Excel file within Access using perhaps the file name?

 

[CedarTree]

Strange thing is though, if I open one excel file it is left as Manual. If I open another it goes back to Automatic.?

Part of my problem!

 

[Gasman]

Part of my problem!

You no longer have a problem. I provided a solution.?

 

[cheekybuddha]

but how can I then take over that TARGET file in Access VBA to manipulate it?

Research:

  Set gobjExcel = GetObject(, "Excel.Application")