Chart Flickering

[ac3100]

I have a macro that pulls values from a text file into an excel spreadsheet.

As values are being added to the sheet, I have a line chart that changes to reflect the addition of those numbers.

I looks very cool!

The problem is that it flickers.

I have read about the screenUpdating = True/False to eliminate the flickering, but when I used it, the chart did not update until the very end.

That's not as cool!

Is there anyway that I can see the chart update without the flickering?

 

[tusharm]

Unfortunately, you cannot have it both ways. The chart can either update as you add/change data or it does not.

You could turn on screenupdating every so often and immediately turn it off.

Of course, the real question is why does it take time to import data from a text file into a XL worksheet? It should be a one step operation, shouldn't it?

 

[ac3100]

The text file is a sales report for inventory items.

each item has one row of information. I pull things like SKU#, description, Unit sales from 1 week ago - 12 weeks ago, retail price etc.

each text file has 200-5000 items.

I formatted excel to look identical to the sales report. The I added some cool graphs to provide more information that a text file can't give.

 

[ac3100]

I've seen this code before but I don't know if it could work in excel.


Private Declare Function LockWindowUpdate Lib _
"USER32" (ByVal hwndLock As Long) As Long

Option Explicit

Public Sub Form(pForm As Form)
LockWindowUpdate pForm.hWnd
End Sub

Private Sub Class_Initialize()
'check who called the class
End Sub

Private Sub Class_Terminate()
LockWindowUpdate False
End Sub


It says it stops flickering in VB but I don't understand it.

 

[tusharm]

Obviously, I'm missing something. Why do you "pull things?" Why not open the text file in XL? Turn on the macro recorder, open the text file, answer the questions posed by the Text Wizard, and turn off the macro recorder. XL will give you the necessary code. Now, depending on how your existing code works, slap on the necessary graphs, or copy the data from the newly opened file into the existing file with the charts. If I were in your shoes, I would have the charts set to use dynamic ranges (a la the Dynamic Charts page of my web site). Then, I'd simply update the data from the text file with a copy+paste+delete extraneous rows operation. The charts would update themselves automatically. Easy to program and machine-efficient.

The text file is a sales report for inventory items.

each item has one row of information. I pull things like SKU#, description, Unit sales from 1 week ago - 12 weeks ago, retail price etc.

each text file has 200-5000 items.

I formatted excel to look identical to the sales report. The I added some cool graphs to provide more information that a text file can't give.

 

[ac3100]

The text file is from a printed report. that means there is a header from every page that I don't need.

I put checks in place to make sure that I omit those rows.

Thats why I pull instead of going thrrough the delimeted wizard.