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

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

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

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

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

You can try this as an experiment. Say some process (human or query) is
going to refresh a table of numbers from B9 thru B20 and we want to "pause"
the macro until the refresh occurs.

1. Before the query, we set B20 to "junk"
2. Before using the query results, we make sure "junk" is no longer there

Sub herb()
Range("B20").Value = "junk"
'
' do the query here
'
While Range("B20").Value = "junk"
DoEvents
Wend
MsgBox ("the new value is: " & Range("B20").Value)
End Sub

The DoEvents loop "shares" focus between the macro and the worksheet. It is
the manual equivalent to a change event.

The reason I said "experiment" is that I know that a user can change B20 in
this "shared" state. I don't know if the query can do the same thing.

Let us know your results.
--
Gary''s Student - gsnu200804


"Herbert Chan" wrote:

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

Hello,

The following still doesn't work.

But I've just checked the property of the ODBCConnection through a dialog
box. It turns out it can be set not to perform the query in background.
After this has been set, I now get what I want.

Thank you for your help.

Herbert

"Gary''s Student" ...
You can try this as an experiment. Say some process (human or query) is
going to refresh a table of numbers from B9 thru B20 and we want to
"pause"
the macro until the refresh occurs.

1. Before the query, we set B20 to "junk"
2. Before using the query results, we make sure "junk" is no longer there

Sub herb()
Range("B20").Value = "junk"
'
' do the query here
'
While Range("B20").Value = "junk"
DoEvents
Wend
MsgBox ("the new value is: " & Range("B20").Value)
End Sub

The DoEvents loop "shares" focus between the macro and the worksheet. It
is
the manual equivalent to a change event.

The reason I said "experiment" is that I know that a user can change B20
in
this "shared" state. I don't know if the query can do the same thing.

Let us know your results.
--
Gary''s Student - gsnu200804


"Herbert Chan" wrote:

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

Very good.
--
Gary''s Student - gsnu200804


"Herbert Chan" wrote:

Hello,

The following still doesn't work.

But I've just checked the property of the ODBCConnection through a dialog
box. It turns out it can be set not to perform the query in background.
After this has been set, I now get what I want.

Thank you for your help.

Herbert

"Gary''s Student" ...
You can try this as an experiment. Say some process (human or query) is
going to refresh a table of numbers from B9 thru B20 and we want to
"pause"
the macro until the refresh occurs.

1. Before the query, we set B20 to "junk"
2. Before using the query results, we make sure "junk" is no longer there

Sub herb()
Range("B20").Value = "junk"
'
' do the query here
'
While Range("B20").Value = "junk"
DoEvents
Wend
MsgBox ("the new value is: " & Range("B20").Value)
End Sub

The DoEvents loop "shares" focus between the macro and the worksheet. It
is
the manual equivalent to a change event.

The reason I said "experiment" is that I know that a user can change B20
in
this "shared" state. I don't know if the query can do the same thing.

Let us know your results.
--
Gary''s Student - gsnu200804


"Herbert Chan" wrote:

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