Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Thanked 0 Times in 0 Posts

    Massage Excel file from VB6.0 (VB6.0 and Excel 2000)

    I've dragged out my copy of VB6.0 and have a question. I added a CommonDialog control on a form so I can select an Excel file. The information that I need is always in the same column. I need to delete all but column M, delete the top 3 rows, and rename the column heading. I need to accomplish this entirely from the VB form, not from Excel.

    I just need some ideas on the proper way to connect to the Excel file, delete columns, sort, rename columns, etc. from VB6.0. Any pointers to materials relevant to this would be greatly appreciated.


  2. #2
    2 Star Lounger
    Join Date
    Nov 2001
    London, UK, England
    Thanked 0 Times in 0 Posts

    Re: Massage Excel file from VB6.0 (VB6.0 and Excel 2000)

    You need to automate Excel by creating an instance of the Excel.Application object. First thing to do is set a reference to Excel in your VB6 project (Project > References menu) by selecting the 'Microsoft Excel 9.0 Object Library'.

    I've attached a text file which is a 'Excel wrapper' class. Download the file, rename the file extension to .cls and add it to your project and you will be able to begin automating excel. The following is some example code on using the object...

    Private Sub DoExcelAutomation()

    Dim cExcel As ExcelAutomation

    Set cExcel = New ExcelAutomation

    cExcel.OpenExcelFile Commondialog.FileName '// Change this to your file name

    With cExcel.ExcelApplication.ActiveSheet

    '// Change this to the specific code you wish to run over the Excel worksheet
    .Selection.Delete Shift:=xlToLeft
    .Selection.Sort Key1:=Range("I1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End With

    Set cExcel = Nothing

    End Sub

    So, you could record a macro in Excel to do all the stuff you want to do to the sheet, then just copy the generated code into the VB application (place the code into the 'With' block in the example code, remembering to prefix all the code with a '.' full stop)
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts