Copy one Excel Table into another one

Posted on 2003-03-18
Medium Priority
Last Modified: 2010-05-01

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!

Question by:Venio
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4

Accepted Solution

dmang earned 120 total points
ID: 8159699
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
    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

Author Comment

ID: 8160834
How can I set a reference to the Excel object library (sorry I'm really new to this)

Expert Comment

ID: 8160856
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 8160900
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?

Expert Comment

ID: 8160956
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

 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

Author Comment

ID: 8165062
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.

Author Comment

ID: 8165175
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!

Expert Comment

ID: 8166286
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.

Author Comment

ID: 8166346
Thank you very much for your help. I've found a good way to solve the problem. Hope to hear from you again

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month15 days, 15 hours left to enroll

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question