event for Data>Refresh Data?

[Ian Elliott]

Thanks for any help.
I have a worksheet where the user changes the date in two cells. After
changing the date in the first cell, the user clicks Data>Refresh Data (or
clicks the Refresh data button), and then, typically with the mouse, selects
the second cell, and changes the data there, and hits enter (no need to hit
Refresh data for the second one). I would like to write some code where after
the user clicks Refresh Data, the second date cell automatically becomes the
selected cell (without the user having to select it with the mouse, or arrow
keys). So I can speed up this process, as it is done everyday. I know it
doesn't take a lot of time with the mouse, but a little bit of speed doesn't
hurt.
I figure this would be done best with some kind of event code, maybe using
events with the QueryTable object (when I record a macro of clicking refresh
Data, the code is: Selection.QueryTable.Refresh BackgroundQuery:=False).
Is this the right path to be heading? Just want to check before I try this.
Thanks again.

[Mike K]

Ian,
I would put this at the end of your data refresh macro:

Range("desired cell").Select

That should do it

Mike.

"Ian Elliott" wrote:

> Thanks for any help.
> I have a worksheet where the user changes the date in two cells. After
> changing the date in the first cell, the user clicks Data>Refresh Data (or
> clicks the Refresh data button), and then, typically with the mouse, selects
> the second cell, and changes the data there, and hits enter (no need to hit
> Refresh data for the second one). I would like to write some code where after
> the user clicks Refresh Data, the second date cell automatically becomes the
> selected cell (without the user having to select it with the mouse, or arrow
> keys). So I can speed up this process, as it is done everyday. I know it
> doesn't take a lot of time with the mouse, but a little bit of speed doesn't
> hurt.
> I figure this would be done best with some kind of event code, maybe using
> events with the QueryTable object (when I record a macro of clicking refresh
> Data, the code is: Selection.QueryTable.Refresh BackgroundQuery:=False).
> Is this the right path to be heading? Just want to check before I try this.
> Thanks again.
>

[Ian Elliott]

Thanks for the help Mike.
Sorry, I didn't mention that the data refresh is not by macro, but by SQL
query. There isn't any place to put code at. The way I tell it is by SQL
query, is where the cell that I select, and then click Data>Refresh Data, is
where if I do Data>Import External Data>Edit Query, it shows the SQL code.
But I figure if I put an onevent of table query (because I recorded a macro
where I clicked on Data>Refresh Data, and it uses
selection.querytable.refresh), I can put some code where to put the selected
cell. Any ideas?
Thanks again.


"Mike K" wrote:

> Ian,
> I would put this at the end of your data refresh macro:
>
> Range("desired cell").Select
>
> That should do it
>
> Mike.
>
> "Ian Elliott" wrote:
>
> > Thanks for any help.
> > I have a worksheet where the user changes the date in two cells. After
> > changing the date in the first cell, the user clicks Data>Refresh Data (or
> > clicks the Refresh data button), and then, typically with the mouse, selects
> > the second cell, and changes the data there, and hits enter (no need to hit
> > Refresh data for the second one). I would like to write some code where after
> > the user clicks Refresh Data, the second date cell automatically becomes the
> > selected cell (without the user having to select it with the mouse, or arrow
> > keys). So I can speed up this process, as it is done everyday. I know it
> > doesn't take a lot of time with the mouse, but a little bit of speed doesn't
> > hurt.
> > I figure this would be done best with some kind of event code, maybe using
> > events with the QueryTable object (when I record a macro of clicking refresh
> > Data, the code is: Selection.QueryTable.Refresh BackgroundQuery:=False).
> > Is this the right path to be heading? Just want to check before I try this.
> > Thanks again.
> >

[Mike K]

Ian,
Does your recorded macro refresh correctly?
If so, won't the range select in that macro execute?

Mike

"Ian Elliott" wrote:

