Jump to content

Most efficient way to script to automate a long process?


Arcaklar
 Share

Recommended Posts

Complete Autoit newbie here. I was recommended by a friend to try using Autoit, which is why I'm here.

I'm on a personal project of sorts and have run into the need for a script that can perform the following tasks in a windows (xp) based environment.

PART 1

Obtaining info from database

Steps are:

0) run a program, and click on a certain option in a dropdown bar in the main window program

1) fill in a pre determined value in the resulting dialog box

2) click next

3) fill in different values in another dialog box (each different value corresponds to the first value filled in in 1)

4) click next

5) check a checkbox (the checkbox results in an export to excel)

6) press 'preview'

7) press save to, enter a specific location on the computer, enter a specific file name, and hit save

8) repeat with different,pre-determined values in step 1.

Then open the exported file(s) resulting from the above process in excel. There will be 8 different ones (since there are 8 different values to be filled in in step 1).

PART 2

In part 1, values in the exported files are listed in different rows under different labels. For example, it would be 'Animals' in one cell, and '2' in the corresponding cell next to it.

Here, I need to obtain the total of all the numerical values in cells next to cells labelled the same

-These totals are listed in a worksheet of a completely separate excel file (separate from the previous 8 in part 1) For simplicity's sake I will refer to this final file as the 'summary file'. I will refer to the 8 different files from part 1 as 'source files', also for simplicity.

So the source files essentially look something like:

Legend: Cell A / Cell B

SOURCE FILE 1:

Animal / 1

Animal / 2

Animal / 3

Seafood / 5

Seafood / 4

Seafood / 5

Grass / 8

SOURCE FILE 2

Lego / 3

Lego / 5

Bark / 8

Bark / 9

King / 3

A characteristic of the source files: There are no overlapping categories in each source file, i.e 'lego' will not appear in file 1 (or any other source file for that matter) and 'animals' will not appear in file 2 (or any other source files)

The thing is, 'animal' cannot be listed as 'animal' in the summary file. They must be listed as codes in the eventual file. The codes for each category for example look like:

Legend: Category / code

Animal / ANI001

Seafood / SEA002

Grass / GRA003

Lego / LEG004

Bark / BAR005

King / KIN006

{The codes are listed alongside their corresponding categories in the 'code file' (again named for simplicity's sake) which is also an excel file.}

The final summary file is also not in numerical order and have no clear pattern to them. Thus, the summary file will something like this:

Code / total

ANI001 / 6

SEA002 / 9

BAR005 / 17

GRA003 / 8

KIN006 / 3

LEG004 / 8

I thought of the following solution, but am looking for a simpler and more concise way (that preferably does not require pasting and deleting codes)

Create a script that will carry out the following actions in the summary file:

-Paste a column of the codes (GRA003, etc) to one empty row

-do an =SUMIF and paste a column of corresponding totals from each category to the row next to its corresponding code

-these two columns will constitute a table and must be placed in an empty area of the worksheet

Create a script to paste the exact number totals (cannot use excel's =IF function since that just pastes cell locations, i want the exact numerical value) in the table to the column next to the corresponding relevant codes in the final excel file.

I have no idea how to write such a script either, though, so help with that or relevant resources would be very appreciated.

Essentially, I am looking for the:

Most efficient way to script parts 1 and 2

Best and most efficient way to carry out the whole process (including the excel part)

How to run the script

Does anyone know of tutorials or useful resources that I can use to get started in this? straight instructions would do too, I have no idea where to begin so any help is appreciated.

Link to comment
Share on other sites

  • Moderators

Arcaklar,

Welcome to the AutoIt forum. :)

I would say that everything you want to do should be possible in AutoIt - I only say should because some apps use non-standard controls and so are difficult to automate. :)

I suggest you start by looking at ControlCommand and the other Control* functions in the Help file - that will help you do most of Part 1 of your project. ;)

And could you please let us know what sort of project this is? I have been racking my brains trying to think of a connection between Animal, Seafood, Grass, Lego, Bark & King and have come up a complete blank! :wacko:

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
Share on other sites

And could you please let us know what sort of project this is? I have been racking my brains trying to think of a connection between Animal, Seafood, Grass, Lego, Bark & King and have come up a complete blank! :wacko:

It's basically automation of a list of stock taking (and the corresponding supplying company that each good was supplied by). I just put lego/animal/seafood/grass as placeholders because I didn't reference the actual file or company names when typing this. (the actual company names are more like 'Kodak' and 'Sasa Cosmetics'.)

Link to comment
Share on other sites

  • Moderators

Arcaklar,

Thank you for explaining - my brain was beginning to overheat trying to come up with some connection! :D

Do come back if you have any specific questions. :)

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

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...