Finding and killing all Access queries in an Excel spreadsheet
 

I've just switched from one Access application to another as the data
source for an Excel spreadsheet, as a result I'm having to swt up all
the links all over.

Inserting the new links isn't a problem, but I can't seem to get rid of
all the old ones. Although I'm pretty sure I've gotten rid of all the
references to the old database I still get error messages about the
path (to the old database) no longer being valid, followed by a login
screen etc.

Is there a way to find and kill all the links so I can start again?

These links were set up with data import external data new database
query. The links option under the edit menu in Excel is greyed out.

Travis

Finding and killing all Access queries in an Excel spreadsheet
 

Hi travis,

Try this small bit of code. It will find all data queries and list them.
Optionally, you can specify the old ODBC path to delete old queries.

Sub ListDataQueries()
Dim sht As Worksheet
Dim qtbl As QueryTable
Dim qName, qSheet, qAdress, qSource As String
Dim connOld, success As String

' set this to your old database to delete connections
' to that database, or just ignore
' to list the current querytables
connOld = "ODBC;DBQ=C:\Old Path\olddb.mdb"
success = "Still Active"

For Each sht In ActiveWorkbook.Worksheets
For Each qtbl In sht.QueryTables
qName = qtbl.Name
qSheet = qtbl.Destination.Worksheet.Name
qAdress = qtbl.Destination.Address
qSource = qtbl.Connection
If Left(qSource, Len(connOld)) = connOld Then
qtbl.Delete
success = "Deleted"
End If
MsgBox qName & " - " _
& qSheet & "!" _
& qAdress & " " _
& success
Next qtbl
Next sht
End Sub

Ed Ferrero
http://www.edferrero.com

I've just switched from one Access application to another as the data
source for an Excel spreadsheet, as a result I'm having to swt up all
the links all over.

Inserting the new links isn't a problem, but I can't seem to get rid of
all the old ones. Although I'm pretty sure I've gotten rid of all the
references to the old database I still get error messages about the
path (to the old database) no longer being valid, followed by a login
screen etc.

Is there a way to find and kill all the links so I can start again?

These links were set up with data import external data new database
query. The links option under the edit menu in Excel is greyed out.

Travis