The Excel help menu lists this set of instructions, to link a range in one
workbook to a range on a sheet in a different workbook:

Create a link between cells in different workbooks

1. Open both the workbook that will contain the link (called the
destination
workbook), and the workbook that contains the data you want to link to
(called the source workbook).
2. In the destination workbook, click Save .
3. Select a cell or cells you want to link from.
4. If you are creating a new formula, type = (an equal sign).
5. If you are entering the link elsewhere in the formula, type the operator
or function that you want to precede the link.
6. On the Window menu, click the name of the source workbook, and then
click the worksheet that contains the cells you want to link to.
7. Select the cells you want to link to.
8. Complete the formula. When you finish entering the formula, press
ENTER.

I need to automate this process in VBA code, since the size of the range
potentially changes each time it's opened.

A source spreadsheet is output from SAP, and I need to link only certain
columns from it into a new spreadsheet. When the report is run each week, the
number of items in the storage location can change, resulting in a different
number of rows in the spreadsheet each time.
I know how to use the UsedRange property to find out how many rows there
are in the source, but need some help setting up the link to the destination
spreadsheet. Every time I try it, I get the "text" of the formula, not the
value from the other spreadsheet ("=[ZUPG32000273.xls]Sheet1!R2C3:R103C3"
shows up in the cell)

Thanks!