Hi,
yes it is.
click somewhere inside the queryrange.
click the edit query icon.
on the last sheet of the query wizard click
view data or edit query in microsoft query then click
finish
when microsoft query comes up click ViewSQL
here delete out the old file path and enter the new file
path.
The data source has been changed.
:)
-----Original Message-----
Hi,
I use an extern excel-database. This database changes
every week, but the
lay-out stays the same. So only the figures change. I
have made al lot of
query's in excel who are connected to the extern
database. The query's work
very wel but now I want to change the source of my extern
database. My
question, Is it possible to change the source of an
extern database without
changing the whole query.

Greetings Mark from the Netherlands
.

Mark

You can write a macro to do this. See here for how to change the database
location programmatically

http://www.dicks-clicks.com/excel/Ex...htm#ChangeConn

and see here for using GetOpenFilename to have the user select a database

http://www.dicks-blog.com/excel/2004...nfilename.html

Post back if you need more specifics.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


Mark van der Sluis wrote:
Thanks for the answer

You are right about the SQL, I found it out myself. Only the problem is,
how can I explain this to a noob in excel, because I'm building these
query's for my boss, he has to use them a lot, but isn't able to
understand this. So actually I want him to choose a file to work with,
everytime he uses the query's. Maybe it's possible to programm something
that if he uses the query's he has to select an external database, that
would be the best. Is this possible???


" wrote:

Hi,
yes it is.
click somewhere inside the queryrange.
click the edit query icon.
on the last sheet of the query wizard click
view data or edit query in microsoft query then click
finish
when microsoft query comes up click ViewSQL
here delete out the old file path and enter the new file
path.
The data source has been changed.
:)
-----Original Message-----
Hi,
I use an extern excel-database. This database changes
every week, but the
lay-out stays the same. So only the figures change. I
have made al lot of
query's in excel who are connected to the extern
database. The query's work
very wel but now I want to change the source of my extern
database. My
question, Is it possible to change the source of an
extern database without
changing the whole query.

Greetings Mark from the Netherlands
.

Thanks Dick for you're help. My only problem is, and maybe this is a very
simple and stupid question. Where do I put the sub-programm Get open File in
my SQL texture (existing query). (SELECT `Blad1$`.`Cost ctr`,
Sum(`Blad1$`.`Val#in rep#cur#`) AS 'Totale kosten per Costctr'
FROM `Y:\MIJN DOCUMENTEN\Excel bestand te exporteren`.`Blad1$` `Blad1$`
WHERE (`Blad1$`.`D/C`='D') OR (`Blad1$`.`D/C`='C')
GROUP BY `Blad1$`.`Cost ctr`)

.. Because if I put it in, MSquery tells me that he can't display the table
and he can't find the table.



"Dick Kusleika" wrote:

Mark

You can write a macro to do this. See here for how to change the database
location programmatically

http://www.dicks-clicks.com/excel/Ex...htm#ChangeConn

and see here for using GetOpenFilename to have the user select a database

http://www.dicks-blog.com/excel/2004...nfilename.html

Post back if you need more specifics.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


Mark van der Sluis wrote:
Thanks for the answer

You are right about the SQL, I found it out myself. Only the problem is,
how can I explain this to a noob in excel, because I'm building these
query's for my boss, he has to use them a lot, but isn't able to
understand this. So actually I want him to choose a file to work with,
everytime he uses the query's. Maybe it's possible to programm something
that if he uses the query's he has to select an external database, that
would be the best. Is this possible???


" wrote:

Hi,
yes it is.
click somewhere inside the queryrange.
click the edit query icon.
on the last sheet of the query wizard click
view data or edit query in microsoft query then click
finish
when microsoft query comes up click ViewSQL
here delete out the old file path and enter the new file
path.
The data source has been changed.
:)
-----Original Message-----
Hi,
I use an extern excel-database. This database changes
every week, but the
lay-out stays the same. So only the figures change. I
have made al lot of
query's in excel who are connected to the extern
database. The query's work
very wel but now I want to change the source of my extern
database. My
question, Is it possible to change the source of an
extern database without
changing the whole query.

Greetings Mark from the Netherlands
.

Mark

It doesn't go in the SQL statement. Your boss will run a macro that will
look something like this

Dim sFname as String
Dim qtTable as QueryTable

sFname = Application.GetOpenFilename(...)

For Each qtTable in Sheet1.QueryTables
qtTable.Connection = Replace(qtTable.Connection, OldFile, sFname)
qtTable.CommandText = Replace(...)
qtTable.Refresh False
Next qtTable

That's pseudocode obviously. You get the new file name and replace the old
with the new in the Connection string and, if necessary, the CommandText.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Mark van der Sluis" wrote in
message ...
Thanks Dick for you're help. My only problem is, and maybe this is a very
simple and stupid question. Where do I put the sub-programm Get open File
in
my SQL texture (existing query). (SELECT `Blad1$`.`Cost ctr`,
Sum(`Blad1$`.`Val#in rep#cur#`) AS 'Totale kosten per Costctr'
FROM `Y:\MIJN DOCUMENTEN\Excel bestand te exporteren`.`Blad1$` `Blad1$`
WHERE (`Blad1$`.`D/C`='D') OR (`Blad1$`.`D/C`='C')
GROUP BY `Blad1$`.`Cost ctr`)

. Because if I put it in, MSquery tells me that he can't display the table
and he can't find the table.



"Dick Kusleika" wrote:

Mark

You can write a macro to do this. See here for how to change the
database
location programmatically

http://www.dicks-clicks.com/excel/Ex...htm#ChangeConn

and see here for using GetOpenFilename to have the user select a
database

http://www.dicks-blog.com/excel/2004...nfilename.html

Post back if you need more specifics.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


Mark van der Sluis wrote:
Thanks for the answer

You are right about the SQL, I found it out myself. Only the problem
is,
how can I explain this to a noob in excel, because I'm building these
query's for my boss, he has to use them a lot, but isn't able to
understand this. So actually I want him to choose a file to work with,
everytime he uses the query's. Maybe it's possible to programm
something
that if he uses the query's he has to select an external database,
that
would be the best. Is this possible???


" wrote:

Hi,
yes it is.
click somewhere inside the queryrange.
click the edit query icon.
on the last sheet of the query wizard click
view data or edit query in microsoft query then click
finish
when microsoft query comes up click ViewSQL
here delete out the old file path and enter the new file
path.
The data source has been changed.
:)
-----Original Message-----
Hi,
I use an extern excel-database. This database changes
every week, but the
lay-out stays the same. So only the figures change. I
have made al lot of
query's in excel who are connected to the extern
database. The query's work
very wel but now I want to change the source of my extern
database. My
question, Is it possible to change the source of an
extern database without
changing the whole query.

Greetings Mark from the Netherlands
.