importing a .csv file without excel application recalculating vba

Question

Hi,

I am trying to import a .csv file using vba,  I have stated at the beginning on the line of code  xlcalculationmanual however any time i run a macro to import the data held in the file the workbook recalculates.

I cannot write a macro into the .csv file to turn off the calculations as the file is overwritten everyday.

any ideas?

Thanks

Tom

Answer 1

The question is how did to you import the CSV data and what else did you do.

If you set the Calculation to manual, the workbook did not recalculate, even if you change a cell, that's a fact.

Example:

Sub Test()

  Dim SaveCalculation As XlCalculation

  SaveCalculation = Application.Calculation

  Application.Calculation = xlCalculationManual

  Range("A1").Formula = "=A2+1"

  Range("A2") = 1

  Application.Calculation = SaveCalculation

End Sub

Copy this sub into a normal module in a new file, press F8 to execute it line by line and look into the sheet after you execute a line what's happen.

You'll see that a formula is calculated if you enter (or change it), even if the calculation is in manual mode. That means you can not switch the calcultion TOTALLY OFF!

If you enter the value in A2, the formula in A1 is not recalculated as expected.

But the formula is recalculated if you set the Calculation property to an automatic mode!

Andreas.