Using VB to process data in Excel

cage88 · Aug 11, 1999

I want to write an app using VB that will allow me to open an XLS file and delete unecessary rows and columns. Also I would like to be able to arrange the columns in one...let say I have a table in excel with different years. I would like to take all the different data for the years and put it in one column. 
 
Can anyone help me start? 
 
Thanks.

bitbrain · Aug 12, 1999

You'll have to learn the Excel Object Model. It's complex but you can do most anything with it. 
 
Start by getting into Excel and using the Macro recorder to do things like selecting & deleting rows. Then inspect the VBA code it generated. Use Help to look-up the specifics. 
Once you understand the Excel object stuff you will be able to open an Excel file from VB and process it behind the scenes. 
 
Following is an excerpt of a macro I used to delete rows. It's not elegant but it works. 
 
 
 
Range("A1").Select 
intLastRow = LastRow 
 
' Delete blank lines 
bDel = True 
Do Until bDel = False 
bDel = False 
Range("A1").Select 
For i = 1 To intLastRow 
If RowBlank(i) Then 
Selection.Cells(i, 1).Select 
Selection.EntireRow.Delete (xlShiftUp) 
Range("A1").Select 
intLastRow = intLastRow - 1 
bDel = True 
i = intLastRow + 1 
ElseIf Cells(i, 1) Like "*TOTAL*" Then 
Selection.Cells(i, 1).Select 
Selection.EntireRow.Delete (xlShiftUp) 
Range("A1").Select 
intLastRow = intLastRow - 1 
bDel = True 
i = intLastRow + 1 
End If 
Next i 
Loop 
 
Function LastRow() As Long 
Dim i As Long 
For i = 1 To 65000 
If RowBlank(i) _ 
And RowBlank(i + 1) _ 
And RowBlank(i + 2) _ 
And RowBlank(i + 3) _ 
And RowBlank(i + 4) _ 
And RowBlank(i + 5) Then 
LastRow = i - 1 
i = 65000 
End If 
Next i 
 
End Function 
 
Function RowBlank(i) As Boolean 
If Cells(i, 1) = "" _ 
And Cells(i, 2) = "" _ 
And Cells(i, 3) = "" _ 
And Cells(i, 4) = "" _ 
And Cells(i, 5) = "" _ 
And Cells(i, 6) = "" _ 
And Cells(i, 7) = "" _ 
And Cells(i, 8) = "" _ 
And Cells(i, 9) = "" _ 
And Cells(i, 10) = "" Then 
RowBlank = True 
Else 
RowBlank = False 
End If 
End Function

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Using VB to process data in Excel

cage88

Programmer

bitbrain

Programmer

Similar threads

Part and Inventory Search

Sponsor