Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save as Template | Excel Discussion (Misc queries) | |||
Save as Template | New Users to Excel | |||
When you hit Save on a template, how can you save as worksheet? | Excel Worksheet Functions | |||
From Template Save As Help | New Users to Excel | |||
Save data in template from a form. Please Help! | Excel Programming |