DigDeep

Read and delete Excel Columns

11 posts in this topic

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

 

Share this post


Link to post
Share on other sites



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

 


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

I am on Excel 2013.

Share this post


Link to post
Share on other sites

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

forum.PNG

the script successfully deletes Column A every time.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

here it is...

Test.xls

Edited by DigDeep

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

 


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

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.

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