Stopping calculation when opening file

RyanD

New Member
Joined
Sep 21, 2002
Messages
10
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
Sep 21, 2002
Messages
10
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

New Member
Joined
Sep 6, 2002
Messages
21
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

New Member
Joined
Sep 21, 2002
Messages
10
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
 

Forum statistics

Threads
1,143,613
Messages
5,719,713
Members
422,242
Latest member
hishamkhatri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top