> Thanks for the help Mike.
> Sorry, I didn't mention that the data refresh is not by macro, but by SQL
> query. There isn't any place to put code at. The way I tell it is by SQL
> query, is where the cell that I select, and then click Data>Refresh Data, is
> where if I do Data>Import External Data>Edit Query, it shows the SQL code.
> But I figure if I put an onevent of table query (because I recorded a macro
> where I clicked on Data>Refresh Data, and it uses
> selection.querytable.refresh), I can put some code where to put the selected
> cell. Any ideas?
> Thanks again.
>
>
> "Mike K" wrote:
>
> > Ian,
> > I would put this at the end of your data refresh macro:
> >
> > Range("desired cell").Select
> >
> > That should do it
> >
> > Mike.
> >
> > "Ian Elliott" wrote:
> >
> > > Thanks for any help.
> > > I have a worksheet where the user changes the date in two cells. After
> > > changing the date in the first cell, the user clicks Data>Refresh Data (or
> > > clicks the Refresh data button), and then, typically with the mouse, selects
> > > the second cell, and changes the data there, and hits enter (no need to hit
> > > Refresh data for the second one). I would like to write some code where after
> > > the user clicks Refresh Data, the second date cell automatically becomes the
> > > selected cell (without the user having to select it with the mouse, or arrow
> > > keys). So I can speed up this process, as it is done everyday. I know it
> > > doesn't take a lot of time with the mouse, but a little bit of speed doesn't
> > > hurt.
> > > I figure this would be done best with some kind of event code, maybe using
> > > events with the QueryTable object (when I record a macro of clicking refresh
> > > Data, the code is: Selection.QueryTable.Refresh BackgroundQuery:=False).
> > > Is this the right path to be heading? Just want to check before I try this.
> > > Thanks again.
> > >

[Ian Elliott]

Thanks Mike.
Actually, I don't even have a macro in there for refreshing data. The user
clicks on the exclamation mark button to refresh data (or Data>Refresh Data),
which doesn't go thru a macro, it uses SQL stuff to refresh the data.
I figure I could write a event macro that changes the selected cell whenever
the data is refreshed.
Any ideas?
Thanks-Ian


"Mike K" wrote:

> Ian,
> Does your recorded macro refresh correctly?
> If so, won't the range select in that macro execute?
>
> Mike
>
> "Ian Elliott" wrote:
>
> > Thanks for the help Mike.
> > Sorry, I didn't mention that the data refresh is not by macro, but by SQL
> > query. There isn't any place to put code at. The way I tell it is by SQL
> > query, is where the cell that I select, and then click Data>Refresh Data, is
> > where if I do Data>Import External Data>Edit Query, it shows the SQL code.
> > But I figure if I put an onevent of table query (because I recorded a macro
> > where I clicked on Data>Refresh Data, and it uses
> > selection.querytable.refresh), I can put some code where to put the selected
> > cell. Any ideas?
> > Thanks again.
> >
> >
> > "Mike K" wrote:
> >
> > > Ian,
> > > I would put this at the end of your data refresh macro:
> > >
> > > Range("desired cell").Select
> > >
> > > That should do it
> > >
> > > Mike.
> > >
> > > "Ian Elliott" wrote:
> > >
> > > > Thanks for any help.
> > > > I have a worksheet where the user changes the date in two cells. After
> > > > changing the date in the first cell, the user clicks Data>Refresh Data (or
> > > > clicks the Refresh data button), and then, typically with the mouse, selects
> > > > the second cell, and changes the data there, and hits enter (no need to hit
> > > > Refresh data for the second one). I would like to write some code where after
> > > > the user clicks Refresh Data, the second date cell automatically becomes the
> > > > selected cell (without the user having to select it with the mouse, or arrow
> > > > keys). So I can speed up this process, as it is done everyday. I know it
> > > > doesn't take a lot of time with the mouse, but a little bit of speed doesn't
> > > > hurt.
> > > > I figure this would be done best with some kind of event code, maybe using
> > > > events with the QueryTable object (when I record a macro of clicking refresh
> > > > Data, the code is: Selection.QueryTable.Refresh BackgroundQuery:=False).
> > > > Is this the right path to be heading? Just want to check before I try this.
> > > > Thanks again.
> > > >