Stopping calculation when opening file

[RyanD]

Hi all,
i am having a small problem.
I have an excel spreadsheet with VBA which opens another Excel spreadsheet, copies and pastes data from the second spreadsheet to a specific spreadsheet in the first file and closes the second file.

The first workbook has many spreadsheets that have formulas which are linked to the spreadsheet where the data is being pasted.

The problem is that when the second file is opened, Excel wants to update all these links while it is pasting across. I have Calculation on manual and Workbooks.Open UpdateLinks:=0

Is there anyway to just copy the data across without it wanting to update the linked data?

Thanks,

Ryan

 

[PaddyD]

Hi - welcome to the board.

Not a direct answer ( & possibly totally off the mark, not knowing what else the code is doind, what's going on in the workbook etc), but could you adapt the method here:

http://www.ozgrid.com/

("Extract from workbook" topic on the VBA page) to your needs? would save you opening the other workbook altogether.

Paddy

 

[RyanD]

Thanks PaddyD. I had a look at the link you suggested but unfortunately i cannot use it. The second file is a generated CSV file from another application and i cannot add VB code to it.

If i open the second file manually, select the data, copy and paste into the first worksheet, it doesn't recalculate/update references. It only seems to do this when i open the file via VB, copy and paste.

Ryan

 

[skywaz]

Try this:

application.screenupdating = false

' your code here

application.screenupdating = true

Also, can you close the second workbook before performing the paste function? This may help.

Let us know,

Darin

 

[RyanD]

turning ScreenUpdating off does not speed up the execution. VB still wants to update all the references and do the calculations.

Closing the second file before pasting does not change the processing time.

I fixed my other problem with Subscript out of range... stupid mistake.

The interesting thing is that if i open the first file in one Excel app and try to open the 2nd file manually in the same excel window, it takes ages to load (same as with opening with VB script). If i open both separately in 2 Excel apps, it is much quicker.

Is there a way to open an Excel file in a separate Excel app from a different Excel app and reference between the two Excel's? i.e. instead of opening a new workbook, start a new Excel and open the workbook in that Excel window and reference back to the original?

XL2000

Thanks for your help,

Ryan