Open Another Workbook - Don't Calculate

[tobermory]

Hello,

I'm trying to open another workbook, so that the second workbook just opens, but doesn't initially calculate.

Here's what I've tried, without success, the original workbook goes to Manual calculate, but the second one still opens as Automatic, and thus calculates when it opens.

Application.Calculation = xlManual
Application.Calculation = xlCalculationManual
Workbooks.Open ("T:\blah\blah\blah.csv")
Application.Calculation = xlManual
Application.Calculation = xlCalculationManual

Many thanks in advance should anyone be able to provide help.

 

[JoeMo]

Didn't think you could save a csv file as a csv file, then close it and retain formulas in it. If calculation is set to xlCalculationManual before you open an Excel workbook in an already open Excel application, the workbook will not calculate.

 

[tobermory]

Perhaps I should explain this better.

I have a spreadsheet, it's a .xlsm file. It's rather large (50MB) and has many formulas. It's usually on Manual Calculate, as when new data is inputted it takes 15-20 seconds to calculate everything again.

I need a script that will...

Open the csv file, copy the data from the csv file and paste it into the .xlsm sheet.

Which is what I've done, the issue is, when the csv file opens, it calculates everything in the .xlsm file.

I don't understand why it's doing that, as the .xlsm file is on Manual Calculate, but the .csv file opens as Automatic Calculate.

--------

Code as it stands...

Application.Calculation = xlManual
Application.Calculation = xlCalculationManual
Workbooks.Open ("T:\blah\EverythingElse.csv")
Application.Calculation = xlManual
Application.Calculation = xlCalculationManual

Windows("EverythingElse.csv").Activate
Range("A2:M50000").Select
Selection.Copy
Windows("Figures.xlsm").Activate
Sheets("Sheet1").Select
Range("A2").Select
ActiveSheet.Paste

Windows("EverythingElse.csv").Activate
ActiveWorkbook.Close False

 

[JoeMo]

That's interesting. Does the calculation state remain on Manual or is it switched to Auto?

 

[tobermory]

That's interesting. Does the calculation state remain on Manual or is it switched to Auto?

Basically, I press Esc while it calculates, thus breaking the script, and then check both spreadsheets.

The .xlsm file stays on Manual.
The .csv file is on Auto.

But upon the csv opening, I get the whole Calculating % in the bottom right hand corner (and the 15-20 sec lag associated with it), meaning it must be calculating the .xlsm (as there is nothing to calculate in the csv one).

 

[JoeMo]

I don't know how to prevent this, but here's a suggestion for a workaround you can try (untested).

In your PERSONAL.XLSB workbook, create a simple macro to open the csv file. You can put a button on the QAT so you can launch this easily.

In this macro, after the csv file is opened, reset calculation to manual (Application.Calculation = xlCalculationManual), then open your .xlsm workbook from the same macro and call your code (the code that's in the .xlsm workbook) from the same macro. The last step will probably require an Application.Run statement (rather than a Call) with a reference to the .xlsm workbook.

You will probably have to modify the code in the .xlsm workbook somewhat. For example, you will no longer need to open the csv file with this code.

If you decide to try this and run into problems, post back showing ALL the code involved, with details of the issue(s) so you can get some help.