Automatic CSV refresh freezes PC - how to fix this?

dikken20

Board Regular
Joined
Feb 25, 2009
Messages
130
Office Version
  1. 2010
Platform
  1. Windows
Hi,

In Sheet1 there's data from CSV file which refreshing every 1 minute.
Since there're 2000 rows and 20 columns the amount of data is pretty big and the Refresh freezes the PC for about 10 seconds.

It is crucial to use the refresh every 1 minute but have to fix the freezing part.

Can anyone offer any help or solution for this please?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Re: HELP! Automatic CSV refresh freezes PC - how to fix this?

How did you connect to the CSV and where is that CSV located?
 
Upvote 0
Re: HELP! Automatic CSV refresh freezes PC - how to fix this?

How did you connect to the CSV and where is that CSV located?

Import using DATA/From Text/ and chose the file from "C:\CSV_data"
The Refresh process defined per 1 minute from "connection properties"
 
Upvote 0
Re: HELP! Automatic CSV refresh freezes PC - how to fix this?

that amount of records and fields should be imported in a flash. Is your workbook by any chance a bit slow to recalculate?
 
Upvote 0
Re: HELP! Automatic CSV refresh freezes PC - how to fix this?

that amount of records and fields should be imported in a flash. Is your workbook by any chance a bit slow to recalculate?

Yes, it says at the status bar below"calculating..." and maybe this is what takes too long and not the refresh, I'm confused now :eek:
 
Upvote 0
Re: HELP! Automatic CSV refresh freezes PC - how to fix this?

It is probably the calculation which takes that long. Have you got an idea which formulas may be causing this? Look for many cells containing:
- VLOOKUPS
- SUMIFS
- COUNTIFS
- Other "*Ifs" functions
- Array formulas
pointing to the range where the CSV is imported into
 
Upvote 0
Re: HELP! Automatic CSV refresh freezes PC - how to fix this?

It is probably the calculation which takes that long. Have you got an idea which formulas may be causing this? Look for many cells containing:
- VLOOKUPS
- SUMIFS
- COUNTIFS
- Other "*Ifs" functions
- Array formulas
pointing to the range where the CSV is imported into

Good question!
Sheet1 - all raw data from the CSV file.
Sheet2 - is using (many) Vlookups and Large/Small formulas in order to get the data from Sheer1 and display only the relevant data.
Other than that, there're about more 10 sheets while each sheet uses many Vlookups and Large/Small formulas in order to calculate the data and show different results.

In other words, there're alot of Vlookups and Large/Small formulas indeed.
 
Upvote 0
Re: HELP! Automatic CSV refresh freezes PC - how to fix this?

Well there you go, time for redesign perhaps :)
Without your file, advising what to do is not easy
 
Upvote 0
Re: HELP! Automatic CSV refresh freezes PC - how to fix this?

Well there you go, time for redesign perhaps :)
Without your file, advising what to do is not easy

I don't mind redesign, just not sure a better way :rolleyes:
I would send you the file, but I'm not sure how to upload it here? echh I feel so n00b
 
Upvote 0
Re: HELP! Automatic CSV refresh freezes PC - how to fix this?

I don't think mrExcel allows file uploads.Visit my homepage (click on the left on my name and choose visit homepage). At the bottom of the screen you can find my email address. Please refer to this web page in your email.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top