Copy Excel worksheets with table links

Question

I have an Excel 2007 document with multiple sheets, call him source.xls. I would like to copy some sheets into all Excel documents inside a folder, say C:\some_folder.

I figured out how to iterate 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 books:

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 SQL Server. Copying works well.

Another sheet refers to the data in this table as Table_MYSERVER_MYDB[[row][col]]. When I copy it, links automatically turn intosource.xls!Table_MYSERVER_MYDB[[row][col]]

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

2: =INDIRECT("Other_Sheet!"&CELL("address")), , , Excel 2007. . :)

, . ?

, VBA.

Answer 1

:

Search & Replace [source.xls].

, :

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

, !

Answer 2

:

Sub CopyFormula()
Dim R1,R2 As Range

Set R1 = Workbooks("wb2.xls").Sheets(1).Range("A1")
Set R2 = Workbooks("wb1.xls").Sheets(1).Range("A1")

R1.Formula = R2.Formula
End Sub