I've tried and it seems that odbcconnection does not support refresh
(false). I get an error.

Any other workaround please?

Herbert

"Gary Keramidas" <GKeramidasATmsn.com bl...
take a look here
http://www.ozgrid.com/forum/showthread.php?t=50961


--


Gary


"Herbert Chan" wrote in message
...
I've also thought about that but I have no idea how I can make sure that
the query is completed before the next code of calculation is done.

Anyone can give specific advice?

Herbert

"Gary''s Student" ...
Make sure the query completes before calculating the MIN.
--
Gary''s Student - gsnu2007k


"Herbert Chan" wrote:

Hello,

I've created a chart that is based on a msquery result. I use a
dropdown
box to choose what I want to show on the chart and the macro ends with
a
refresh of the msquery. I want to further adjust the minimum value of
the
x-axis of the chart based on the msquery result. My code is as
follows:

Sub FundChange()
Dim Rng As Range, rngD As Range, cht As Chart, rngScale As Range
Set Rng = ActiveWorkbook.Worksheets("MPFData").Range("K1")
Set rngD = ActiveWorkbook.Worksheets("MPFData").Range("M1")
Set rngScale = ActiveWorkbook.Worksheets("MPFData").Range("M3")
Set cht = ActiveWorkbook.Charts(1)
ActiveWorkbook.Connections("MPFChart").ODBCConnect ion.CommandText =
"SELECT
Max(MPFChart.Date) AS CloseDate, First(MPFChart." & Rng.Value & ") AS
[Open], Max(MPFChart." & Rng.Value & ") AS High, Min(MPFChart." &
Rng.Value
& ") AS Low, Last(MPFChart." & Rng.Value & ") AS [Close] FROM MPFChart
MPFChart WHERE MPFChart.Date #" & Format(rngD.Value, "m/d/yyyy") & "#
GROUP BY Format(Date, 'yyyy' & 'ww') ORDER BY Max(MPFChart.Date);"
ActiveWorkbook.Connections("MPFChart").ODBCConnect ion.Refresh
cht.Axes(xlValue).MinimumScale =
WorksheetFunction.RoundDown(WorksheetFunction.Min( ActiveWorkbook.Worksheets("MPFData").Range("D:D")) ,
0)
End Sub

As shown on the above last line of code, I want to make the
minimumscale to
be equal to the rounddown of the min value of column D. However, when
the
code executes, it ALWAYS uses the result of the previous msquery
refresh.
It is very baffling. How can I get the min value of the FRESH refresh
of
the msquery?

Herbert