Read and delete Excel Columns

[DigDeep]

Hi,

I have an excel sheet. and want to delete all the columns except $Column1, $Column2 and $Column3.

The steps I am trying is to Open the excel > read the Cell A1 > If A1 does not read as $Column1, $Column2 and $Column3 then delete them.

This does not work. Looks like I am not able to create a loop.

But if I am trying to delete only the $Column1 as per the below code, it works good.

 

Can someone please help here to create a loop and delete all the columns except these 3?

#RequireAdmin

#include <Array.au3>
#include <Excel.au3>


Local $result = _Excel_RangeRead($oWorkbook, Default, "A1")
    Local $Column1 = 'Column1'
local $Column2 = 'Column2'
local $Column3 = 'Column3'

;~  While 1
;~      If $result <> $Column1 Or $result <> $Column2 Or $result <> $Column3 Then
If $result <> $Column1 Then
        _Excel_RangeDelete($oWorkbook.ActiveSheet, 'A:A', 2)

    EndIf

;~  WEnd

    _Excel_Close($oExcel)
EndFunc   ;==>ReadCell

 

[JLogan3o13]

This works for me. You can do it with a series of If statements, but this works well if you decide to take some action if it does equal Column 1, 2, or 3.

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\Test.xlsx")
Local $result = _Excel_RangeRead($oWorkbook, Default, "A1")
Local $Column1 = 'Column1'
Local $Column2 = 'Column2'
Local $Column3 = 'Column3'

    Switch $result
        Case $Column1, $Column2, $Column3
            ;do nothing
        Case Else
            _Excel_RangeDelete($oWorkbook.ActiveSheet, 'A:A', 2)
    EndSwitch

 

[DigDeep]

I tried using the above and with If statement too. But it doesn't delete any of the columns.

[JLogan3o13]

What version of Excel? It works just fine for me with Excel 2013 and 365 (2016).

[DigDeep]

I am on Excel 2013.

[JLogan3o13]

Is your excel file something you can share? With a simple excel file with the following columns:

the script successfully deletes Column A every time.

[DigDeep]

here it is...

Test.xls

Edited December 14, 2016 by DigDeep

[DigDeep]

I think I missed some parts here... If the Range reads $Column1 it should move to the next column to read it. If the next column does not read $Column2 then it should delete that column.

So, until $Column1 is found, rest columns should keep on deleting. Once $Column1 is found, it will be left out under 'A'. It will then move to read the $Column2 and do the same action.

Therefore, Column 'A' should not be deleted always. My purpose is, except the 3 columns, all the other columns should be deleted.

[DigDeep]

Basically, steps should be....

1. Read Column A. If Range Read <> $Column1 then delete... loop this until found $Column1.

2. Read Column B. If Range Read <> $Column2 then delete... loop this until found $Column2.

2. Read Column C. If Range Read <> $Column3 then delete... loop this until found $Column3.

Close Excel.

[JLogan3o13]

Do you know how many columns there will be, or is it an unknown? Also, are the values you're looking for always going to be in order? By that I mean, will you always find $Column1 before you find $Column2, etc. Or could they appear in any order?

 

[DigDeep]

1. As of now the excel I am working on has 26 columns, starting from A-Z. But the columns might increase / decrease in future. That's the reason I wanted to read the column names and decide if should be deleted or to be kept.

2. Also $Column1 will always be first then $Column2 and then $Column3 as in order.