Copy one Excel Table into another one


I've got a problem. I need to switch to the right Excel Window (Named DEBITOR-9xxxx...) copy all the lines that have text in another Excel Window (Named ALL) and close the Excel Window afterwards.
The Problem is, that I have to do this with Visual Basic 6.0 (so no VBA commands...) and that I need to repeat this for a number of files (which I have to load)
I hope you can help me!

Who is Participating?
dmangConnect With a Mentor Commented:
Set a reference to the Excel object library and then try take the current worksheet makes a copy, renames the new sheet, and removes blank rows

sub command1_click()

    Dim oXLApp As Excel.Application
    Dim oXLBook As Object
    Dim r as integer
    Set oXLApp = New Excel.Application
    'open you workbook
    Set oXLBook = oXLApp.Workbooks.Open("c:\test.xls")
    If Err.Number = 0 Then
        'oXLApp.WindowState = xlNormal
        oXLApp.Visible = True 'set to false to hide process
    End If
    set wssheet = oxlbook.worksheets("All")
    with oxlbook.activesheet
        .name = "Debitor-9xxxx"
    end with
   For r = 1 To LastRow(oxlbook.workSheets(ActiveSheet.Name))
        With oxlbook.ActiveSheet
            If Selection.Text = "" Then
            End If
        End With
    Next r      
    Set oXLBook = Nothing
    Set oXLApp = Nothing

end sub

Function LastRow(ws As Worksheet) As Single
  On Error Resume Next

  With ws
    LastRow = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
  End With
End Function
VenioAuthor Commented:
How can I set a reference to the Excel object library (sorry I'm really new to this)
In the VB project, select Project | References from the main menu.  Scroll down the list to find the Microsoft Excel Object Library (the version will depend on your Office installation ie. v8.0 is for Office 97, v9.0 for Office 2000.)
Check the reference.
Once referenced, you should be OK
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

VenioAuthor Commented:
I see - thank you - the only problem I still have is, that the data I should combine is in completely different files. For Example a file called FINAL.XLS and DEBITOR90001.XLS ...02.XLS and so on. Ca I do that with the same source?
you can manage multiple workbooks at the same time by setting up additional object variable...

for example ..

set oxlbook1 ="c:\test1.xls")
set oxlbook2 ="c:\test2.xls")

If you have one target worksheet, I suggest you use the original code above

 for each workbook ....(use dir to loop through a folder)
     remove blank rows

     'when all blank rows are removed, then select all rows ... will select the entire sheet
      selection.copy.. will copy the sheet

      now, on you target sheet,
      select the next available row
          (ie. lastrow(sheet) + 1), and then

      Selection.paste    .. to paste in the copied sheet..
      close the workbook
 next worksbook
VenioAuthor Commented:
thank you very much so far! I will try it and accept your answer as the right one. You've really helped me. It would be great, if you could help if I happen to have a question regarding this topic.
VenioAuthor Commented:
I've just tried your sourcecode and whenever I try to run the programm I get the errorcode 1004 saying, that its not possible to name one sheet like another. What suprises me is that the code creates a new excel table.
Please help me again... I need to get this programm running. Can I mail you the code so you can have a slight look at it? WOULD BE REALLY GREAT!
can you paste the code in this window?
or you could try this...

after the copy is done, set the = "New sheet name" ... what ever you like as a name (Up to 30 characters).  Include a counter or something else that will uniquely identify the worksheet.
VenioAuthor Commented:
Thank you very much for your help. I've found a good way to solve the problem. Hope to hear from you again
All Courses

From novice to tech pro — start learning today.