Open multiple workbook then combine into single workbook butdifferent sheets

  • Thread starter Thread starter geniusideas
  • Start date Start date
G

geniusideas

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
 
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 before:=ThisWorkbook.Worksheets(1)
myB.Close False
Next i
Else:
MsgBox "You clicked cancel"
End If
End Sub
 
Use a macro - run the code below, and select the files of interest in thedialog.

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 before:=ThisWorkbook.Worksheets(1)
      myB.Close False
   Next i
Else:
   MsgBox "You clicked cancel"
End If
End Sub
Click to expand...

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
 
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 before:=myNB.Worksheets(1)
myB.Close False
Next i
Else:
MsgBox "You clicked cancel"
End If
End Sub



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 before:=ThisWorkbook.Worksheets(1)
myB.Close False
Next i
Else:
MsgBox "You clicked cancel"
End If
End Sub
Click to expand...

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
 
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 before:=myNB.Worksheets(1)
myB.Close False
Next i
Else:
MsgBox "You clicked cancel"
End If
End Sub
Click to expand...

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 theirtab 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
 
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 before:=myNB.Worksheets(1)
myB.Close False
Next i
Else:
MsgBox "You clicked cancel"
End If
End Sub
Click to expand...

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 needto 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?)
Click to expand...

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
Click to expand...

David T
 
After serious thinking David T wrote :
That's the correct sound... but it's required in Excel.


cheers
Click to expand...

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
 
After serious thinking David T wrote :

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)
Click to expand...

I'll get back to you on that after discovering what ADODB is.


cheers
 
Back
Top