Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import data from a particular cell in different excel sheets | Excel Discussion (Misc queries) | |||
Import Txt file over several excel sheets | Excel Discussion (Misc queries) | |||
Import table into Excel with lines sliced into separate sheets by UID | Excel Programming | |||
import and search datas from different Excel Sheets | Excel Programming | |||
Import to excel Access database directly to a variable (not using sheets) | Excel Programming |