locked
Links to excel spreadsheets RRS feed

  • Question

  • I have linked to several spreadsheets and want to remove all of the links at one time. I can remove them one at a time if I use each name by using the following:


    For Each tdf In db.TableDefs
        If tdf.Name = "MARS BILL" Then
            db.TableDefs.Delete tdf.Name
        End If
    Next tdf

    is there a better way to delete all links at once?

    Wednesday, February 16, 2011 8:09 PM

Answers

  • Try:

    Dim db As DAO.Database
    Dim lngLoop As Long

      Set db = CurrentDb
      For lngLoop = (db.TableDefs.Count - 1) To 0 Step -1
        If LCase(Left$(db.TableDefs(lngLoop).Connect, 5)) = "excel" Then
          db.TableDefs.Delete db.TableDefs(lngLoop).Name
        End If
      Next lngLoop

    Note that you need to loop backwards since the table numbers get reset with each delete.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    • Marked as answer by Bruce Song Tuesday, February 22, 2011 7:40 AM
    Wednesday, February 16, 2011 8:31 PM
  • "harrimi2" wrote in message
    news:fb2604d1-fff6-493a-a172-2ae91f76ca8d@communitybridge.codeplex.com...
    >I have linked to several spreadsheets and want to remove all of the links
    >at one time. I can remove them one at a time if I use each name by using
    >the following:
    >
    >
    > For Each tdf In db.TableDefs
    >    If tdf.Name = "MARS BILL" Then
    >        db.TableDefs.Delete tdf.Name
    >    End If
    > Next tdf
    >
    > is there a better way to delete all links at once?
    >
     
    I'm not entirely sure what you have in mind.
     
    (1) If you have a few specific linked tables -- whose names you know -- that
    you want to delete, then you can delete them directly, either using
    DoCmd.DeleteObject or CurrentDb.TableDefs.Delete:
    '
       DoCmd.DeleteObject acTable, "Foo"
       DoCmd.DeleteObject acTable, "Bar"
    '
    or
    '
       With CurrentDb.TableDefs
           .Delete "Foo"
           .Delete "Bar"
       End With
    '
     
    (2) If you want to delete all linked tables, without knowing their names (or
    what they are linked to), you could do this:
    '
       Dim I As Long
        With CurrentDb.TableDefs
           For I = (.Count - 1) to 0 Step - 1
               If Len(.Item(I).Connect) > 0 Then
                   .Delete .Item(I).Name
               End If
           Next I
       End With
    '
    Note that we loop from the last item in the collection to the first.  That's
    to avoid problems caused by the collection being renumbered when an item is
    deleted from it.
     
    (3) If you want to delete all linked *Excel* tables, while allowing other
    linked tables to remain, you could do this:
    '
       Dim I As Long
        With CurrentDb.TableDefs
           For I = (.Count - 1) to 0 Step - 1
               If InStr(.Item(I).Connect, "Excel ") > 0 Then
                   .Delete .Item(I).Name
               End If
           Next I
       End With
    '
    You may need to examine the .Connect properties of several of the linked
    Excel files to see exactly what the InStr() function should be looking for.
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Marked as answer by Bruce Song Tuesday, February 22, 2011 7:40 AM
    Wednesday, February 16, 2011 8:35 PM

All replies

  • Try:

    Dim db As DAO.Database
    Dim lngLoop As Long

      Set db = CurrentDb
      For lngLoop = (db.TableDefs.Count - 1) To 0 Step -1
        If LCase(Left$(db.TableDefs(lngLoop).Connect, 5)) = "excel" Then
          db.TableDefs.Delete db.TableDefs(lngLoop).Name
        End If
      Next lngLoop

    Note that you need to loop backwards since the table numbers get reset with each delete.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    • Marked as answer by Bruce Song Tuesday, February 22, 2011 7:40 AM
    Wednesday, February 16, 2011 8:31 PM
  • "harrimi2" wrote in message
    news:fb2604d1-fff6-493a-a172-2ae91f76ca8d@communitybridge.codeplex.com...
    >I have linked to several spreadsheets and want to remove all of the links
    >at one time. I can remove them one at a time if I use each name by using
    >the following:
    >
    >
    > For Each tdf In db.TableDefs
    >    If tdf.Name = "MARS BILL" Then
    >        db.TableDefs.Delete tdf.Name
    >    End If
    > Next tdf
    >
    > is there a better way to delete all links at once?
    >
     
    I'm not entirely sure what you have in mind.
     
    (1) If you have a few specific linked tables -- whose names you know -- that
    you want to delete, then you can delete them directly, either using
    DoCmd.DeleteObject or CurrentDb.TableDefs.Delete:
    '
       DoCmd.DeleteObject acTable, "Foo"
       DoCmd.DeleteObject acTable, "Bar"
    '
    or
    '
       With CurrentDb.TableDefs
           .Delete "Foo"
           .Delete "Bar"
       End With
    '
     
    (2) If you want to delete all linked tables, without knowing their names (or
    what they are linked to), you could do this:
    '
       Dim I As Long
        With CurrentDb.TableDefs
           For I = (.Count - 1) to 0 Step - 1
               If Len(.Item(I).Connect) > 0 Then
                   .Delete .Item(I).Name
               End If
           Next I
       End With
    '
    Note that we loop from the last item in the collection to the first.  That's
    to avoid problems caused by the collection being renumbered when an item is
    deleted from it.
     
    (3) If you want to delete all linked *Excel* tables, while allowing other
    linked tables to remain, you could do this:
    '
       Dim I As Long
        With CurrentDb.TableDefs
           For I = (.Count - 1) to 0 Step - 1
               If InStr(.Item(I).Connect, "Excel ") > 0 Then
                   .Delete .Item(I).Name
               End If
           Next I
       End With
    '
    You may need to examine the .Connect properties of several of the linked
    Excel files to see exactly what the InStr() function should be looking for.
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Marked as answer by Bruce Song Tuesday, February 22, 2011 7:40 AM
    Wednesday, February 16, 2011 8:35 PM
  • the last one, (3) worked the best for what I want to do. Thank you.
    Thanks to all for your kind assitance.

    Thursday, February 17, 2011 10:45 AM