Sign in to follow this  
Followers 0
zsoltm

<Noobie>Excel first script

28 posts in this topic

Hello everybody. I working in office and I need to do a lot of repetitive excel job ... thats how I get here lol. So I am very noobie yet. Thats why i want to ask your help if not problem. I am kinda understand the GUI...that is not a problem... My problem is starting when I need to write a script :D (its ironic, isn't it?)

So  the first script should do the followings :

  1. Insert a New Column (should be B but doesnt really matter)
  2. add a formula to the whole Column (Ctrl + Enter)
  3. If the result is Yes DELETE the whole row which are yes (I using Kutools for Excel when i doing manually by hand)
  4. after clear the coumn
  5. add a second formula
  6. if yes delete the whole rows again.
  7. after delete the whole column.

I'm not expect to write me this ( but if you do that is very appriciable) so if you can show me a similar script that would be help a lot.

That is what I had done so far:
 

#include <GUIConstants.au3>
#include <Constants.au3>



; GUI Creation
GUICreate("Excel LEAD CleanUP", 150, 320)
GUISetIcon(@WindowsDir & "\explorer.exe", 0)



; Menu Creation
$filemenu = GUICtrlCreateMenu("File")
GUICtrlCreateMenuItem("Start", $filemenu)
GUICtrlCreateMenuItem("Stop", $filemenu)
GUICtrlCreateMenuItem("Exit", $filemenu)
; Context Menu
$contextmenu = GUICtrlCreateContextMenu()
; Context Menu Item
GUICtrlCreateMenuItem("Example1", $contextmenu)
GUICtrlCreateMenuItem("", $contextmenu) ; separator
GUICtrlCreateMenuItem("Example2", $contextmenu)
; BusinessRemoval
$business = GUICtrlCreateButton("BusinessRemoval", 15, 10, 120, 25)
; Address Lane 1 Separator
$address1 = GUICtrlCreateButton("Clean Address1", 15, 40, 120, 25)
; Address Lane 2 Separator
$address2 = GUICtrlCreateButton("Clean Address2", 15, 70, 120, 25)
; Postcode Cleaner
$postcode = GUICtrlCreateButton("Postcode", 15, 100, 120, 25)
; Phone number separator
$phone = GUICtrlCreateButton("Phone Cleaner", 15, 130, 120, 25)
; Phone PREFIX
$phone = GUICtrlCreateButton("PREFIX", 15, 160, 120, 25)
; Progress Bar
GUICtrlCreateLabel("Progress Example", 15, 260, 120, 25)
$progress = GUICtrlCreateProgress(15, 260, 120, 25)
GUICtrlSetData($progress, 33)
; Dummy
$dummybutton = GUICtrlCreateButton("Dummy Example", 15, 230, 120, 25)
$dummy = GUICtrlCreateDummy()
; Display GUI
GUISetState(@SW_SHOW)
; Continuous Loop to check for GUI Events
While 1
    $guimsg = GUIGetMsg()
    Select
        Case $guimsg = $dummybutton
            GUICtrlSendToDummy($dummy)
        Case $guimsg = $dummy
            MsgBox(0, "Dummy Example", "You have clicked the dummy button.")
        Case $guimsg = $GUI_EVENT_CLOSE
            Exit
    EndSelect
WEnd

just copied from the quick guide.

Thanks a lot. 

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

Welcome to AutoIt and the forum!

AutoIt comes with a lot of UDFs (User Defined Functions). One of them handles Excel. It is a bit limited to XLS files and and is a bit slow.

If you want to work with the latest beta version of AutoIt I suggest my rewrite of the Excel UDF (for download please see my signature). My UDF supports all what you want to do.

Edited by water

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

Hello water! thank you I already downloaded. 
when i including the Excel rewrite its giving me an error.

C:Program Files (x86)AutoIt3IncludeExcel Rewrite.au3(168,56) : ERROR: ObjGet() [built-in] called with wrong number of args.
$oWorkbook = ObjGet("", $sCLSID_Workbook, $iCount + 1)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:Program Files (x86)AutoIt3IncludeExcel Rewrite.au3(249,57) : ERROR: ObjGet() [built-in] called with wrong number of args.
$oWorkbook = ObjGet("", $sCLSID_Workbook, $iIndex + 1)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:Program Files (x86)AutoIt3IncludeExcel Rewrite.au3(1155,27) : ERROR: _ArrayTranspose(): undefined function.
_ArrayTranspose($vResult)
~~~~~~~~~~~~~~~~~~~~~~~~^
C:UserssainsburysDocumentsscript.au3 - 3 error(s), 0 warning(s)
!>21:47:35 AU3Check ended. Press F4 to jump to next error.rc:2
>Exit code: 2    Time: 0.425



or what i should include? 

Share this post


Link to post
Share on other sites

My UDF needs to be run with the latest beta version of AutoIt


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

yep alright I downloaded the beta and if execute the Beta Run than its alright. anything else what can make my life easier?

Share this post


Link to post
Share on other sites

I recorded a macro in excel but after gave me an error so I just copied what i can use. Its very raw I dont even now what do I need to change to be acceptable for autoIT.. I am asking for help becouse the time just working against me. 

Columns("A:A").Select
Selection.Insert Shift : = xlToRight, CopyOrigin : = xlFormatFromLeftOrAbove
Range("A1:A100000").Select ;I just made up the range further notes at the end
Selection.FormulaR1C1 = "=IF(OR(ISNUMBER(search(" "&{"quality","st","rainbow","carryout","auto","surgery","public","fertiliser","Fertilisers","ifertilisersl","rendezvous","remember","that","precast","barefoot","mail","scaffolding","safe","access","salvation","army","funeral","directors","director","seahorse","simply","affordable","stich","time","dressmaking","alterations","hospital","by","jewellers","post","sweet","sensation","trophies","engraving","protection","produce","public","relations","made","fun","auctions","auction","kiltmakers","kiltmaker","linen","chippy","timber","tidy","tangles","past","dealer","trading","crafts","craft","naval","activities","US","Uncle","Diner","Removals","Volunteer","Zoo","Wavelength","Wavelengths","Friends","hairdressing","theatreclub","daynursery","MUSLC","superstore","colleges","AALCO","AARDVARK","ABACUS","ABB","ABBOTSWELL","ABBOTTPUBLIC","ABC","ABCON","ABDN","ABERCARE","ABERDEE","ABERDEEN","ABERDEENSHIRE","ABERFLORA","ABERGELDIE","ABERGLEN","ABERLOUR","ABERMED","ABERNE","ABERPEST","ABERSOL","ABERT","ABSCOT","ABSOFT","ABSPEK","ABSURE","ABTEX","ABTRUST","ACADEMY","ACCESS","ACCESSORIES","ACCOMMODATION","ACCORD","ACCOUNTANCY","ACCURAY","ACE","ACTION","ADAPT","ADEPT","ADMIN","ADMIRAL","ADS","ADULT","ADVANCE","ADVANCED","ADVANT","ADVANTAGE","ADVICE","AEA","AFFAIR","AFOS","AGA","AGE","AGENCY","AGIP","AGRI","AGRICULTURAL","AGRICULTURE","AHT","AI","AIDS","AIR","AIRBORNE","AIRFAIR","AIRLINES","AIRNAUTIC","AIRPAC","AIRPORT","AIRWAYS","AIRYHALL","AIRYLEA","AKRON","ALAD","ALASDAIR","ALB","ALBATECH","ALFA","ALL","ALLIANCE","ALLIED","ALLOMAX","ALPINE","ALSTEV","ALTERNATIVES","ALTRA","ALWAYS","ALZHEIMER","AM","AMALGAMATED","AMAT","AMATOLA","AMBER","AMEC","AMERICAN","AMETEK","AMIN","AMITEC","AMOCO","AMTECH","AMTRAK","AMUSEMENT","AND","ANDERGAUGE","ANIXTER","ANKA","ANTIQUES","ANTRIM","AOC","AP","APARDION","APEX","APOLLO","APOSTOLIC","APPEALS","APPLIED","APPOLLO","AQUA","AQUARISTS","AQUATEC","AQUATIC","AQUIDATA","ARCHITECTURAL","ARCO","ARGONAUT","ARGOSY","ARISTACUT","ARJO","ARJON","ARKAIG","ARTISTES","ARTISTS","ARTWORKS","ARUP","AS","ASA","ASCOT","ASDASUPERSTORES","ASHVALE","ASPECT","ASSEMBLIES","ASSESSORS","ASSOCIATED","ASSOCIATES","ASSOCIATION","ASSURANCE","ASTER","ASTRA","ATEL","ATHENA","ATHENAEUM","ATHOLL","ATLANTIC","AUCHMILL","AUCHTERLESS","AUGHTON","AUGUST","AUP","AUQUHARNEY","AURORA","AUTO","AUTOFIX","AUTOGLASS","AUTOMAGIC","AUTOQUICK","AUTOSAVE","AUTOSPRAY","AUTOTRUCK","AVCO","AVENUE","AWAKENING","AWAY","AXIOM","AZTEC","BAADER","BALNAGASK","BANKHEAD","BAR","BARICO","BARRIER","BARTERING","BASELINE","BATCHBRIGHT","BATH","BATHROOM","BAVARIA","BAYLISS","BAYLOR","BEARING","BEATTIES","BEAUTY","BEAVERBROOKS","BEECHWOOD","BEEFEATER","BEELINE","BEFRIEND","BENBOW","BESTWAY","BEWS","BIKES","BLIND","BLINDCRAFT","BLINDS","BLOOD","BLOOMING","BLOSSOMS","BLOW","BLUEBELLS","BMT","BNA","BOARD","BOARS","BOAT","BODY","BOHEMIA","BOILERMAKERS","BON","BONADDIO","BONAVENTURE","BONIVIEW","BONNYMUIR","BOOKBINDING","BOOTS","BOROWSKI","BORSALINO","BOSANQUET","BOSCH","BOTTLED","BOULEVARD","BOURTREE","BOWEN","BOWLING","BOWTECH","BOX","BP","BPCC","BRA","BRACKENRIDGE","BRAEHEAD","BRAESIDE","BRAKE","BRIEF","BRITANNIA","BRITANNIC","BROAD","BROADCAST","BROADSTRAIK","BROS","BROTHERHOOD","BRUNSWICK","BUCKSBURN","BUDGET","BUILDING","BULAWEYO","BUREAU","BURNTHILLS","BUS","BUSINESS","CABER","CABINS","CABLE","CABS","CAFE","CAIRD","CAIRNCRY","CAIRNGORM","CAKE","CALEDON","CALEDONIA","CALEDONIAN","CALLANDERS","CALOR","CALTEC","CALVARYTABERNACLEUNITED","CAMARGUE","CAMEO","CAMPAIGN","CAMPBELLS","CAMPHILL","CAMWATER","CAN","CANALE","CANCER","CANDO","CANSCO","CAPE","CARADON","CARD","CAREERS","CARGO","CARPET","CARPETS"}&" "," "&b1&" "))),"YES","NO")"
Selection.AutoFilter
Columns("A:A").Select
ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field : = 1, Criteria1 : = "YES"
Rows("x:y").Select ;all row which is YES
Selection.Delete Shift : = xlUp
ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field : = 1
Columns("A:A").Select
Selection.ClearContents
Range("A1:A100000").Select ;I just made up the range further notes at the end
Selection.FormulaR1C1 = "=IF(OR(ISNUMBER(search(" "&{"CARRINGTON","CARS","CASTLEHILL","CASTLEPARK","CASTROL","CASUAL","CATC","CATEREASE","CATERING","CATHEAD","CATHERWOOD","CATHOLIC","CATS","CAUSEWAYEND","CAVALIER","CAVANAGH","CEBO","CELEBRATION","CELICA","CELLHIRE","CELTIC","CENTER","CENTRAL","CENTRE","CENTRILIFT","CENTURY","CERAMICS","CH","CHALCO","CHAMBER","CHAMPERS","CHAP","CHARTERED","CHAUFFEUR","CHECKPOINT","CHELL","CHELTENHAM","CHEM","CHEMDRY","CHEMICAL","CHEMICALS","CHEMISTS","CHESS","CHEST","CHILDREN","CHIME","CHINESE","CHIP","CHIROPODY","CHRISTADELPHIAN","CHURCHES","CICT","CIMAGE","CINDERELLA","CINDERS","CINQUE","CIRCLE","CIRCUIT","CIRRUS","CISCO","CITY","CL","CLAN","CLEANALL","CLEANCARE","CLEANING","CLEARWAY","CLEENEZE","CLINIC","CLINICARE","CLIPSO","CLOCKWORK","CLOTHING","CLUB","COALFORD","CODNCIL","COFFEE","COLLEGE","COLLEGES","COLTD","COMMERCE","COMMUNICATIONS","COMMUNITY","COMPANIES","COMPONENT","COMPONENTS","COMPUTER","CONCERN","CONDITIONING","CONFERENCE","CONNECTIONS","CONSERVATORY","CONSTRUCTIN","CONSTRUCTION","CONSULTANTS","CONTINENTAL","CONTRACTS","CONTROL","COOKERS","COT","COUNCELNNG","COUNCIL","COUNCTL","COURIERS","CREMATORIUM","CRICKET","CRYOGENIC","CUISINE","CYRENIAN","CYRENIANS","DEAF","DECORATORS","DENTAL","DES","DESIGN","DETACHED","DEVELOPMENT","DEVELOPMENTS","DEVICES","DIGITAL","DIRECT","DISCOTHEQUE","DISPOSALS","DISTILLERS","DISTRIBUTORS","DIVING","DMLTD","DOMESTIC","DREDGING","DRILLING","DRIVE","DRUGS","DRUM","DRY","EDUCATION","ELECTRICAL","ELEVEN","EMPLOYMENT","ENCOUNTER","ENCOUNTERS","ENDOWMENTS","ENERGY","ENG","ENGINEERING","ENGLNEERING","ENGRAVING","ENL","ENQUIRIES","ENVIRONMENTAL","EQUIPMENT","ERAL","ESL","EXHAUST","EXHAUSTS","EXHIBITION","EXPLORATION","EXPRESS","FABRICATIONS","FACTORS","FARMHOUSE","FASTENERS","FILLING","FILM","FIRE","FISH","FISHERIES","FLORISTS","FLOWERS","FOOD","FOODS","FOODSERVICE","FOOTBALL","FOR","FOUNDRIES","FREE","FUND","FURNISHINGS","FURTHER","GALLERIES","GARAGE","GARMENTS","GAS","GEN","GIRI","GLOUCESTER","GOD","GOLF","GROUP","GUESTHOUSE","HAIR","HANDLING","HEALTH","HEALTHCENTRE","HEATING","HIRE","HO","HOLDINGS","HOMEI","HOTEL","HOUR","HOUSE","HOUSING","HRS","HTG","IA","IECENTRE","IMAGE","IMAGING","IMPELL","IMPERIAL","IN","INC","INCOME","INCORPORATED","INDUATRIAL","INDUSTRIAL","INFANT","INJECTION","INJURY","INN","INORTH","INSPECTION","INSURANCE","INTERNATIONA","INTERNATIONAL","INVESTMENT","INVESTMENTS","IPRIVATE","ISCOUNT","IU","IUKI","JEWELLERS","JEWELLERY","JH","JOINERY","JOURNALS","KITCHEN","KL","KURTUBA","LA","LADLES","LADS","LAUNDERETTE","LAVAL","LEAGUE","LEICESTER","LEISURE","LINK","LIVAL","LOCAL","LOUNGE","LOVELY","LTD","LTDL","MACHINE","MANAGEMENT","MANAGERS","MANIVOFIELD","MARINE","MARKETING","MAZDA","MECHANICAL","MEDICAL","MEDICINE","MENTS","METAL","METERING","MORTGAGE","MOTOR","MOTORING","MOTORS","MOUNTAIN","NDLERS","NEWS","NEWSPAPERS","NTERNATIONAL","NURSERY","NURSING","OF","OFFICE","OFFSHORE","OIL","OILS","OLD","OPTICAL","OPTICIANS","OVE","P0","PAD","PARCELS","PARTNERS","PARTY","PEOPLES","PETROLEUM","PFNTECOSTAL","PHARMACY","PHONE","PHOTOGRAPHIC","PL","PLC","PLMBG","PLUS","PODIATRY","POLLUTION","POWER","PRACTICE","PRE","PRESBYTERIAN","PRIMARY","PRINT","PRINTING","PRODUCTS","PROJECT","PROMOTIONS","PROP","PROPERTY","PROTECTION","PROVINCIAL","PUB","PUBLICATIONS","PUBLISHING","QUALITY","QUALTTY","RADIOGRAPHY","RD","RE","RECREATION","REFRIGERATION","RELOCATIONS","REMOVALS","RENDEZVOUS","RENT","RENTALS","REPAIRS","RESEARCH","RESOURCES","RESTAURANT","RESTAURANTS","RESTNT","RESTORATIONS","RUGBY","SAFETY","SALES","SALON","SAS","SATELLITE","SCHOOL","SCIENCE","SCOTLAND","SEA","SEAL","SEAS","SECURITIES","SERVICE","SERVICES","SHOP","SKIP","SLATING","SMOKED","SOCIAL","SOCIETY","SOFTWARE","SOLICITORS","SONY","SPBRT","SPEC","SPORTS","SRD","STABILISERS","STAMP","START","STATION","STATIONERS","STATIONERY","STAYTAN","STONEYWOOD","STUDIO","SUNBEDS","SUPERBOWL","SUPPLIES","SUPPLTES","SUPPORT","SURVEYS","SYSTEMS","SYSTENIS","TAKE","TAKEAWAY","TAKEAWAYS","TAXIS","TEC","TECHNICAL","TECHNOLOGY","TELEVIDEO","TELEVISION","THE","THISTLE","TO","TOOLS","TOURIST","TRACKING","TRAILER","TRAINING","TRANSFUSION","TRANSMISSION","TRANSPORT","TRAVEL","TROPHIES","TRUST","TUBULARS","TYRE","TYRES","UK","UNION","UNIT","UP","VERITAS","VETCO","VICTIM","VIDEO","VILLAGE","WAYS","WELFARE","WERKE","WHOLESALE","WINDOW","WINDOWS","WINDSCREENS","WITH","WOMEN","WORK","WORLD","YOGA","YOUTH"}&" "," "&b1&" "))),"YES","NO")"
Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field : = 1, Criteria1 : = "YES"
Rows("x:y").Select ;all row which is YES
Selection.Delete Shift : = xlUp
ActiveSheet.Range("$A$1:$A$2651").AutoFilter Field : = 1
Columns("A:A").Select
Selection.ClearContents
Range("A1:A100000").Select ;I just made up the range further notes at the end
Selection.FormulaR1C1 = "=IF(OR(ISNUMBER(search(" "&{"Coffee","health","club","distribution","design","solution","taxis","sport","Porsche","garage","shelving","plastics","ndscreens","body","repairs","process","automation","cleaners","signs","management","services","co","school","spares","cooling","domestics","gardening","raceshop","racing","LTD","Taxi","cabs","automotive","electrical","wholesale","enguineering","fabrications","structures","recruitment","construction","contracts","above","products","insurance","brokers","machine","tool","transport","services","trucks","aviation","homestores","associates","joinery","heating","safety","autos","security","supplies","spares","electronics","news","alternative","metal","fabrications","book","keeping","partnership","services","alfa","romeo","fertilizers","reusable","marketing","interiors","pneumatics","quarries","butchers","fresh","frozen","meats","travel","agent","agts","sstem","team","automotive","rubbish","machinery","communication","analytical","café","consumer","products","hotel","rest","precision","grinding","estates","assurance","roofing","office","blinds","business","cettre","caravan","décor","laboratory","dental","hire","national","pharmacy","photo","photographic","picture","framing","press","shop","sports","truck","preservation","preservations","video","society","print","services","pictures","studio","sub","tropical","swannery","packaging","capt","Europe","building","booking","visa","operative","balloon","demolition","driving","fibreglass","employment","flats","properly","waste","welding","window","dirtbuster","gift","inn","hire","mower","flooring","air","the","garage","marketing","residential","dry","pharmacy","glazing","uk","training","british","conference","centre","age","concern","hire","retirement","inspectorate","training","board","financial","age","sales","sale","store","personalized","trust","insurance","squadron","aviation","ventilation","news","international","supplies","rent","est","fabrics","gents","hair","carriage","investments","chips","plc","speciality","gifts","models","anonymous","commercial","surplus","stores","plastia","engineering","collection","chemical","amusement","care","service","antiques","inn","alloys","extrusions","recruitment","maze","sandwich","all","seasons","jazz","weather","carriage","dealers","stores","fairs","fruiterers","butchers","solrs","solicitors","acoustics","accountants","haulage","survy","surveyors","lighting","gospel","disease","insulation","patisserie","mining","amateur","amazon","security","ambassador","snooker","pool","adhesive","Wessex","clinic","wheels","garage","fabrics","express","autos","housing","inn","beautiful","maintenance","opticians","diesel","hire","co","gallery","photography","print","tea","coffee","tackle","store","American","world","continental","educational","roofing","group","sanctuary","beauty","minimarket"}&" "," "&b1&" "))),"YES","NO")"
Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$10000").AutoFilter Field : = 1, Criteria1 : = "YES"
Rows("x:y").Select ;all row which is YES
Selection.Delete Shift : = xlUp
ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field : = 1
Columns("A:A").Select
Selection.ClearContents
Range("A1:A100000").Select ;I just made up the range further notes at the end
Selection.FormulaR1C1 = "=IF(OR(ISNUMBER(search(" "&{"market","pence","bazaar","shave","anorak","bakery","alarm","alarms","bros","applications","partnership","of","restaurant","courtyard","licenced","landscapes","travel","specialist","testing","relay","imaging","environmental","associates","nursing","technology","software","florist","limited","consultant","interiors","exteriors","plasterer","builder","carpenter","mouldings","holdings","vending","logistic","logistics","roofing","fitness","vetinaty","distributors","catalogue","showroom","service","enterprises","cadet","navy","information","centre","clinic","chiropractic","reserve","discount","jewellery","farm","farmers","rafters","pipeline","concrete","fabrications","marketing","performance","engines","bookmaker","peking","exhaust","disposal","guest","recruitment","agencies","estate","golf","panel","healthcare","NHS","estates","driving","clothing","sstems","emergency","gas","handling","hire","nursing","perforators","radio","television","computer","cabs","insulation","insulations","contacts","trust","aerials","satellite","tv","advertising","aquarius","partnership","partners","dental","laboratory","leisure","sound","vision","ear","nose","throat","electrical","advertising","needlecraft","licence","roadhouse","components","repair","products","bod","lynx","spares","mobile","trader","technician","bodywork","autoglass","windshield","unit","flat","units","fl","flats","apartmant","ap","Furnitures","Nursery","Music","Joiners","Fire","Protection","Fabracates","Properties","Out","Filling","Station","Fine","Cafe","ices","tourist","nurseries","lottery","guide","assoc","bus","cafeteria","infirmary","harbour","tarmacadam","fireworks","drafting","deslgn","design","axiom","pyrotenax","furnishing","before","and","after","Bengal","Tandoori","Takeaway","Motors","Drawer","TWAT","Property","Brigade","Advertiser","Quarry","Salon","Bridal","Dreams","Brighton","Selfdrive","Llmousines","Limousines","Distinctive","Furnishers","Motors","Catering","Railway","Produce","Bar","Upholstery","Cleaning","Tyres","Developments","Craft","Citizens","Advice","Bureaux","City","Royal","Royai","Bakeries","Bakery","Filling","Manufacturing","Autobuild","Remarks","Dalhousie","Nite","Chinese","Out","Diamond","Dimensions","Unisex","Dolphin","Fish","Chicken","Catering","Plumbing","Drain","Drainer","Partners","Partnrs","Superbikes","Parrish","CAMERAS","Foods","Food","Epicurus","Productions","Eyeline","Messrs","Home","Toyota","Pet","Bureau","Maternity","Kitchens","Bathrooms","Developments","Seed","Analysis","Guild","Cats","Cradle","Harlequin","Fancy","Dress","Patio","Slabs","Chauffeur","Charter","Highland","Scene","Chrtdarcht","Seafoods","Leader","Jehovah","Jehovahs","Witnesses","Fast","Kashmir","Kebab","Prop","Karriers","Keltic","Self","Drive","Miniature","Fruit","Bazzar","Kleenomat","Laundrette","Uphdlstery","Ser","Llces","Lounge","Brassiere","Lifeboat","Removals","Exchange","Overseas","Link","Kennels","Kennel","Grocer","Family","Fashion","Waves","Making","Boutique","Institute","Mechanics","Medical","Prescriptions","Only","Fellowship","Museum","Port","Authority","Infirmary","Supersavers","Joiners","Pumps","Animal","Feeds","Kong","Hong","New","To","you","suit","midwife","midwives","midwnes","nutters","pottery","and","about","crematorium","potatoes","paws","thought","chip","pets","pantry","handicrafts","dectr","pleasureland","motorcycles","pyramid"}&" "," "&b1&" "))),"YES","NO")"
Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field : = 1, Criteria1 : = "YES"
Rows("x:y").Select ;all row which is YES
Selection.ClearContents
ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field : = 1
Columns("A:A").Select
Selection.Delete Shift : = xlToLeft ;Delete the Whole column. The job is done, dont need more.

ActiveWorkbook.SaveAs Filename : = _
        "C:\Users\user\Documents\THEDOCUMENTNAME_cleaning in progress.xlsx" _ ;just a save as with adding to the name "_cleaning in progress"
        ;I used for Range A1:A100000 however every excel file contains different number of rows. If there is anyway just select where actually any data than I prefer to that otherwise if i select the whole column the program want to fil in more than a million cell. That is just killin' my i5 proc+8gb ram.

So yeah this is it... Any suggestion?

Share this post


Link to post
Share on other sites

Recording a macro is very static. Means: The recorded script does exactly what you have done when recording. To make it more dynamic you need to understand what was recorded and how Excel works.

Another problem is that the recorded script works with the GUI (selection etc.) which makes it rather slow.

If you want to use my Excfel UDF I suggest the following:

  1. Insert a New Column (should be B but doesnt really matter): _Excel_RangeInsert
  2. add a formula to the whole Column (Ctrl + Enter): _Excel_RangeWrite
  3. If the result is Yes DELETE the whole row which are yes (I using Kutools for Excel when i doing manually by hand): _Excel_RangeFilter and some manual processing with .EntireRow.Delete
  4. after clear the coumn: Nothing needed if the next step writes to the same column
  5. add a second formula: _Excel_RangeWrite
  6. if yes delete the whole rows again: Same as above
  7. after delete the whole column: _Excel_RangeDelete

Start with 1. and 2. If successfull I will have a look at 3.


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

#8 ·  Posted (edited)

Example:

#include <Excel Rewrite.au3>
Global $oExcel = _Excel_Open() ; Connects to Excel or starts a new instance
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\_Excel1.xls") ; Opens _Excel1 workbook in the directory where the script resides
_Excel_RangeInsert($oWorkbook.Activesheet, "B:B", $xlShiftToRight) ; Inserts a new column B and shifts the other columns to the right
_Excel_RangeWrite($oWorkbook, Default, "=MOD(A1;2)", "B:B", False) ; Inserts the formula to mark even row numbers with 0 
_Excel_FilterSet($oWorkbook, Default, "B:B", 1, "0") ; Only displays even row numbers
Edited by water

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

Another example. In addition all visible rows are deleted.

#include <Excel Rewrite.au3>
Global $aData[][] = [[" ", " "], [1, "1-2"], [2, "2-2"], [3, "3-2"], [4, "4-2"]]
Global $oExcel = _Excel_Open() ; Connect to Excel or open a new instance
Global $oWorkbook = _Excel_BookNew($oExcel) ; Create a new workbook
_Excel_RangeWrite($oWorkbook, Default, $aData, "A1") ; Write the array to the workbook
_Excel_RangeInsert($oWorkbook.Activesheet, "B:B", $xlShiftToRight) ; Insert column B and shift existing data to the right
_Excel_RangeWrite($oWorkbook, Default, "=Rest(A2;2)", "$B2:B" & $oWorkbook.Activesheet.UsedRange.Rows.Count, False) ; Insert the formula (modulo) in column B
_Excel_FilterSet($oWorkbook, Default, $oWorkbook.Activesheet.UsedRange, 2, "0") ; Only display even numbers
$oRange = $oWorkbook.Activesheet.UsedRange.SpecialCells($xlCellTypeVisible) ; Only select visible rows
$oRange.EntireRow.Delete ; delete visible rows

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

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

Dim $selector = """quality"",""st"",""rainbow"",""carryout"",""auto"",""surgery"",""public"",""fertiliser"",""Fertilisers"",""ifertilisersl"",""rendezvous"",""remember"",""that"",""precast"",""barefoot"",""mail"",""scaffolding"",""safe"",""access"",""salvation"",""army"",""funeral"",""directors"",""director"",""seahorse"",""simply"",""affordable"",""stich"",""time"",""dressmaking"",""alterations"",""hospital"",""by"",""jewellers"",""post"",""sweet"",""sensation"",""trophies"",""engraving"",""protection"",""produce"",""public"",""relations"",""made"",""fun"",""auctions"",""auction"",""kiltmakers"",""kiltmaker"",""linen"",""chippy"",""timber"",""tidy"",""tangles"",""past"",""dealer"",""trading"",""crafts"",""craft"",""naval"",""activities"",""US"",""Uncle"",""Diner"",""Removals"",""Volunteer"",""Zoo"",""Wavelength"",""Wavelengths"",""Friends"",""hairdressing"",""theatreclub"",""daynursery"",""MUSLC"",""superstore"",""colleges"",""AALCO"",""AARDVARK"",""ABACUS"",""ABB"",""ABBOTSWELL"",""ABBOTTPUBLIC"",""ABC"",""ABCON"",""ABDN"",""ABERCARE"",""ABERDEE"",""ABERDEEN"",""ABERDEENSHIRE"",""ABERFLORA"",""ABERGELDIE"",""ABERGLEN"",""ABERLOUR"",""ABERMED"",""ABERNE"",""ABERPEST"",""ABERSOL"",""ABERT"",""ABSCOT"",""ABSOFT"",""ABSPEK"",""ABSURE"",""ABTEX"",""ABTRUST"",""ACADEMY"",""ACCESS"",""ACCESSORIES"",""ACCOMMODATION"",""ACCORD"",""ACCOUNTANCY"",""ACCURAY"",""ACE"",""ACTION"",""ADAPT"",""ADEPT"",""ADMIN"",""ADMIRAL"",""ADS"",""ADULT"",""ADVANCE"",""ADVANCED"",""ADVANT"",""ADVANTAGE"",""ADVICE"",""AEA"",""AFFAIR"",""AFOS"",""AGA"",""AGE"",""AGENCY"",""AGIP"",""AGRI"",""AGRICULTURAL"",""AGRICULTURE"",""AHT"",""AI"",""AIDS"",""AIR"",""AIRBORNE"",""AIRFAIR"",""AIRLINES"",""AIRNAUTIC"",""AIRPAC"",""AIRPORT"",""AIRWAYS"",""AIRYHALL"",""AIRYLEA"",""AKRON"",""ALAD"",""ALASDAIR"",""ALB"",""ALBATECH"",""ALFA"",""ALL"",""ALLIANCE"",""ALLIED"",""ALLOMAX"",""ALPINE"",""ALSTEV"",""ALTERNATIVES"",""ALTRA"",""ALWAYS"",""ALZHEIMER"",""AM"",""AMALGAMATED"",""AMAT"",""AMATOLA"",""AMBER"",""AMEC"",""AMERICAN"",""AMETEK"",""AMIN"",""AMITEC"",""AMOCO"",""AMTECH"",""AMTRAK"",""AMUSEMENT"",""AND"",""ANDERGAUGE"",""ANIXTER"",""ANKA"",""ANTIQUES"",""ANTRIM"",""AOC"",""AP"",""APARDION"",""APEX"",""APOLLO"",""APOSTOLIC"",""APPEALS"",""APPLIED"",""APPOLLO"",""AQUA"",""AQUARISTS"",""AQUATEC"",""AQUATIC"",""AQUIDATA"",""ARCHITECTURAL"",""ARCO"",""ARGONAUT"",""ARGOSY"",""ARISTACUT"",""ARJO"",""ARJON"",""ARKAIG"",""ARTISTES"",""ARTISTS"",""ARTWORKS"",""ARUP"",""AS"",""ASA"",""ASCOT"",""ASDASUPERSTORES"",""ASHVALE"",""ASPECT"",""ASSEMBLIES"",""ASSESSORS"",""ASSOCIATED"",""ASSOCIATES"",""ASSOCIATION"",""ASSURANCE"",""ASTER"",""ASTRA"",""ATEL"",""ATHENA"",""ATHENAEUM"",""ATHOLL"",""ATLANTIC"",""AUCHMILL"",""AUCHTERLESS"",""AUGHTON"",""AUGUST"",""AUP"",""AUQUHARNEY"",""AURORA"",""AUTO"",""AUTOFIX"",""AUTOGLASS"",""AUTOMAGIC"",""AUTOQUICK"",""AUTOSAVE"",""AUTOSPRAY"",""AUTOTRUCK"",""AVCO"",""AVENUE"",""AWAKENING"",""AWAY"",""AXIOM"",""AZTEC"",""BAADER"",""BALNAGASK"",""BANKHEAD"",""BAR"",""BARICO"",""BARRIER"",""BARTERING"",""BASELINE"",""BATCHBRIGHT"",""BATH"",""BATHROOM"",""BAVARIA"",""BAYLISS"",""BAYLOR"",""BEARING"",""BEATTIES"",""BEAUTY"",""BEAVERBROOKS"",""BEECHWOOD"",""BEEFEATER"",""BEELINE"",""BEFRIEND"",""BENBOW"",""BESTWAY"",""BEWS"",""BIKES"",""BLIND"",""BLINDCRAFT"",""BLINDS"",""BLOOD"",""BLOOMING"",""BLOSSOMS"",""BLOW"",""BLUEBELLS"",""BMT"",""BNA"",""BOARD"",""BOARS"",""BOAT"",""BODY"",""BOHEMIA"",""BOILERMAKERS"",""BON"",""BONADDIO"",""BONAVENTURE"",""BONIVIEW"",""BONNYMUIR"",""BOOKBINDING"",""BOOTS"",""BOROWSKI"",""BORSALINO"",""BOSANQUET"",""BOSCH"",""BOTTLED"",""BOULEVARD"",""BOURTREE"",""BOWEN"",""BOWLING"",""BOWTECH"",""BOX"",""BP"",""BPCC"",""BRA"",""BRACKENRIDGE"",""BRAEHEAD"",""BRAESIDE"",""BRAKE"",""BRIEF"",""BRITANNIA"",""BRITANNIC"",""BROAD"",""BROADCAST"",""BROADSTRAIK"",""BROS"",""BROTHERHOOD"",""BRUNSWICK"",""BUCKSBURN"",""BUDGET"",""BUILDING"",""BULAWEYO"",""BUREAU"",""BURNTHILLS"",""BUS"",""BUSINESS"",""CABER"",""CABINS"",""CABLE"",""CABS"",""CAFE"",""CAIRD"",""CAIRNCRY"",""CAIRNGORM"",""CAKE"",""CALEDON"",""CALEDONIA"",""CALEDONIAN"",""CALLANDERS"",""CALOR"",""CALTEC"",""CALVARYTABERNACLEUNITED"",""CAMARGUE"",""CAMEO"",""CAMPAIGN"",""CAMPBELLS"",""CAMPHILL"",""CAMWATER"",""CAN"",""CANALE"",""CANCER"",""CANDO"",""CANSCO"",""CAPE"",""CARADON"",""CARD"",""CAREERS"",""CARGO"",""CARPET"",""CARPETS"""
Dim $brformula = "=IF(OR(ISNUMBER(search("" ""&{" & $selector & "}&"" "","" ""&b1&"" ""))),""YES"",""NO"")"
Global $aData[][] = [[$brformula], [2], [3], [4],[1], [2], [3], [4],[1], [2], [3], [4],[1] , [2], [3], [4]]
Global $oExcel = _Excel_Open() ; Connect to Excel or open a new instance
Global $oWorkbook = _Excel_BookNew($oExcel) ; Create a new workbook
_Excel_RangeInsert($oWorkbook.Activesheet, "A:A", $xlShiftToRight) ; Insert column B and shift existing data to the right
_Excel_RangeWrite($oWorkbook, Default, $aData, "A1") ; Write the array to the workbook
_Excel_RangeWrite($oWorkbook, Default, "=Rest(A2;2)", "$A2:A" & $oWorkbook.Activesheet.UsedRange.Rows.Count, False) ; Insert the formula (modulo) in column B
_Excel_FilterSet($oWorkbook, Default, $oWorkbook.Activesheet.UsedRange, 1, "1") ; Only display even numbers
Global $oRange = $oWorkbook.Activesheet.UsedRange.SpecialCells($xlCellTypeVisible) ; Only select visible rows
$oRange.EntireRow.Delete ; delete visible rows

whats wring with this? its executing but not doing anything only opens the excel... but if not put the [$brformula] to the $aData at least writeing the other numbers to the columns.

Share this post


Link to post
Share on other sites

My fault. Posted a wrong formula (german).

"=Rest(A2;2)"

needs to be the name of the modulo function

"=MOD(A2;2)"

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

#12 ·  Posted (edited)

its giving me error for this as well. ==> Variable used without being declared.:

Global $oRange = $oWorkbook.Activesheet.UsedRange.SpecialCells($xlCellTypeVisible)
Global $oRange = $oWorkbook.Activesheet.UsedRange.SpecialCells(^ ERROR


and still with the MOD its not writing nothing to excel. does my variables are correct?
 
edit: if just using simple texts its working but when i want to put the variable than nothing.
Edited by zsoltm

Share this post


Link to post
Share on other sites

I modified the Constants include file too. Add this line to your script:

Global Const $xlCellTypeVisible = 12 ; All visible cells
1 person likes this

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

Can you post a screenshot of the workbook after you manually entered the data?


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

a1_2013_10_16_rtrmrbe4ht_tn.png

a1_2013_10_16_t7ovot3mfw_tn.jpg

 

this is what I mean. of course when it will work correctly i want to change the settings to open already existing xls files. make an automatation when one is ready open the next one and start again ..if its possible

Share this post


Link to post
Share on other sites

The following script works fine here.

#include <Excel Rewrite.au3>

;Global $selector = """quality"",""st"",""rainbow"",""carryout"",""auto"",""surgery"",""public"",""fertiliser"",""Fertilisers"",""ifertilisersl"",""rendezvous"",""remember"",""that"",""precast"",""barefoot"",""mail"",""scaffolding"",""safe"",""access"",""salvation"",""army"",""funeral"",""directors"",""director"",""seahorse"",""simply"",""affordable"",""stich"",""time"",""dressmaking"",""alterations"",""hospital"",""by"",""jewellers"",""post"",""sweet"",""sensation"",""trophies"",""engraving"",""protection"",""produce"",""public"",""relations"",""made"",""fun"",""auctions"",""auction"",""kiltmakers"",""kiltmaker"",""linen"",""chippy"",""timber"",""tidy"",""tangles"",""past"",""dealer"",""trading"",""crafts"",""craft"",""naval"",""activities"",""US"",""Uncle"",""Diner"",""Removals"",""Volunteer"",""Zoo"",""Wavelength"",""Wavelengths"",""Friends"",""hairdressing"",""theatreclub"",""daynursery"",""MUSLC"",""superstore"",""colleges"",""AALCO"",""AARDVARK"",""ABACUS"",""ABB"",""ABBOTSWELL"",""ABBOTTPUBLIC"",""ABC"",""ABCON"",""ABDN"",""ABERCARE"",""ABERDEE"",""ABERDEEN"",""ABERDEENSHIRE"",""ABERFLORA"",""ABERGELDIE"",""ABERGLEN"",""ABERLOUR"",""ABERMED"",""ABERNE"",""ABERPEST"",""ABERSOL"",""ABERT"",""ABSCOT"",""ABSOFT"",""ABSPEK"",""ABSURE"",""ABTEX"",""ABTRUST"",""ACADEMY"",""ACCESS"",""ACCESSORIES"",""ACCOMMODATION"",""ACCORD"",""ACCOUNTANCY"",""ACCURAY"",""ACE"",""ACTION"",""ADAPT"",""ADEPT"",""ADMIN"",""ADMIRAL"",""ADS"",""ADULT"",""ADVANCE"",""ADVANCED"",""ADVANT"",""ADVANTAGE"",""ADVICE"",""AEA"",""AFFAIR"",""AFOS"",""AGA"",""AGE"",""AGENCY"",""AGIP"",""AGRI"",""AGRICULTURAL"",""AGRICULTURE"",""AHT"",""AI"",""AIDS"",""AIR"",""AIRBORNE"",""AIRFAIR"",""AIRLINES"",""AIRNAUTIC"",""AIRPAC"",""AIRPORT"",""AIRWAYS"",""AIRYHALL"",""AIRYLEA"",""AKRON"",""ALAD"",""ALASDAIR"",""ALB"",""ALBATECH"",""ALFA"",""ALL"",""ALLIANCE"",""ALLIED"",""ALLOMAX"",""ALPINE"",""ALSTEV"",""ALTERNATIVES"",""ALTRA"",""ALWAYS"",""ALZHEIMER"",""AM"",""AMALGAMATED"",""AMAT"",""AMATOLA"",""AMBER"",""AMEC"",""AMERICAN"",""AMETEK"",""AMIN"",""AMITEC"",""AMOCO"",""AMTECH"",""AMTRAK"",""AMUSEMENT"",""AND"",""ANDERGAUGE"",""ANIXTER"",""ANKA"",""ANTIQUES"",""ANTRIM"",""AOC"",""AP"",""APARDION"",""APEX"",""APOLLO"",""APOSTOLIC"",""APPEALS"",""APPLIED"",""APPOLLO"",""AQUA"",""AQUARISTS"",""AQUATEC"",""AQUATIC"",""AQUIDATA"",""ARCHITECTURAL"",""ARCO"",""ARGONAUT"",""ARGOSY"",""ARISTACUT"",""ARJO"",""ARJON"",""ARKAIG"",""ARTISTES"",""ARTISTS"",""ARTWORKS"",""ARUP"",""AS"",""ASA"",""ASCOT"",""ASDASUPERSTORES"",""ASHVALE"",""ASPECT"",""ASSEMBLIES"",""ASSESSORS"",""ASSOCIATED"",""ASSOCIATES"",""ASSOCIATION"",""ASSURANCE"",""ASTER"",""ASTRA"",""ATEL"",""ATHENA"",""ATHENAEUM"",""ATHOLL"",""ATLANTIC"",""AUCHMILL"",""AUCHTERLESS"",""AUGHTON"",""AUGUST"",""AUP"",""AUQUHARNEY"",""AURORA"",""AUTO"",""AUTOFIX"",""AUTOGLASS"",""AUTOMAGIC"",""AUTOQUICK"",""AUTOSAVE"",""AUTOSPRAY"",""AUTOTRUCK"",""AVCO"",""AVENUE"",""AWAKENING"",""AWAY"",""AXIOM"",""AZTEC"",""BAADER"",""BALNAGASK"",""BANKHEAD"",""BAR"",""BARICO"",""BARRIER"",""BARTERING"",""BASELINE"",""BATCHBRIGHT"",""BATH"",""BATHROOM"",""BAVARIA"",""BAYLISS"",""BAYLOR"",""BEARING"",""BEATTIES"",""BEAUTY"",""BEAVERBROOKS"",""BEECHWOOD"",""BEEFEATER"",""BEELINE"",""BEFRIEND"",""BENBOW"",""BESTWAY"",""BEWS"",""BIKES"",""BLIND"",""BLINDCRAFT"",""BLINDS"",""BLOOD"",""BLOOMING"",""BLOSSOMS"",""BLOW"",""BLUEBELLS"",""BMT"",""BNA"",""BOARD"",""BOARS"",""BOAT"",""BODY"",""BOHEMIA"",""BOILERMAKERS"",""BON"",""BONADDIO"",""BONAVENTURE"",""BONIVIEW"",""BONNYMUIR"",""BOOKBINDING"",""BOOTS"",""BOROWSKI"",""BORSALINO"",""BOSANQUET"",""BOSCH"",""BOTTLED"",""BOULEVARD"",""BOURTREE"",""BOWEN"",""BOWLING"",""BOWTECH"",""BOX"",""BP"",""BPCC"",""BRA"",""BRACKENRIDGE"",""BRAEHEAD"",""BRAESIDE"",""BRAKE"",""BRIEF"",""BRITANNIA"",""BRITANNIC"",""BROAD"",""BROADCAST"",""BROADSTRAIK"",""BROS"",""BROTHERHOOD"",""BRUNSWICK"",""BUCKSBURN"",""BUDGET"",""BUILDING"",""BULAWEYO"",""BUREAU"",""BURNTHILLS"",""BUS"",""BUSINESS"",""CABER"",""CABINS"",""CABLE"",""CABS"",""CAFE"",""CAIRD"",""CAIRNCRY"",""CAIRNGORM"",""CAKE"",""CALEDON"",""CALEDONIA"",""CALEDONIAN"",""CALLANDERS"",""CALOR"",""CALTEC"",""CALVARYTABERNACLEUNITED"",""CAMARGUE"",""CAMEO"",""CAMPAIGN"",""CAMPBELLS"",""CAMPHILL"",""CAMWATER"",""CAN"",""CANALE"",""CANCER"",""CANDO"",""CANSCO"",""CAPE"",""CARADON"",""CARD"",""CAREERS"",""CARGO"",""CARPET"",""CARPETS"""
;Global $brformula = "=IF(OR(ISNUMBER(search("" ""&{" & $selector & "}&"" "","" ""&b1&"" ""))),""YES"",""NO"")"
Global Const $xlCellTypeVisible = 12 ; All visible cells
Global $aData[] = [" ", 1, 2, 3, 4, 1, 2, 3, 4, 1, 2, 3, 4, 1, 2, 3, 4]
Global $oExcel = _Excel_Open() ; Connect to Excel or open a new instance
Global $oWorkbook = _Excel_BookNew($oExcel) ; Create a new workbook
_Excel_RangeWrite($oWorkbook, Default, $aData, "A1") ; Write the array to the workbook
MsgBox(0, "RangeWrite Data", @error)
_Excel_RangeWrite($oWorkbook, Default, "=Mod(A2;2)", "$B2:B" & $oWorkbook.Activesheet.UsedRange.Rows.Count, False) ; Insert the formula (modulo) in column B
MsgBox(0, "RangeWrite Formula", @error)
_Excel_FilterSet($oWorkbook, Default, $oWorkbook.Activesheet.UsedRange, 2, "1") ; Only display even numbers
MsgBox(0, "FilterSet", @error)
Global $oRange = $oWorkbook.Activesheet.UsedRange.SpecialCells($xlCellTypeVisible) ; Only select visible rows
MsgBox(0, "Visible cells", @error)
$oRange.EntireRow.Delete ; delete visible rows

Each MsgBox shows the result. Click OK to proceed to the next step.

1 person likes this

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

BTW: The definition of $selector doesn't work because the line is too long (>4096 characters).


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

#18 ·  Posted (edited)

FYI not even 4k characters just around 240~   :S i have 17k~ what a wanna do... arrays.
but with that works ...
next step is in the A column i just want to use my formula what I mean 
A1 checks B1 
A2 checks B2

A3 checks B3
... every row while we had filled every row  which are not empty.The other documents can be 5000 rows can be 800.000 rows

Global $brformula = "=IF(OR(ISNUMBER(search("" ""&{" & $selector & "}&"" "","" ""&b" & $i & "&"" ""))),""YES"",""NO"")"

For $i = 1 to (maximum row) Step 1

Edited by zsoltm

Share this post


Link to post
Share on other sites

In the Scite help i found the following code:
 

do
         local var, _limit, _step = tonumber(e1), tonumber(e2), tonumber(e3)
         if not (var and _limit and _step) then error() end
         while (_step>0 and var<=_limit) or (_step<=0 and var>=_limit) do
           block
           var = var + _step
         end
       end

but in the scite is looks like not recognize the end word .. 
I tried to do the following:

Do
    Local $i, 100, step = 1
    if not ( $i and 100 and 1) then error() EndFunc
    while (100>0 and $i<=100) or (100<=0 and $i>=100 do
_ExcelWriteFormula($oExcel, $sFormula, $sRangeOrRow, $iColumn = $i)
 $i = $i + 1
 WEnd
 End

Share this post


Link to post
Share on other sites

#20 ·  Posted (edited)

While you can definitely do this in AutoIt i would say, it would be alot easier to accomplish this using VBA in excel.

Not trying to diss autoit in anyway  :ILA2:  but for what you are wanting to accomplish, so much easier to do it with VBA especially with the macro record tool in Excel.

Then if you want it in a GUI in Autoit you can simply have an autoit import/call the macro within the excel spreadsheet.

Edited by 13lack13lade

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
Sign in to follow this  
Followers 0