The body of your macro looks fine to me, as a macro to update the links
in the active workbook (in fact I think I recognise some of the
code<g).

But it shouldn't be Workbook_Open.
No need to involve Access.
You can run the macro from Excel.
Tools / Macro / Run

But first, put it in a normal module in a workbook of your choosing.
Change it from
Private Sub Workbook_Open
to
Sub UpdateActiveWorkbookLinks()

And then add a procedure that will cycle through the workbooks you need
to update. It might look something like this:

Sub ProcessXLSFilesInDirectory()
Dim aFiles() As String, iFile As Integer
Dim stFile As String, vFile As Variant
Dim stDirectory As String

' first build an array of the files and then process them
' this is because you may upset the Dir function if you save a file

stDirectory = "D:\TEMP\" ' name of directory to look in
' use Dir function to find XLS files in Directory
stFile = Dir(stDirectory & "*.XLS")
If stFile = "" Then Exit Sub ' no files to process
Do While stFile < ""
' add to array of files
iFile = iFile + 1
' add one element to the array
ReDim Preserve aFiles(1 To iFile)
aFiles(iFile) = stFile
stFile = Dir() ' gets next file
Loop

' now process the files
For Each vFile In aFiles
Workbooks.Open stDirectory & vFile
UpdateActiveWorkbookLinks
Workbooks(vFile).Close saveChanges:=False
Next vFile

End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup