Save lookup data in template (.xlt)
 

Is it possible to store lookup data in a .xlt template file and access the
sheet from with the VBA code stored in the .xlt file itself?

I created a .xlt template with one worksheet and a toolbar with one button.
This template contains data along with some macros that reference that data.
Unfortunately, the macro ends with an error when run from a workbook without
the referenced worksheet. Is it possible to reference the worksheet
contained within the .xlt template?

Save lookup data in template (.xlt)
 

When you store the code/data in a .xlt, then every workbook that's based on this
template will contain the code/data.

Have you thought of separating your code/data and putting it into an addin.
Then the user could load the addin when they need the macros.

It might make it easier for you if you ever have to update the code or data--who
knows how many workbooks could have created using the .xlt template workbook.

But in either case (.xlt or .xla), you could refer to a worksheet named "Sheet1"
in the workbook that contains the code with something like:

msgbox thisworkbook.worksheets("sheet1").range("b99").val ue

ThisWorkbook is the workbook that owns the code.

Bill James wrote:

Is it possible to store lookup data in a .xlt template file and access the
sheet from with the VBA code stored in the .xlt file itself?

I created a .xlt template with one worksheet and a toolbar with one button.
This template contains data along with some macros that reference that data.
Unfortunately, the macro ends with an error when run from a workbook without
the referenced worksheet. Is it possible to reference the worksheet
contained within the .xlt template?

--

Dave Peterson

Save lookup data in template (.xlt)
 

Thanks Dave for the tip. I ended up referencing the workbook directly
(requiring it to be opened) and referencing the data from VBA using:

Application.WorksheetFunction.VLookup(myIndex,
Workbooks("Royalties.xls").Sheets("Royalties").Use dRange, 4, False)

The data needs to be modifiable by the user, so I thought it would be best
to keep it in a workbook. The user will need to open the workbook to run
code contained within the workbook.

I was trying to avoid writing a full add-in, but the night is still young :)
....

"Dave Peterson" wrote in message
...
When you store the code/data in a .xlt, then every workbook that's based
on this
template will contain the code/data.

Have you thought of separating your code/data and putting it into an
addin.
Then the user could load the addin when they need the macros.

It might make it easier for you if you ever have to update the code or
data--who
knows how many workbooks could have created using the .xlt template
workbook.

But in either case (.xlt or .xla), you could refer to a worksheet named
"Sheet1"
in the workbook that contains the code with something like:

msgbox thisworkbook.worksheets("sheet1").range("b99").val ue

ThisWorkbook is the workbook that owns the code.

Bill James wrote:

Is it possible to store lookup data in a .xlt template file and access
the
sheet from with the VBA code stored in the .xlt file itself?

I created a .xlt template with one worksheet and a toolbar with one
button.
This template contains data along with some macros that reference that
data.
Unfortunately, the macro ends with an error when run from a workbook
without
the referenced worksheet. Is it possible to reference the worksheet
contained within the .xlt template?

--

Dave Peterson