How do you import sheets into an Excel XLA Add-In?
 

I'm trying to import worksheets from a spreadsheet file into an the running
XLA Add-In (the XLA holding the code for importing the spreadsheets).

I tried using:
Sheets(Array("Sheet1","Sheet2")).Copy After:=ThisWorkbook.Sheets(1)

But got a "Run-time error '1004': Copy Method of Worksheet Class failed" error

I tried using:
Sheets.Add Type:=Source_File_Name, befo=ThisWorkbook.Sheets(1)
But it will only add the sheets to the Active Workbook, not the XLA Workbook
(if there is no open workbook I get a "Method 'Sheets' of object '_Global'
failed" error).

How do you import sheets into an Excel XLA Add-In?
 

This seemed pretty simple. I save a workbook as xla (add-in file). Then
opened new workbook and used instruction below to open the xla file. It read
the add-in to the new workbook


Workbooks.Open Filename:= "C:\temp\Booka.xla"

"smileclick" wrote:

I'm trying to import worksheets from a spreadsheet file into an the running
XLA Add-In (the XLA holding the code for importing the spreadsheets).

I tried using:
Sheets(Array("Sheet1","Sheet2")).Copy After:=ThisWorkbook.Sheets(1)

But got a "Run-time error '1004': Copy Method of Worksheet Class failed" error

I tried using:
Sheets.Add Type:=Source_File_Name, befo=ThisWorkbook.Sheets(1)
But it will only add the sheets to the Active Workbook, not the XLA Workbook
(if there is no open workbook I get a "Method 'Sheets' of object '_Global'
failed" error).

How do you import sheets into an Excel XLA Add-In?
 

Thanks for the tip. However it doesn't cover my requirement, as the importing
of sheets needs to be automated:
Each time Excel opens the Add-In needs to import the sheets, as they contain
variables that change over time.

Is there a way of importing directly into a running Add-In (XLA file)?

"Joel" wrote:

This seemed pretty simple. I save a workbook as xla (add-in file). Then
opened new workbook and used instruction below to open the xla file. It read
the add-in to the new workbook


Workbooks.Open Filename:= "C:\temp\Booka.xla"

"smileclick" wrote:

I'm trying to import worksheets from a spreadsheet file into an the running
XLA Add-In (the XLA holding the code for importing the spreadsheets).

I tried using:
Sheets(Array("Sheet1","Sheet2")).Copy After:=ThisWorkbook.Sheets(1)

But got a "Run-time error '1004': Copy Method of Worksheet Class failed" error

I tried using:
Sheets.Add Type:=Source_File_Name, befo=ThisWorkbook.Sheets(1)
But it will only add the sheets to the Active Workbook, not the XLA Workbook
(if there is no open workbook I get a "Method 'Sheets' of object '_Global'
failed" error).

How do you import sheets into an Excel XLA Add-In?
 

You can put the open in a workbook_open function like this

Private Sub Workbook_Open()
Workbooks.Open Filename:="C:\temp\Booka.xla"
End Sub


"smileclick" wrote:

Thanks for the tip. However it doesn't cover my requirement, as the importing
of sheets needs to be automated:
Each time Excel opens the Add-In needs to import the sheets, as they contain
variables that change over time.

Is there a way of importing directly into a running Add-In (XLA file)?

"Joel" wrote:

This seemed pretty simple. I save a workbook as xla (add-in file). Then
opened new workbook and used instruction below to open the xla file. It read
the add-in to the new workbook


Workbooks.Open Filename:= "C:\temp\Booka.xla"

"smileclick" wrote:

I'm trying to import worksheets from a spreadsheet file into an the running
XLA Add-In (the XLA holding the code for importing the spreadsheets).

I tried using:
Sheets(Array("Sheet1","Sheet2")).Copy After:=ThisWorkbook.Sheets(1)

But got a "Run-time error '1004': Copy Method of Worksheet Class failed" error

I tried using:
Sheets.Add Type:=Source_File_Name, befo=ThisWorkbook.Sheets(1)
But it will only add the sheets to the Active Workbook, not the XLA Workbook
(if there is no open workbook I get a "Method 'Sheets' of object '_Global'
failed" error).

How do you import sheets into an Excel XLA Add-In?
 

Hi smileclick -

Joel's solutions work properly and open the addin file, but I'm interpreting
your original post differently.

If I understand your problem correctly, I don't think that it's possible to
import new worksheets into an addin (.xla). The action of making an addin
from a normal workbook converts the workbook into a static XLA file for the
primary purpose of making its VB code internally available to other workbooks
(without reference to the workbook containing the code). I believe the only
way to modify an addin XLA file is to modify the original workbook file that
was used to create it and save the modified workbook as a new addin XLA file.
Making an addin is like forging a horseshoe; once it's quenched and
tempered, it can't be changed.

Please correct me if you (or others) have discovered otherwise or my
interpretation is missing the mark.
--
Jay


"smileclick" wrote:

I'm trying to import worksheets from a spreadsheet file into an the running
XLA Add-In (the XLA holding the code for importing the spreadsheets).

I tried using:
Sheets(Array("Sheet1","Sheet2")).Copy After:=ThisWorkbook.Sheets(1)

But got a "Run-time error '1004': Copy Method of Worksheet Class failed" error

I tried using:
Sheets.Add Type:=Source_File_Name, befo=ThisWorkbook.Sheets(1)
But it will only add the sheets to the Active Workbook, not the XLA Workbook
(if there is no open workbook I get a "Method 'Sheets' of object '_Global'
failed" error).

How do you import sheets into an Excel XLA Add-In?
 

Hi Jay,

'--------------------
Joel's solutions work properly and open the addin file, but I'm interpreting
your original post differently.

If I understand your problem correctly, I don't think that it's possible to
import new worksheets into an addin (.xla). The action of making an addin
from a normal workbook converts the workbook into a static XLA file for the
primary purpose of making its VB code internally available to other
workbooks
(without reference to the workbook containing the code). I believe the only
way to modify an addin XLA file is to modify the original workbook file that
was used to create it and save the modified workbook as a new addin XLA
file.
Making an addin is like forging a horseshoe; once it's quenched and
tempered, it can't be changed.

Please correct me if you (or others) have discovered otherwise or my
interpretation is missing the mark.
'--------------------

'=============
Public Sub Demo()
Dim destWB As Workbook
Dim srcWB As Workbook

Set srcWB = ThisWorkbook
Set destWB = Workbooks("Pluto.xla")

With destWB
MsgBox .Sheets.Count
.IsAddin = False
srcWB.Sheets("Sheet1").Copy _
After:=.Sheets(.Sheets.Count)
.IsAddin = True
MsgBox .Sheets.Count
End With
End Sub
'<<=============


---
Regards,
Norman

How do you import sheets into an Excel XLA Add-In?
 

Additional info for Jay.

You do not need the original *.xls file that was the basis for the add-in.

In the VBE select the add-in then in Thisworkbook properties you can change
"IsAddin" to False.

Edit/add what you want then change back to True and save.


Gord Dibben MS Excel MVP

On Sun, 1 Apr 2007 20:12:48 +0100, "Norman Jones"
wrote:

I believe the only
way to modify an addin XLA file is to modify the original workbook file that
was used to create it and save the modified workbook as a new addin XLA
file.
Making an addin is like forging a horseshoe; once it's quenched and
tempered, it can't be changed.

Please correct me if you (or others) have discovered otherwise or my
interpretation is missing the mark.

How do you import sheets into an Excel XLA Add-In?
 

Norman and Gord -

Thanks a bunch for the information and technique for converting between
xla's and xls's. There you go 'smileclick'; Norman has produced the VBA
answer to your original question and Gord has produced the non-VBA approach.
Doesn't get any better.

If needed 'smileclick', you could add an Open statement and a Save statement
to Norman's procedure as follows (the '.Close' statement would be optional,
too). I've added them and archived his procedure for future use as follows:

'=============
Public Sub Demo()
Dim destWB As Workbook
Dim srcWB As Workbook

'Next statement assumes that xla is in same folder as ThisWorkbook;
'Modify path to suit.
Workbooks.Open Filename:=ThisWorkbook.Path & "\Pluto.xla"

Set srcWB = ThisWorkbook
Set destWB = Workbooks("Pluto.xla")

With destWB
'MsgBox .Sheets.Count
.IsAddin = False
srcWB.Sheets("Sheet1").Copy _
After:=.Sheets(.Sheets.Count)
.IsAddin = True
'MsgBox .Sheets.Count
.Save
'.Close '<--close or don't close to suit.
End With
End Sub
'<<=============

Thanks again, Norman and Gord.
--
Jay


"Gord Dibben" wrote:

Additional info for Jay.

You do not need the original *.xls file that was the basis for the add-in.

In the VBE select the add-in then in Thisworkbook properties you can change
"IsAddin" to False.

Edit/add what you want then change back to True and save.


Gord Dibben MS Excel MVP

On Sun, 1 Apr 2007 20:12:48 +0100, "Norman Jones"
wrote:

I believe the only
way to modify an addin XLA file is to modify the original workbook file that
was used to create it and save the modified workbook as a new addin XLA
file.
Making an addin is like forging a horseshoe; once it's quenched and
tempered, it can't be changed.

Please correct me if you (or others) have discovered otherwise or my
interpretation is missing the mark.

How do you import sheets into an Excel XLA Add-In?
 

Thanks everyone. I worked out that whilst you can't import sheets into an
XLA, you can paste data into spreadsheets contained within it (with the same
sheet name):

Private Sub ImportVariables()
fName = ThisWorkbook.Path + "\variables.xls"
Workbooks.Open Filename:=fName
For i = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(i).Select
SName = ActiveWorkbook.Sheets(i).Name
Cells.Cut ThisWorkbook.Sheets(SName).Cells
Next i
ActiveWorkbook.Close SaveChanges:=False
End Sub

' I used 'cut' rather than copy to preserve the links between the sheets in
the variable workbook.

----------------------------------------------------------------------------------------
"Jay" wrote:

Norman and Gord -

Thanks a bunch for the information and technique for converting between
xla's and xls's. There you go 'smileclick'; Norman has produced the VBA
answer to your original question and Gord has produced the non-VBA approach.
Doesn't get any better.

If needed 'smileclick', you could add an Open statement and a Save statement
to Norman's procedure as follows (the '.Close' statement would be optional,
too). I've added them and archived his procedure for future use as follows:

'=============
Public Sub Demo()
Dim destWB As Workbook
Dim srcWB As Workbook

'Next statement assumes that xla is in same folder as ThisWorkbook;
'Modify path to suit.
Workbooks.Open Filename:=ThisWorkbook.Path & "\Pluto.xla"

Set srcWB = ThisWorkbook
Set destWB = Workbooks("Pluto.xla")

With destWB
'MsgBox .Sheets.Count
.IsAddin = False
srcWB.Sheets("Sheet1").Copy _
After:=.Sheets(.Sheets.Count)
.IsAddin = True
'MsgBox .Sheets.Count
.Save
'.Close '<--close or don't close to suit.
End With
End Sub
'<<=============

Thanks again, Norman and Gord.
--
Jay


"Gord Dibben" wrote:

Additional info for Jay.

You do not need the original *.xls file that was the basis for the add-in.

In the VBE select the add-in then in Thisworkbook properties you can change
"IsAddin" to False.

Edit/add what you want then change back to True and save.


Gord Dibben MS Excel MVP

On Sun, 1 Apr 2007 20:12:48 +0100, "Norman Jones"
wrote:

I believe the only
way to modify an addin XLA file is to modify the original workbook file that
was used to create it and save the modified workbook as a new addin XLA
file.
Making an addin is like forging a horseshoe; once it's quenched and
tempered, it can't be changed.

Please correct me if you (or others) have discovered otherwise or my
interpretation is missing the mark.