Excel Chart Automation Problem

[mjb2727]

I'm stumped with this one. I have code that is supposed to create a number of charts in an Excel workbook. It loops through from 1 to some number. It adds each chart and then applies formatting in a With block. The weird thing is that all of the charts look fine when it is done except for the first one. The first chart doesn't have its source data set properly. I can go in and set its source data manually and it works fine.

The even weirder thing is that if I step through the code one line at a time using F8, it works perfectly fine and the first chart is correct! What the heck?

Here's the code:


Dim c As Excel.Chart
For w = 1 To SetSize 'SetSize is an integer
Set c = xlf.Charts.Add 'xlf is an Excel workbook
With c
.ChartType = xlLine
.SetSourceData xlf.Sheets("PlotData" & w).Range("A1:B361&quot
.HasTitle = True
.ChartTitle.Text = "Plot of Skid Number vs. Time"
.HasLegend = True
.Legend.Position = xlLegendPositionBottom
.Axes(xlValue).MinimumScaleIsAuto = False
.Axes(xlValue).MaximumScaleIsAuto = False
.Axes(xlValue).MinimumScale = 0
.Axes(xlValue).MaximumScale = 120
.Axes(xlCategory).TickLabelSpacing = 1
.Axes(xlCategory).TickMarkSpacing = 1
.Axes(xlCategory).CategoryNames = xlf.Sheets("PlotData" & w).Range("C2:C361&quot
.Location xlLocationAsObject, "Tire " & w
End With
xlf.Sheets("Tire " & w).ChartObjects(1).Width = 300
xlf.Sheets("Tire " & w).ChartObjects(1).Height = 100
xlf.Sheets("Tire " & w).ChartObjects(1).Top = 600
xlf.Sheets("Tire " & w).ChartObjects(1).Left = 200
Next

 

[robk54]

I find that if it works when you step thru it, you just need a couple of "doevents" strategically placed - seems to slow it down enough to get the job done !

I have even resorted to
for x= 1 to 5000:doevents:next
although this can slow things down too much at times

Hope this helps


Rob