auto Refresh MS Query SQL Server

[Souris]

I have a MS Query to retrieve data from SQL server database through ODBC
Connection

My user needs to right click to refresh data every time.

Is it possible to write some code to refresh the MS Query when open the
spreadsheet?

Also, May I pass parameter from spreadsheet?

Any information is great appreciated,

[K Dales]

Yes, both those abilities are built into the querytable in Excel so it is not
hard to set it up: Place the cursor on any cell in your querytable results
list, then right-click and select "Data Range Properties." Make sure save
query definition and save password (if your database has a password) are
checked off and then (about halfway down in the dialog box) check off the box
that says "Refresh data on file open." If you do this you can also choose to
"Remove external data from worksheet before saving" which minimizes the file
size but also means that the query data is not saved and must be refreshed
next time the file is open to be seen - if there were any problem accessing
the database when the file opened the list would be blank!

To pass a parameter from the spreadsheet, go into MSQuery and set up your
criteria with the parameter in square braces, e.g. [Name]='K Dales'. Then
close Query and return the data to Excel. It will ask you to supply the
value for [Name]; you can supply a sample value or even just leave that blank
for now. Then, when the query results are displayed, go back to a cell
within the query and right-click again; this time choose "Parameters..." The
dialog will list all parameters you asked for (e.g. Name, in the example I
gave) and then will ask how you want to supply the parameter; choose the 3rd
choice, "Get the value from the following cell:" and specify the worksheet
cell you want to use for the parameter (or, alternately, you can prompt the
user at the time the query runs - 1st choice in the parameters dialog).

--
- K Dales


"Souris" wrote:

> I have a MS Query to retrieve data from SQL server database through ODBC
> Connection
>
> My user needs to right click to refresh data every time.
>
> Is it possible to write some code to refresh the MS Query when open the
> spreadsheet?
>
> Also, May I pass parameter from spreadsheet?
>
> Any information is great appreciated,
>
>

[Souris]

Thanks for your information,

I got refresh one.

About pass parameter, my stored procedures are not in my select list when I
use MS Query to access my SQL server.

I tried to use MS Access to access SQL server stored procedure.

My Excel spreadsheet to access a query to SQL server query.

My MS Access query must have paramter like
EXEC MySP @MyParam = 'Name'

My parameter celll must match the parameter in the access query.
It makes me need to go to change paramter all the time.

Can you please let me know where I did wrong?
Thanks again millions,



"K Dales" wrote:

> Yes, both those abilities are built into the querytable in Excel so it is not
> hard to set it up: Place the cursor on any cell in your querytable results
> list, then right-click and select "Data Range Properties." Make sure save
> query definition and save password (if your database has a password) are
> checked off and then (about halfway down in the dialog box) check off the box
> that says "Refresh data on file open." If you do this you can also choose to
> "Remove external data from worksheet before saving" which minimizes the file
> size but also means that the query data is not saved and must be refreshed
> next time the file is open to be seen - if there were any problem accessing
> the database when the file opened the list would be blank!
>
> To pass a parameter from the spreadsheet, go into MSQuery and set up your
> criteria with the parameter in square braces, e.g. [Name]='K Dales'. Then
> close Query and return the data to Excel. It will ask you to supply the
> value for [Name]; you can supply a sample value or even just leave that blank
> for now. Then, when the query results are displayed, go back to a cell
> within the query and right-click again; this time choose "Parameters..." The
> dialog will list all parameters you asked for (e.g. Name, in the example I
> gave) and then will ask how you want to supply the parameter; choose the 3rd
> choice, "Get the value from the following cell:" and specify the worksheet
> cell you want to use for the parameter (or, alternately, you can prompt the
> user at the time the query runs - 1st choice in the parameters dialog).
>
> --
> - K Dales
>
>
> "Souris" wrote:
>
> > I have a MS Query to retrieve data from SQL server database through ODBC
> > Connection
> >
> > My user needs to right click to refresh data every time.
> >
> > Is it possible to write some code to refresh the MS Query when open the
> > spreadsheet?
> >
> > Also, May I pass parameter from spreadsheet?
> >
> > Any information is great appreciated,
> >
> >

[Souris]

Thanks for the indformation,
I got it works.

Thanks millions,

"Souris" wrote:

> Thanks for your information,
>
> I got refresh one.
>
> About pass parameter, my stored procedures are not in my select list when I
> use MS Query to access my SQL server.
>
> I tried to use MS Access to access SQL server stored procedure.
>
> My Excel spreadsheet to access a query to SQL server query.
>
> My MS Access query must have paramter like
> EXEC MySP @MyParam = 'Name'
>
> My parameter celll must match the parameter in the access query.
> It makes me need to go to change paramter all the time.
>
> Can you please let me know where I did wrong?
> Thanks again millions,
>
>
>
> "K Dales" wrote:
>
> > Yes, both those abilities are built into the querytable in Excel so it is not
> > hard to set it up: Place the cursor on any cell in your querytable results
> > list, then right-click and select "Data Range Properties." Make sure save
> > query definition and save password (if your database has a password) are
> > checked off and then (about halfway down in the dialog box) check off the box
> > that says "Refresh data on file open." If you do this you can also choose to
> > "Remove external data from worksheet before saving" which minimizes the file
> > size but also means that the query data is not saved and must be refreshed
> > next time the file is open to be seen - if there were any problem accessing
> > the database when the file opened the list would be blank!
> >
> > To pass a parameter from the spreadsheet, go into MSQuery and set up your
> > criteria with the parameter in square braces, e.g. [Name]='K Dales'. Then
> > close Query and return the data to Excel. It will ask you to supply the
> > value for [Name]; you can supply a sample value or even just leave that blank
> > for now. Then, when the query results are displayed, go back to a cell
> > within the query and right-click again; this time choose "Parameters..." The
> > dialog will list all parameters you asked for (e.g. Name, in the example I
> > gave) and then will ask how you want to supply the parameter; choose the 3rd
> > choice, "Get the value from the following cell:" and specify the worksheet
> > cell you want to use for the parameter (or, alternately, you can prompt the
> > user at the time the query runs - 1st choice in the parameters dialog).
> >
> > --
> > - K Dales
> >
> >
> > "Souris" wrote:
> >
> > > I have a MS Query to retrieve data from SQL server database through ODBC
> > > Connection
> > >
> > > My user needs to right click to refresh data every time.
> > >
> > > Is it possible to write some code to refresh the MS Query when open the
> > > spreadsheet?
> > >
> > > Also, May I pass parameter from spreadsheet?
> > >
> > > Any information is great appreciated,
> > >
> > >