Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open multiple workbook then combine into single workbook butdifferent sheets
Hi!
The task now is to Open multiple workbook then combine into single workbook but different sheets. Pls help how to do in vba? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open multiple workbook then combine into single workbook but different sheets
Use a macro - run the code below, and select the files of interest in the dialog.
HTH, Bernie MS Excel MVP Sub MergeUserSelectedFiles() Dim FileArray As Variant Dim myB As Workbook Dim i As Integer FileArray = Application.GetOpenFilename(MultiSelect:=True) If IsArray(FileArray) Then For i = LBound(FileArray) To UBound(FileArray) Set myB = Workbooks.Open(FileArray(i)) myB.Worksheets.Copy befo=ThisWorkbook.Worksheets(1) myB.Close False Next i Else: MsgBox "You clicked cancel" End If End Sub "geniusideas" wrote in message ... Hi! The task now is to Open multiple workbook then combine into single workbook but different sheets. Pls help how to do in vba? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open multiple workbook then combine into single workbook butdifferent sheets
On Nov 24, 10:34*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: Use a macro - run the code below, and select the files of interest in the dialog. HTH, Bernie MS Excel MVP Sub MergeUserSelectedFiles() Dim FileArray As Variant Dim myB As Workbook Dim i As Integer FileArray = Application.GetOpenFilename(MultiSelect:=True) If IsArray(FileArray) Then * *For i = LBound(FileArray) To UBound(FileArray) * * * Set myB = Workbooks.Open(FileArray(i)) * * * myB.Worksheets.Copy befo=ThisWorkbook.Worksheets(1) * * * myB.Close False * *Next i Else: * *MsgBox "You clicked cancel" End If End Sub "geniusideas" wrote in message ... Hi! The task now is to Open multiple workbook then combine into single workbook but different sheets. Pls help how to do in vba? Thanks Thanks bernie.. It's work. if I want to combined into new workbook how to do because currently if I run it will combined into current workbook. Pls help .Thank |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open multiple workbook then combine into single workbook but different sheets
See code below.
HTH, Bernie MS Excel MVP Sub MergeUserSelectedFiles() Dim FileArray As Variant Dim myB As Workbook Dim myNB As Workbook Dim i As Integer Set myNB = Workbooks.Add FileArray = Application.GetOpenFilename(MultiSelect:=True) If IsArray(FileArray) Then For i = LBound(FileArray) To UBound(FileArray) Set myB = Workbooks.Open(FileArray(i)) myB.Worksheets.Copy befo=myNB.Worksheets(1) myB.Close False Next i Else: MsgBox "You clicked cancel" End If End Sub "geniusideas" wrote in message ... On Nov 24, 10:34 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Use a macro - run the code below, and select the files of interest in the dialog. HTH, Bernie MS Excel MVP Sub MergeUserSelectedFiles() Dim FileArray As Variant Dim myB As Workbook Dim i As Integer FileArray = Application.GetOpenFilename(MultiSelect:=True) If IsArray(FileArray) Then For i = LBound(FileArray) To UBound(FileArray) Set myB = Workbooks.Open(FileArray(i)) myB.Worksheets.Copy befo=ThisWorkbook.Worksheets(1) myB.Close False Next i Else: MsgBox "You clicked cancel" End If End Sub "geniusideas" wrote in message ... Hi! The task now is to Open multiple workbook then combine into single workbook but different sheets. Pls help how to do in vba? Thanks Thanks bernie.. It's work. if I want to combined into new workbook how to do because currently if I run it will combined into current workbook. Pls help .Thank |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open multiple workbook then combine into single workbook butdifferent sheets
On Thursday, November 26, 2009 9:47:14 AM UTC+10, Bernie Deitrick wrote:
See code below. HTH, Bernie MS Excel MVP Sub MergeUserSelectedFiles() Dim FileArray As Variant Dim myB As Workbook Dim myNB As Workbook Dim i As Integer Set myNB = Workbooks.Add FileArray = Application.GetOpenFilename(MultiSelect:=True) If IsArray(FileArray) Then For i = LBound(FileArray) To UBound(FileArray) Set myB = Workbooks.Open(FileArray(i)) myB.Worksheets.Copy befo=myNB.Worksheets(1) myB.Close False Next i Else: MsgBox "You clicked cancel" End If End Sub News from June 2012: Bernie's code looked like it would do what I needed, and it does, but only up to a specific point. I have a folder containing 119 single sheet workbooks, all have the identical format, and they all have a sheet name of "pn_[unique number]". I need to combine all of those worksheets into a single workbook and maintain their tab names. Bernie's code above did that, but after it adds the 62nd worksheet to the new mega-workbook it falls over with a window that says; "Excel cannot complete this task with available resources. Choose less data or close other applications." Closing this box then allows a VB window to pop-up that says; Run-time error '1004': Method of 'Copy' of object 'Sheets' failed. Can anyone advise me of what these mean? (Is there a maximum number of wsheets allowed?) and ideally, a fix? Am using Excel03, but could run in 07 if that'd fix it. cheers David T |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open multiple workbook then combine into single workbook butdifferent sheets
On Friday, June 29, 2012 10:58:12 AM UTC+10, David T wrote:
On Thursday, November 26, 2009 9:47:14 AM UTC+10, Bernie Deitrick wrote: See code below. HTH, Bernie MS Excel MVP Sub MergeUserSelectedFiles() Dim FileArray As Variant Dim myB As Workbook Dim myNB As Workbook Dim i As Integer Set myNB = Workbooks.Add FileArray = Application.GetOpenFilename(MultiSelect:=True) If IsArray(FileArray) Then For i = LBound(FileArray) To UBound(FileArray) Set myB = Workbooks.Open(FileArray(i)) myB.Worksheets.Copy befo=myNB.Worksheets(1) myB.Close False Next i Else: MsgBox "You clicked cancel" End If End Sub News from June 2012: Bernie's code looked like it would do what I needed, and it does, but only up to a specific point. I have a folder containing 119 single sheet workbooks, all have the identical format, and they all have a sheet name of "pn_[unique number]". I need to combine all of those worksheets into a single workbook and maintain their tab names. Bernie's code above did that, but after it adds the 62nd worksheet to the new mega-workbook it falls over with a window that says; "Excel cannot complete this task with available resources. Choose less data or close other applications." Closing this box then allows a VB window to pop-up that says; Run-time error '1004': Method of 'Copy' of object 'Sheets' failed. Can anyone advise me of what these mean? (Is there a maximum number of wsheets allowed?) Further reading reveals that it's a memory problem. (They're big sheets) Bugger. and ideally, a fix? Am using Excel03, but could run in 07 if that'd fix it. cheers David T |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open multiple workbook then combine into single workbook butdifferent sheets
sounds like you need a database rather than spreadsheets
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open multiple workbook then combine into single workbook butdifferent sheets
On Friday, June 29, 2012 6:52:48 PM UTC+10, Ggalla1779 wrote:
sounds like you need a database rather than spreadsheets That's the correct sound... but it's required in Excel. cheers |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open multiple workbook then combine into single workbook but different sheets
After serious thinking David T wrote :
On Friday, June 29, 2012 6:52:48 PM UTC+10, Ggalla1779 wrote: sounds like you need a database rather than spreadsheets That's the correct sound... but it's required in Excel. cheers Have you tried reading the files via ADODB and writing the data to a newly inserted sheet for each file? This would obviate having to open the workbooks so the only tax on resources will be the newly added sheets. (This assumes the same variable to store the recordset is reused for each file) -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open multiple workbook then combine into single workbook butdifferent sheets
On Tuesday, July 3, 2012 2:46:28 PM UTC+10, GS wrote:
After serious thinking David T wrote : On Friday, June 29, 2012 6:52:48 PM UTC+10, Ggalla1779 wrote: sounds like you need a database rather than spreadsheets That's the correct sound... but it's required in Excel. cheers Have you tried reading the files via ADODB and writing the data to a newly inserted sheet for each file? This would obviate having to open the workbooks so the only tax on resources will be the newly added sheets. (This assumes the same variable to store the recordset is reused for each file) I'll get back to you on that after discovering what ADODB is. cheers |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open multiple workbook then combine into single workbook but different sheets
David T wrote :
On Tuesday, July 3, 2012 2:46:28 PM UTC+10, GS wrote: After serious thinking David T wrote : On Friday, June 29, 2012 6:52:48 PM UTC+10, Ggalla1779 wrote: sounds like you need a database rather than spreadsheets That's the correct sound... but it's required in Excel. cheers Have you tried reading the files via ADODB and writing the data to a newly inserted sheet for each file? This would obviate having to open the workbooks so the only tax on resources will be the newly added sheets. (This assumes the same variable to store the recordset is reused for each file) I'll get back to you on that after discovering what ADODB is. cheers Here's a good place to start... http://www.appspro.com/conference/Da...rogramming.zip -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine worksheets in multiple workbook in one workbook with a macro | Excel Discussion (Misc queries) | |||
how can I combine multiple worksheets into a single workbook? | Excel Discussion (Misc queries) | |||
Multiple workbook data imported into single workbook | Excel Worksheet Functions | |||
Creatn multiple sheets in single workbook | Excel Programming | |||
continuous page numbering multiple sheets of a single workbook? | Excel Worksheet Functions |