Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.links
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|