DigDeep

Read Excel columns and rangedelete

10 posts in this topic

#1 ·  Posted (edited)

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 by DigDeep

Share this post


Link to post
Share on other sites



#2 ·  Posted

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted

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.

Share this post


Link to post
Share on other sites

#4 ·  Posted

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted

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

 

Share this post


Link to post
Share on other sites

#6 ·  Posted

Still $ColName does not get set inside the loop!


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#7 ·  Posted

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

 

Share this post


Link to post
Share on other sites

#8 ·  Posted

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.

Share this post


Link to post
Share on other sites

#9 ·  Posted

It's all good now.

Thanks.

Share this post


Link to post
Share on other sites

#10 ·  Posted

:) 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now