How can I change the source of my extern source database in an ex.

[Guest]

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 View>SQL
here delete out the old file path and enter the new file
path.
The data source has been changed.

 

[Dick Kusleika]

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/ExternalData5.htm#ChangeConn

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

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

Post back if you need more specifics.

 

[Guest]

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]

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.