Automatic CSV refresh freezes PC - how to fix this?

[dikken20]

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?

 

[jkpieterse]

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]

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]

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]

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

 

[jkpieterse]

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]

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]

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]

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
I would send you the file, but I'm not sure how to upload it here? echh I feel so n00b

 

[jkpieterse]

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.