Creating a chart while data is being written to file!!!

[Guest]

Alrite,

I have a slight problem whereby I dont have a clue where to start.

I have an external source creating a .csv file and I would like to produce a
graph in Excel while the data is being written to the file. All it is, is
Column A being plotted against Column B.

Does any1 know if this can be achieved with VBA???

Thanks a lot

Nick

 

[Stephen Bullen]

Hi Nick,

I have a slight problem whereby I dont have a clue where to start.

I have an external source creating a .csv file and I would like to produce a
graph in Excel while the data is being written to the file. All it is, is
Column A being plotted against Column B.

Does any1 know if this can be achieved with VBA???

Sure, and probably without VBA!

Start by getting the data into the workbook, without locking the file. The
easiest way to do that is with Data > Import External Data > Import Data.
Change the 'Files of Type' to All files, browse to the csv file and OK through
the dialogs. When done, you should end up with both columns in the sheet. If
you want, you can right-click inside the data area, choose 'Data Range
Properties', give it a meaningful name (e.g. qryMyData) and tell it to refresh
every minute or so.

Now create a chart based on the data range. If you'd like the chart to
automatically expand as the new data is added, you can create two named ranges
to link the chart to the data, using Insert > Name > Define:

Name: ChtA
Refers To: =OFFSET(qryMyData,0,0,,1)

Name: ChtB
Refers To: =OFFSET(qryMyData,0,1,,1)

(Note that the qryMyData defined name is automatically created in the first
step, as the name you gave to the query range).

then use these names in the =SERIES() formula for the chart:

=SERIES("My Data",Book1.xls!ChtA,Book1.xls!ChtB,1)


Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Guest]

Hi Stephen,

I have tried it but I didnt know where to put the series formula.

OK, I imported the as you said. I have two columns of data. So I highlighted
the data, right-clicked on the highlighted data and choose 'Data Range
Properties', I gave it the name (qryMyData) and told it to refresh every
minute. I also de-selected the option 'Prompt for a file name on refresh'.

I then highlighted the data in Column A and went to Insert > Name > Define:.
I then put ChtA and =OFFSET(qryMyData,0,0,,1) in the correct boxes. I then
repeated this for Column B. I then highlighted the data and then clicked on
the chart wizard. Now this is where I get stuck. Where do I put the
=SERIES("My Data",Book1.xls!ChtA,Book1.xls!ChtB,1) formula??? If I select the
chart type and click next I then get the data ranges tab and the series tab.
Within the series tab I have the following input boxes: Name, Values, and
Category (X) axis labels. Where does the code go??? Also do I apply it for
both Series???

Sorry about this.

Thanks a lot

Nick

 

[Guest]

Hi Stephen,

I have tried it but I didnt know where to put the series formula.

OK, I imported the data as above. I have two columns of data. So I
highlighted the data, right-clicked on the highlighted data and choose 'Data
Range Properties', I gave it the name (qryMyData) and told it to refresh
every minute. I also de-selected the option 'Prompt for a file name on
refresh'.

I then highlighted the data in Column A and went to Insert > Name > Define:.
I then put ChtA and =OFFSET(qryMyData,0,0,,1) in the correct boxes. I then
repeated this for Column B. I then highlighted the data and then clicked on
the chart wizard. Now this is where I get stuck. Where do I put the
=SERIES("My Data",Book1.xls!ChtA,Book1.xls!ChtB,1) formula??? If I select the
chart type and click next I then get the data ranges tab and the series tab.
Within the series tab I have the following input boxes: Name, Values, and
Category (X) axis labels. Where does the code go??? Also do I apply it for
both Series???

Sorry about this.

Thanks a lot

Nick

 

[Stephen Bullen]

Hi Nick,

I then highlighted the data and then clicked on
the chart wizard. Now this is where I get stuck. Where do I put the
=SERIES("My Data",Book1.xls!ChtA,Book1.xls!ChtB,1) formula???

Start the chart wizard and go through it to create the chart normally,
based on the selected data. When done, select the series in the chart.
The =SERIES() formula should be shown in the formula bar. Edit it to
use the defined names instead of the ranges.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Guest]

Sorry about this Stephen. OK then, I have created a scatter graph to plot the
columns against each other. I click on the points until they were all
highlighted. I then put that formula in.

Problem is though, all of the points are shown. It does not show you the
first point and then after a minute or so, show you the next point and so on.
Is it supposed to???

