I developed some code in an Excel Workbook. It all worked fine. I did "Save
as" - and selected "add-in." I'm using Excel 2002. In another sheet - I try
to select this add-in from Tools/Addins. I get errors when my code tries to
reference any sheet in the original workbook. For example a line like this:

Dim xs as Excel.Worksheet
Set xs = Application.Workbooks("MyCodeWorkBook.xls")

This line causes an error - subscript out of range in the add-in but works
from my "code sheet."

It is like the "add-in" does not see it's own workbook - when running as a
add-in. Any advice appreciated.

Steve

A workbook is made up of many worksheets. So when you say that a worksheet is a workbook
then you ask for trouble.

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"S. Daum" wrote in message
...

Dim xs as Excel.Worksheet
Set xs = Application.Workbooks("MyCodeWorkBook.xls")

Steve,
further to Neils comment I do not think you can access worksheets in an
add-in - they do not logically exist

ijb

--
Remove nospam from my e-mail address to talk direct

Not MCSD just experienced

"Neil" wrote in message
...
Steve,

Only guessing here but I think that addins have an extension of .xla
not
.xls.

Neil


"S. Daum" wrote in message
...
I developed some code in an Excel Workbook. It all worked fine. I did
"Save
as" - and selected "add-in." I'm using Excel 2002. In another sheet - I
try
to select this add-in from Tools/Addins. I get errors when my code tries
to
reference any sheet in the original workbook. For example a line like
this:

Dim xs as Excel.Worksheet
Set xs = Application.Workbooks("MyCodeWorkBook.xls")

This line causes an error - subscript out of range in the add-in but
works
from my "code sheet."

It is like the "add-in" does not see it's own workbook - when running as
a
add-in. Any advice appreciated.

Steve

Thanks for that, as I was replying it occured to me that there was no real
reason for them not to exist. I stand corrected.

--
Remove nospam from my e-mail address to talk direct

Not MCSD just experienced

"Gord Dibben" wrote in message
...
ijb

Worksheets DO exist in an Add-in. You CAN refer to them and move data to
and
from. You just can't see them unless you change the ThisWorkbook
Properties
IsAddin from True to False.

Sub copythings()
ThisWorkbook.Sheets("Sheet2").Activate
Range(Range("A1"), Range("A1").End(xlDown)).Copy _
Destination:=ThisWorkbook.Sheets("Sheet1").Range(" A1")
End Sub

Gord Dibben Excel MVP - XL97 SR2 & XL2002

On Tue, 29 Jul 2003 22:42:31 +0000 (UTC), "ijb"

wrote:

Steve,
further to Neils comment I do not think you can access worksheets in an
add-in - they do not logically exist

ijb

This has been fixed. The problem was the file extension. Some code like
this, in the Workbook_Open event solves the problem:

If ThisWorkbook.IsAddin Then
g_sMyWbName = "example.xla"
Else
g_sMyWbName = "example.xls"
End If

then later...

Dim xwb as Excel.Workbook
Set xwb = Application.Workbooks(g_sMyWbName)

Thanks again...


"S. Daum" wrote in message
...
Thanks for all the replies. I am closer to a solution but not there yet.
In haste, the example I originally posted disguised/confused my real
problem. I am trying to get a reference to the "workbook" that IS my
"add-in." This workbook contains two "worksheets." Here is a more precise
code snippet:

Dim xwb as Excel.Workbook

Set xwb = Application.Workbooks("MyAddinWorkbook.xls")

The reply about an add-in having an .xla extension may be the answer? I am
in the process of trying this but I am temporarily "broken" for other
reasons.

As I mentioned this works when I have MyAddinWorkbook.xls open within
Excel
but fails when running as an add-in...

About the replay saying I could set ThisWorkbook.IsAddin = False - will
this
not then display my workbook as any other workbook? I would like to avoid
this if possible.

Thanks again for the replies.

Steve


"Harald Staff" wrote in message
...
A workbook is made up of many worksheets. So when you say that a
worksheet
is a workbook
then you ask for trouble.

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"S. Daum" wrote in message
...

Dim xs as Excel.Worksheet
Set xs = Application.Workbooks("MyCodeWorkBook.xls")