?
Solved

Copy one Excel Table into another one

Posted on 2003-03-18
9
Medium Priority
?
127 Views
Last Modified: 2010-05-01
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!

0
Comment
Question by:Venio
[X]
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
9 Comments
 
LVL 9

Accepted Solution

by:
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
    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
 

Author Comment

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

Expert Comment

by:dmang
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
0
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

by:Venio
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?
0
 
LVL 9

Expert Comment

by:dmang
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

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
 

Author Comment

by:Venio
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.
0
 

Author Comment

by:Venio
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!
0
 
LVL 9

Expert Comment

by:dmang
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.
0
 

Author Comment

by:Venio
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
0

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