Also, is there any way of getting Excel to refresh in less than a minute as
I tried to input 0.25 and it wouldnt have it because it only accepts integers


Thanks for your help

Nick

 

[Stephen Bullen]

Hi Nick,

OK then, I have created a scatter graph to plot the
columns against each other. I click on the points until they were all
highlighted. I then put that formula in.
Cool!

Problem is though, all of the points are shown. It does not show you the
first point and then after a minute or so, show you the next point and so on.
Is it supposed to???

No, it's supposed to show all the points all the time, and automatically include
any new points added to the data file, refreshing every minute. I thought that
was what you wanted?

Also, is there any way of getting Excel to refresh in less than a minute as
I tried to input 0.25 and it wouldnt have it because it only accepts integers

Only by using VBA, I'm afraid, setting an OnTime routine to keep refreshing
quicker than the one-minute interval. Searching google for OnTime (and maybe
"flashing text") will probably return some code you can use.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Stephen Bullen]

Hi Nick,

Sorry about this Stephen. OK then, I have created a scatter graph to plot the
columns against each other. I click on the points until they were all
highlighted. I then put that formula in.
Great!

Problem is though, all of the points are shown. It does not show you the
first point and then after a minute or so, show you the next point and so on.
Is it supposed to???

No it's not. It's supposed to plot all the points, updating every minute to
automatically include new data being written to the file.

Also, is there any way of getting Excel to refresh in less than a minute as
I tried to input 0.25 and it wouldnt have it because it only accepts integers

Not without VBA. You would need a routine to repeatedly refresh the query table
(ActiveSheet.QueryTables(1).Refresh), which can be done either in a continuous
loop (with maybe Application.Wait in it to pause things), or by chaining
Application.OnTime routines (search google for 'flashing cells' for some
examples of the latter).

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Guest]

No, what I wanted was to have each point adding to the chart as the file was
bein written to. Or have the data in the file and update the chart with the
next point after say 0.3 seconds.

Sorry about the confusion.

Do you know of any way of doing this???

Thanks again

Nick

 

[Stephen Bullen]

Hi Nick,

No, what I wanted was to have each point adding to the chart as the file was
being written to.

Unfortunately, we don't have a notification message for when files are being
written to, so we can't do that directly

Or have the data in the file and update the chart with the
next point after say 0.3 seconds.

That we can do. Set calculation to automation and leave this running in Excel:

Sub KeepUpdated()

Do
ActiveSheet.QueryTables(1).Refresh
DoEvents
'Use Ctrl+Break to stop it
Loop

End Sub

However, that will probably hog the processor, so badly affecting whatever
application is creating the file. We could add a Sleep call in there to pause
things:

Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

Sub KeepUpdated()

Do
ActiveSheet.QueryTables(1).Refresh
DoEvents
Sleep 300
'Use Ctrl+Break to stop it
Loop

End Sub


Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Guest]

Im sorry about this Stephen!!!

You said: Set calculation to automation.
As I dont know Excel that much, where do I do this???

Im really sorry and I do really appreciate your help

Thanks once again

Nick

 

[Stephen Bullen]

Hi Nick,

You said: Set calculation to automation.
As I dont know Excel that much, where do I do this???

Tools > Options > Calculation

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Guest]

Ur going to hate me, sorry

OK then, where do I put the VBA code??? Im know Im probably getting to you
now, but I have never inputted VBA code into Excel. Also is there any
specific place the code should go???

Thanks

Nick

P.S. Here is the code that you gave me:


Sub KeepUpdated()

Do
ActiveSheet.QueryTables(1).Refresh
DoEvents
'Use Ctrl+Break to stop it
Loop

End Sub

However, that will probably hog the processor, so badly affecting whatever
application is creating the file. We could add a Sleep call in there to pause
things:

Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

Sub KeepUpdated()

Do
ActiveSheet.QueryTables(1).Refresh
DoEvents
Sleep 300
'Use Ctrl+Break to stop it
Loop

End Sub

 

[Stephen Bullen]

Hi Nick,

OK then, where do I put the VBA code??? Im know Im probably getting to you
now, but I have never inputted VBA code into Excel. Also is there any
specific place the code should go???

OK, what we're doing is not a good way to start learning VBA. If we continue
down this path, you're going to get very confused very quickly. I strongly
suggest you stop what you're doing, go to your local bookstore (or
amazon.com), buy an introductory book about Excel VBA, read it and try the
examples. Once you've done that, you'll know where the code I gave you goes,
how to run it, how to stop it, etc <g>.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk