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

[smileclick]

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, before:=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).

 

[Joel]

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]

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]

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

 

[Jay]

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.

 

[Norman Jones]

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
'<<=============

 

[Gord Dibben]

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

 

[Jay]

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.

 

[smileclick]

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.

----------------------------------------------------------------------------------------