Jump to content

Read Excel columns and rangedelete


Recommended Posts

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
Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

Still $ColName does not get set inside the loop!

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

:) 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...