Changing Query references - Please

[Snowshoeken]

Anyone know how to change a saved query reference so that I might be able to move saved queries to alternate file locations. As it stands, if I move my saved queries I am no longer able to refresh the data that they retrieve as Excel cannot find the queries. I want to be able to e-mail my my data and have so that the queries update the data for the person I send it to.

Thank you,
Snowshoeken

 

[DonkeyOte]

Are you saying that you save the queries as "files" somewhere on your PC? If so, can you not simply get external data and choose the query file? (New Datasource - Queries tab - and then browse for the query).

If it's the database you can no longer find then go into the query and view the table definition - you should from there be able to redirect it to the "new" ODBC.

Sorry if this is of no use - but let us know what it is you're after exactly.

 

[DonkeyOte]

Just re read it - if you want other people to be able to update the data - they will need the same ODBC Drivers set up on their PCs also.

 

[Snowshoeken]

I am saving the queries in a folder. I have set the refresh properties to refresh every minute. But if I try and move the folder which contains the saved queries, then I get an error which basically tells me that it can't find the queries any longer. I can't find anything that tells the workbook where to look for the saved queries.

 

[Snowshoeken]

The queries build/update tables that are created using data from another worksheet within the same workbook. The data will change based on inputs from another sheet from within the same workbook. The table that are created are used by some VLOOKUPS that return info based on the user input. The end result is a caluculation of margin based on the inputs of the user. It might sound confusing, but from what I have seen on this site, it would be considered very basic, just very involved.

Regards,
Snowshoeken

 

[Ian Mac]

Hi,

You could set the data source on the fly by using the 'On Open' event of the workbooks you want,
Here is the code from the help file (I typed in DSN in the answer wizard):

DataSource Property
See Also Applies To Example Specifics
Returns or sets a String that represents the name of the attached data source. Read/write.

expression.DataSource

expression Required. An expression that returns one of the objects in the Applies To list.

Example
The following example sets the name of the data source if the name is blank.

Sub SetAndReturnDataSourceName()
Dim appOffice As OfficeDataSourceObject

Set appOffice = Application.OfficeDataSourceObject
With appOffice
.Open bstrConnect:="DRIVER=SQL Server;SERVER=ServerName;" & _
"UID=user;PWD=;DATABASE=Northwind", bstrTable:="Employees"

If .DataSource = "" Then
.DataSource = "Northwind"
MsgBox .DataSource
End If
End With
End Sub

If you have all of the information (you should be able to get this from actually setting up the data source in the first place.)

I should then set this up on opening the file.
This is untested but it should do the trick.
I'll have a play and post back.

Hope this helps.