2

Im wondering if it's possible to reference an excel sheet from another work book without making a copy of that sheet?

The situation : I have some very large worksheets filled with various data, but i don't want to keep a copy of them open in my workbooks because while each workbook uses the same data source, they're slightly different.

I have a vba routine that takes this data and creates input files for other codes, vba expects this data to be available on the defined sheet names.

Is it possible to make either excel or vba to know that when i request worksheet("Example_1") it instead knows that i mean example_1 from a different workbook?

Thanks

Harley Walsh
  • 43
  • 1
  • 1
  • 3

2 Answers2

8

Yes, it is possible.

You need to add those lines to your code:

Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet

Set wkb = Excel.Workbooks("name_of_workbook.xlsx")
Set wks = wkb.Worksheets("Example_1")

Now, every time you want to refer to a range from this other workbook, you need to add wks. before, i.e.:

'Printing value in cell.
wks.Range("A1") = "x"

'Selecting range.
call wks.Range(wks.Cells(1,1), wks.Cells(2,2)).Select
mielk
  • 3,890
  • 12
  • 19
  • Currently my code is declaring the worksheets at the start i.e Data_sheet1 = worksheets("Data_sheet_2014") Then later in the code i can say data_sheet1.range(....) Would it be enough to just update the code here, or will i have to update every time it references one of these sheets? – Harley Walsh Jul 29 '15 at 10:35
  • Yes, it would be enough if you change declaration. Once you change it, variable `data_sheet1` will refer to this worksheet from other workbook. – mielk Jul 29 '15 at 10:43
2
=SUM('C:\test\[test.xlsx]sheet_name'!A1:A25)

is an example of a formula which references sheet sheet_name in workbook C:\test\text.xlsx.

Note that when the other workbook is opened, the formula automatically changes to 

=SUM([test.xlsx]sheet_name!A1:A25)

and then when it is closed, the formula will change back.

Mark Butler
  • 895
  • 1
  • 5
  • 18