Automatic CSV refresh freezes PC - how to fix this?

dikken20

Board Regular
Joined
Feb 25, 2009
Messages
84
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?
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
1,063
Re: HELP! Automatic CSV refresh freezes PC - how to fix this?

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

dikken20

Board Regular
Joined
Feb 25, 2009
Messages
84
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"
 

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
1,063
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?
 

dikken20

Board Regular
Joined
Feb 25, 2009
Messages
84
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:
 

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
1,063
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
 

dikken20

Board Regular
Joined
Feb 25, 2009
Messages
84
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.
 

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
1,063
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
 

dikken20

Board Regular
Joined
Feb 25, 2009
Messages
84
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
 

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
1,063
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,098,875
Messages
5,465,220
Members
406,419
Latest member
ldp124

This Week's Hot Topics

Top