No editing allowed? Excel DSN editing!

• sqltung

  Hall of Fame

  Points: 3962

  More actions

  October 4, 2005 at 8:26 am

  #363817

  Anyone know how to edit an Excel DSN?

  I've inherited some Excel docs (available on a shared network drive) that may have DSN issues. We'd like to find out what DSNs are being accessed.

  I can't find where to edit the DSN info from Excel. Can anyone help?

  Thanks

• John Russell

  Grasshopper

  Points: 10

  More actions

  October 5, 2005 at 8:47 pm

  #595328

  Use the connection property of either a pivotcache or querytable... a little sub like this will list them all for you:

  Sub showconnections()

  Dim pc As PivotCache

  For Each pc In ActiveWorkbook.PivotCaches

      Debug.Print pc.Connection

  Next

  Dim ws As Worksheet

  Dim qt As QueryTable

  For Each ws In ActiveWorkbook.Worksheets

      For Each qt In ws.QueryTables

          Debug.Print qt.Connection

      Next qt

  Next ws

  End Sub

  Good luck!

  JR