Linked to .xls and now go to .xlsx

[AtLarge]

I "had" tables that were links to .xls files. Now everyone has upgraded to Office 2010 so I need my linked files to point to the same place and file name but with the .xlsx extension not the old .xls so how can I do this without recreating everyone one by hand?

Linked Table Manager shows the complete path but I cannot figure out how to just make that minor change to the file name. Any suggestions? TIA.

 

[gemma-the-husky]

it isn't quite a minor change

one way is to drop the current connection, and reconnect to the new xlsx file.

easy when you are used to it, but not quite trivial.

 

[Galaxiom]

Make a backup of your database.
Copy this function into a Standard Module and Run it.

It will change ALL the links from .xls files to .xlsx with the same name in the same folder as the originals.

It doesn't test if the xlsx files exist.
It assumes the xlsx files are Excel 2007 (version 12.0)

Private Function XlsToXlsx()
 
Dim db As DAO.Database
Dim tdf As TableDef
Dim strConnect As String
 
    Set db = CurrentDb
 
    For Each tdf In db.TableDefs
 
        With tdf
            strConnect = .Connect
 
            If Right(strConnect, 4) = ".xls" Then
                strConnect = Replace(strConnect, "Excel 8.0", "Excel 12.0 Xml")
                strConnect = Replace(strConnect, "IMEX=2", "IMEX=2;ACCDB=YES") & "x"
                .Connect = strConnect
                .RefreshLink
            End If
 
        End With
 
    Next
 
    db.TableDefs.Refresh
 
    Set db = Nothing
 
End Function

 

[electronicdesigner]

hello
i have problem when import xlsx file into access 2010 that same name but it has linked field with another table
.

best regard