Copy one Excel Table into another one

Hi!

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!

VenioAsked:
Who is Participating?
 
dmangConnect With a Mentor Commented:
Set a reference to the Excel object library and then try this....it 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
    Else
        Err.Clear
    End If
   
    set wssheet = oxlbook.worksheets("All")
    wssheet.select
    wssheet.copy
    with oxlbook.activesheet
        .name = "Debitor-9xxxx"
    end with
   For r = 1 To LastRow(oxlbook.workSheets(ActiveSheet.Name))
   
        With oxlbook.ActiveSheet
            .Rows(r).Select
            If Selection.Text = "" Then
                .Rows(r).Delete
            End If
        End With
    Next r      
   
    oXLBook.Close
    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, _
      SearchOrder:=xlByRows).Row
  End With
 
End Function
0
 
VenioAuthor Commented:
How can I set a reference to the Excel object library (sorry I'm really new to this)
0
 
dmangCommented:
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
0
Never miss a deadline with monday.com

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?
0
 
dmangCommented:
you can manage multiple workbooks at the same time by setting up additional object variable...

for example ..

set oxlbook1 = oxlapp.workbooks.open("c:\test1.xls")
set oxlbook2 = oxlapp.workbooks.open("c:\test2.xls")

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

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

     'when all blank rows are removed, then select all rows

      cells.select ... 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
0
 
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.
0
 
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!
0
 
dmangCommented:
can you paste the code in this window?
or you could try this...

after the copy is done, set the
activesheet.name = "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.
0
 
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
0
All Courses

From novice to tech pro — start learning today.