How to get current information regarding a fresh query refresh?

H

Herbert Chan

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").ODBCConnection.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").ODBCConnection.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
 
H

Herbert Chan

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 said:
Make sure the query completes before calculating the MIN.
--
Gary''s Student - gsnu2007k


Herbert Chan said:
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").ODBCConnection.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").ODBCConnection.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
Click to expand...
Click to expand...
 
G

Gary Keramidas

take a look here
http://www.ozgrid.com/forum/showthread.php?t=50961


--


Gary


Herbert Chan said:
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 said:
Make sure the query completes before calculating the MIN.
--
Gary''s Student - gsnu2007k


Herbert Chan said:
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").ODBCConnection.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").ODBCConnection.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
Click to expand...
Click to expand...
Click to expand...
 
H

Herbert Chan

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

Any other workaround please?

Herbert

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


--


Gary


Herbert Chan said:
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 said:
Make sure the query completes before calculating the MIN.
--
Gary''s Student - gsnu2007k


:

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").ODBCConnection.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").ODBCConnection.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
Click to expand...
Click to expand...
Click to expand...
 
G

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 said:
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 said:
Make sure the query completes before calculating the MIN.
--
Gary''s Student - gsnu2007k


Herbert Chan said:
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").ODBCConnection.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").ODBCConnection.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
Click to expand...
Click to expand...
Click to expand...
 
H

Herbert Chan

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 said:
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 said:
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 said:
Make sure the query completes before calculating the MIN.
--
Gary''s Student - gsnu2007k


:

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").ODBCConnection.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").ODBCConnection.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
Click to expand...
Click to expand...
Click to expand...
 
G

Gary''s Student

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


Herbert Chan said:
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 said:
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 said:
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" <[email protected]> ???gco?l¢Do¡Ps?D:[email protected]...

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


:

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").ODBCConnection.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").ODBCConnection.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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to Activate a Macro 6
How to obtain the row count of a QueryTable from a Web query 4
How to refresh a pivot table that uses changing source data 1
Get Max & Min from a chart series 1
How to get the context information of the embedded Excel 1
How to refresh a Scenario PivotTable Report? 9
How to create a min-max bar chart in excel 2007 ? 5
HELP: Web Query does not seem to get the data 1

Top