CODEWITHSUNDEEP
excelvbacsv
athos
athos

Reputation: 6413

Avoid side effect of workbooks.open a .csv file

i'm using vba to open an .csv file 

Sub open_csv_file()        
    Workbooks.Open("c:\test.csv")
    ActiveWorkbook.Close    
End Sub

It works, but with a side effect: when the csv file is opened, the original .xlsm file start to recalculate.

Is there a way to avoid the side effect?

ps. I've already set Excel calculation as Manual.

Upvotes: 1

Views: 1396

Answers (3)

Imran Malek
Imran Malek

Reputation: 1719

The only workaround that seems to be working is to set Worksheet.EnableCalculation to False for each worksheet.

Try this

Sub open_csv_file()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.EnableCalculation = False
    Next ws
    Workbooks.Open ("c:\users\test\desktop\test.csv")
    ActiveWorkbook.Close
End Sub

Upvotes: 2

Michael
Michael

Reputation: 4848

The calling .xlsm file recalculates when opening a .csv file, but not when opening a .xls file. Renaming the .csv file to .xls before opening will prevent recalculation and still open the file as normal.

Sub open_csv_file()
    Name "c:\test.csv" As "c:\test.xls"
    Workbooks.Open "c:\test.xls", Format:=2
End Sub

Some error handling should be added to handle the situation where the new filename already exists.

Upvotes: 1

Florent B.
Florent B.

Reputation: 42528

One way to avoid the calculation is to open the file with another instance of Excel:

Dim app As Excel.Application
Dim csv As Workbook

Set app = New Excel.Application
app.Visible = True

Set csv = app.Workbooks.Open("c:\test.csv")

Upvotes: 4

Related Questions