Read Excel columns and rangedelete

[DigDeep]

Hi,

I wanted to read the excel > get to the last column header > delete all the columns except the column names

Test1, Test2, Test3 and Test4.

Everything works here, except the RangeDelete is not. Not sure what am I doing wrong?

 

Func ColumnFilter()
    Local $results = "C:\Temp\Results.txt"
    Local $oExcel = _Excel_Open(False)
    Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Temp\Test.xls")


    Local $CountRows = $oWorkbook.ActiveSheet.UsedRange.Rows.Count ; Count Rows till end
    Local $ColNum = $oWorkbook.ActiveSheet.UsedRange.Columns.Count ; Get last column Number
    Local $ColName = _Excel_ColumnToLetter($ColNum) ; Get last column Header Name

    If @error Then Exit MsgBox(0, "Excel UDF: _Excel_ColumnToNumber Example 1", "Error converting letter to number." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

    $FileOpen = FileOpen($results, 2)
    $FileWrite = FileWrite($FileOpen, $ColName)
    FileClose($results)
    $Col = FileRead($results)

    For $j = $ColNum To 1 Step -1
        $FileOpen = FileOpen($results, 2)
        $FileWrite = FileWrite($FileOpen, $ColNum)
        FileClose($results)
        $FileRead = FileRead($results)
        Local $sTab1 = _Excel_RangeRead($oWorkbook, Default, $ColName & '1')
        MsgBox(0, '', $sTab1)

        If $sTab1 <> 'Test1' Or $sTab1 <> 'Test2' Or $sTab1 <> 'Test3' Or $sTab1 <> 'Test4' Then
            _Excel_RangeDelete($oWorkbook.ActiveSheet, $sTab7, $xlShiftToLeft, 2)
            _Excel_BookSave($oWorkbook)
        EndIf

    Next

    _Excel_Close($oExcel)
EndFunc   ;==>ColumnFilter

 

Edited January 6, 2017 by DigDeep

[water]

Where do you set $sTab7?
What is the value of @error after _Excel_RangeDelete?

[DigDeep]

My bad... It should be $Tab1 and not $Tab7.

Also during this time I found that changing the below from:

_Excel_RangeDelete($oWorkbook.ActiveSheet, $sTab7, $xlShiftToLeft, 2)

to:

_Excel_RangeDelete($oWorkbook.ActiveSheet, $ColName & '1', 2)

works but it only removes 1 Column from last and the code exists.

My goal is only to keep the 4 columns and delete all the rest of them no matter if they are aligned after each other or in between other columns.

 

Say, if there are total 38 columns found as per $ColNum which falls as AH, I have assigned $Tab1 to read the last column name from the last 38 which is AH1 and if it does not read the column name as any of the 4, then it will delete the column and continue until only the 4 columns are left.

[water]

The following statement should be moved inside the loop. Else you will never change $ColName and hence always query the same column.

Local $ColName = _Excel_ColumnToLetter($ColNum) ; Get last column Header Name

 

[DigDeep]

I have tried again.

Gave msgbox for checking what's wrong. The 1st msgbox shows correct loop from 27, 26, 25, 24....

but the $Tab1 msgbox revolves around same column... That's why it is deleting only the last column and then exiting the loop.

 

Local $ColNum = $oWorkbook.ActiveSheet.UsedRange.Columns.Count ; Get last column Number

    For $j = $ColNum To 1 Step -1
MsgBox(0, '', $j) ; This shows correct results...

        Local $sTab1 = _Excel_RangeRead($oWorkbook, Default, $ColName & '1')

        If $sTab1 <> 'Test1' Or $sTab1 <> 'Test2' Or $sTab1 <> 'Test3' Or $sTab1 <> 'Test4' Then
MsgBox(0, '', $sTab1) ; This does not shows correctly. It circles around the same column...

            _Excel_RangeDelete($oWorkbook.ActiveSheet, $sTab1, $xlShiftToLeft, 2)
            _Excel_BookSave($oWorkbook)
        EndIf

    Next

 

[water]

Still $ColName does not get set inside the loop!

[DigDeep]

Re-tried with this. Still the same.

Local $ColNum = $oWorkbook.ActiveSheet.UsedRange.Columns.Count ; Get last column Number

    For $j = $ColNum To 1 Step -1

Local $ColName = _Excel_ColumnToLetter($ColNum) ; Get last column Header Name
        Local $sTab1 = _Excel_RangeRead($oWorkbook, Default, $ColName & '1')

        If $sTab1 <> 'Test1' Or $sTab1 <> 'Test2' Or $sTab1 <> 'Test3' Or $sTab1 <> 'Test4' Then

            _Excel_RangeDelete($oWorkbook.ActiveSheet, $sTab1, 2)
            _Excel_BookSave($oWorkbook)
        EndIf

    Next

 

[DigDeep]

I don't understand why the loop is only working for $j = $colnum to 1 Step -1

but the section:

If .... then

Endif

only deletes 1 column which is the last one.

[DigDeep]

It's all good now.

Thanks.

[water]