Changing Query references - Please

Snowshoeken

Active Member
Joined
Aug 8, 2002
Messages
306
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

Active Member
Joined
Aug 8, 2002
Messages
306
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

Active Member
Joined
Aug 8, 2002
Messages
306
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

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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.
 

Forum statistics

Threads
1,144,274
Messages
5,723,444
Members
422,497
Latest member
dougy99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top