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

Topic

New Reply

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.

Randy

Reply | Quote

Replies

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
.Columns(“I:K”).Select
.Selection.ClearContents
.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)

Reply | Quote