Using VB to process data in Excel

[cage88]

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. <br>
<br>
Can anyone help me start?<br>
<br>
Thanks.

 

[bitbrain]

You'll have to learn the Excel Object Model. It's complex but you can do most anything with it.<br>
<br>
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.<br>
Once you understand the Excel object stuff you will be able to open an Excel file from VB and process it behind the scenes.<br>
<br>
Following is an excerpt of a macro I used to delete rows. It's not elegant but it works.<br>
<br>
<br>
<br>
Range("A1").Select<br>
intLastRow = LastRow<br>
<br>
' Delete blank lines<br>
bDel = True<br>
Do Until bDel = False<br>
bDel = False<br>
Range("A1").Select<br>
For i = 1 To intLastRow<br>
If RowBlank(i) Then<br>
Selection.Cells(i, 1).Select<br>
Selection.EntireRow.Delete (xlShiftUp)<br>
Range("A1").Select<br>
intLastRow = intLastRow - 1<br>
bDel = True<br>
i = intLastRow + 1<br>
ElseIf Cells(i, 1) Like "*TOTAL*" Then<br>
Selection.Cells(i, 1).Select<br>
Selection.EntireRow.Delete (xlShiftUp)<br>
Range("A1").Select<br>
intLastRow = intLastRow - 1<br>
bDel = True<br>
i = intLastRow + 1<br>
End If<br>
Next i<br>
Loop<br>
<br>
Function LastRow() As Long<br>
Dim i As Long<br>
For i = 1 To 65000<br>
If RowBlank(i) _<br>
And RowBlank(i + 1) _<br>
And RowBlank(i + 2) _<br>
And RowBlank(i + 3) _<br>
And RowBlank(i + 4) _<br>
And RowBlank(i + 5) Then<br>
LastRow = i - 1<br>
i = 65000<br>
End If<br>
Next i<br>
<br>
End Function<br>
<br>
Function RowBlank(i) As Boolean<br>
If Cells(i, 1) = "" _<br>
And Cells(i, 2) = "" _<br>
And Cells(i, 3) = "" _<br>
And Cells(i, 4) = "" _<br>
And Cells(i, 5) = "" _<br>
And Cells(i, 6) = "" _<br>
And Cells(i, 7) = "" _<br>
And Cells(i, 8) = "" _<br>
And Cells(i, 9) = "" _<br>
And Cells(i, 10) = "" Then<br>
RowBlank = True<br>
Else<br>
RowBlank = False<br>
End If<br>
End Function