Combining Excel Worksheets

[solidjp]

I have 2 or more different excel workbooks with 1 worksheet of data in each workbook. I need to put all the worksheets into one new excel workbook. Either by combining all the worksheets into one big worksheet, or each having there own worksheet in one big workbook. Do I need to do some copy and paste methods for doing this or is there a better way. I know it can be done, I just can't figure it out. Oh yeah, using VB 6 and Excel to do this.

Thanks in advance.

 

[ufobaby]

hi,

there's no need to involve VB actually , this can be done thru excel itself.

'==== Code
'Create a dummy workbook and open it first

Workbooks.open("dummy.xls"...

'Open the WorkBook which has the sheet to be copied this can be in a loop if lot of flies r there...

Workbooks.open(.....
ActiveWorkbook.Sheets("Sheet1&quot.Copy After:=Workbooks("dummy&quot.Sheets(1)

This code can be written in any workbook and executed...

Would also like to suggest u that , if any VBA code is required then u should first use the Tools->Macro->Record Macro option , this will generate the code for all the actions performed and then it's easy to use MSDN and
play around with the code to get thru...dear all, correct me here if am wrong

solidjp , if this doesn't work lemme know , feel free , we all r here to learn


Niraj

 

[solidjp]

Actually I do need to use VB. I'm writing a program that will merge the selected excel files together by a command button.

I tried doing what you suggested in the code you supplied but didn't have any luck. It keeps telling me that I have a copy error. I still don't see how it pastes the code into the new sheet or what it does. I'm a little confused. I haven't ever used the record macro option so I would like to stay away from that unless that's my only option to getting this to work.

Thanks

 

[JustinEzequiel]

There might be a better solution but as yet have not found one.
What I did find is this.
I was not able to make the Copy method work also until I made the Excel.Application object visible.

 

[solidjp]

When I'm debugging the code in VB I will open the Excel spreadsheet in memory and see what's going on during the copy method. When I open it, I can see the whole sheet selected like it's in copy mode but anything I usually do after that doesn't work. I'll try what you suggested and see if that helps. Thanks.

 

[solidjp]

bumpity bump

 

[JustinEzequiel]

>>I tried doing what you suggested in the code you supplied but didn't have any luck. It keeps telling me that I have a copy error.

post your code then I'll see if I can suggest something

 

[solidjp]

Here's the code:

Function MergeExcelFiles()

Dim xlApp As Excel.Application
Dim xlApp2 As Excel.Application
Dim xlWb As Object
Dim xlWb2 As Object
Dim xlWs As Object
Dim xlWs2 As Object
Dim lngRow As Long
Dim i, j As Integer
Dim sGridOrder As String
Dim sGridOrder2 As String
Dim sFileNumSplit As Variant
Dim sFileNumSplit2 As Variant

j = fgFileOrder.Rows - 1

For i = 1 To j
If i = 1 Then
Set xlApp = CreateObject("Excel.Application&quot
xlApp.AskToUpdateLinks = False
xlApp.DisplayAlerts = False

sGridOrder = fgFileOrder.TextMatrix(i, 0)
Set xlWb = xlApp.Workbooks.Open(Dir1.Path & "\" & sGridOrder)
sFileNumSplit = Split(sGridOrder, ".", , vbTextCompare)
Set xlWs = xlWb.Worksheets(sFileNumSplit(0)) 'sFileNumSplit="excelfile.xls"
Else
If i > 1 Then
Set xlApp2 = CreateObject("Excel.Application&quot
xlApp2.AskToUpdateLinks = False
xlApp2.DisplayAlerts = False
sGridOrder2 = fgFileOrder.TextMatrix(i, 0)
Set xlWb2 = xlApp2.Workbooks.Open(Dir1.Path & "\" & sGridOrder2)
sFileNumSplit2 = Split(sGridOrder2, ".", , vbTextCompare)
Set xlWs2 = xlWb2.Worksheets(sFileNumSplit2(0))
Workbooks(1).Sheets(1).Copy After:=xlWb.Sheets(sFileNumSplit(0))
End If


If i > 1 Then
xlWb.Close savechanges:=True
xlWb2.Close savechanges:=True
End If
End If
Next

Set xlWb = Nothing
Set xlWb2 = Nothing
Set xlWs = Nothing
Set xlWs2 = Nothing
Set xlApp = Nothing
Set xlApp2 = Nothing


End Function

 

[solidjp]

Here's the problem. I can't seem to get Workbook 1 to copy it's sheet into Workbook 2. I get a subscript out of range error when I try to do that. I can only copy Workbook 1's worksheet into another worksheet in it's workbook, not into a completely different workbook. The line of code I'm having trouble with is this:

Workbooks(1).Sheets(1).Copy After:=xlWb.Sheets(sFileNumSplit(0))
where sFileNumSplit(0) equals a the worksheet.

Does anyone know how to copy a worksheet and put it into a new workbook? Can this even be done.

THanks

 

[JustinEzequiel]

using the code below, I was able to copy a worksheet from one workbook to another workbook

and I did not have to make the Excel Application visible (see my post above)

Option Explicit

Private Sub Command1_Click()
  Dim oXLApp As Excel.Application
  
  Dim oWBSource As Excel.Workbook
  Dim oWBTarget As Excel.Workbook
  
  Dim oWSSource As Excel.Worksheet
  Dim oWSTarget As Excel.Worksheet
  
  Set oXLApp = New Excel.Application
  
  Set oWBSource = oXLApp.Workbooks.Open("C:\Book1.xls")
  Set oWSSource = oWBSource.Worksheets(1)
  
  Set oWBTarget = oXLApp.Workbooks.Open("C:\Book2.xls")
  Set oWSTarget = oWBTarget.Worksheets(1)
  
  oWSSource.Copy After:=oWSTarget
  
  Set oWSTarget = Nothing
  
  oWBTarget.Save
  oWBTarget.Close
  Set oWBTarget = Nothing
  
  Set oWSSource = Nothing
  oWBSource.Close
  Set oWBSource = Nothing
  
  oXLApp.Quit
  Set oXLApp = Nothing
  
  MsgBox "done"
End Sub

 

[solidjp]

Thanks Justin. I figured out what I was doing wrong. I never did the New Excel.Application. I declared two Excel.Applications's without the second one being new. Anyway, thanks a lot bro.

 

[JustinEzequiel]

no problem