copy excel sheets with table references

Question

I have an Excel 2007 document with multiple sheets, let's call it source.xls. I'd like to copy some of the sheets into all Excel documents within a folder, say C:\some_folder.

I figured how to loop over a directory:

Dim file As String
file = dir("C:\some_folder\*.xlsx")
Do While file <> ""

   Rem do_stuff

   file = dir()
Loop

And how to copy sheets between workbooks:

For Each ws in ActiveWorkbook.Worksheets
    Dim wb as Workbook
    Set wb = Workbook.Open(file)
    ws.Copy , wb.sheets(w.sheets.Count)
    wb.Close SaveChanges:=True
Next ws

So far so good.

Now one of the sheets contains a table with external data from an SQL Server. Copying it works well.

Another sheet references data in that table as Table_MYSERVER_MYDB[[row][col]]. When I copy it, the references are automatically turned into source.xls!Table_MYSERVER_MYDB[[row][col]]

UPDATE: I just tried to reference the data in the table by sheet and cell, e.g. =Other_Sheet!A1. Still the same problem, the reference magically turns into =[source.xls]Other_Sheet!A1.

UPDATE 2: The next try was to access the cells in the other sheet with =INDIRECT("Other_Sheet!"&CELL("address")), but that seems to trigger a bug in Excel 2007. All cells will show the same value. Try it for yourself :)

I'd like the sheets in the target document to reference the table in the same workbook. How would I do that?

I'm open for other solutions than VBA too

Answer 1

I just figured it out myself:

My last desperate attempt was using Search&Replace over all formulas to remove [source.xls].

That's when a workmate suggested using:

wb.ChangeLink Name:=source.xls NewName:=wb.Name Type:=xlExcelLinks

Exactly what I was looking for!