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