Macro changing calculation to manual not working

Hi there,

I have macro that I run each day that opens a series of CSV files and pastes the content into a report spreadsheet. Because there are lookup formulae connected to the data, I am using a line of code in the macro (Application.Calculation = xlCalculationManual) to change Excel to manual calculation mode before running the rest of the macro, then change back to automatic again after it is complete.

The problem is that when Excel opens and pastes the data, it recalculates automatically despite being set to manual. I have gone through the code and I'm sure the change is working, and I've even used msgbox to report the calculation mode at each step of the process and it shows manual all the way through, but it is still automatically calculating.

Can someone help point me in the right direction as to what I might be missing here? Calculation should be controlled at the application level rather than the workbook or worksheet level, so there shouldn't be settings in any of the files that are causing it, and they are csv files so there are no macros in any of them that could be resetting it. I'm also doing the change to manual calculation before I open the files, just in case it could be picking up that calculation was automatic when they were first opened.

Is this an issue with Excel or am I doing something wrong here? I'm running Excel 2003 on a Windows XP system if that helps identify the problem.

Thanks
Alexis

Thanks Richard,

No, the formulae aren't linked to another workbook, but they do look at the sheet where the data is pasted.