Jump to content

Autoit Excel read cells - (Moved)


Recommended Posts

Hello Everyone, I'm new here

Currently I'm trying to code a simple program that just reads a cell from Excel each day at a given time (always the same hour). What I'm trying to say is that each day it has to read the next cell of the column.

An examble would be: Day 1: B2, Day 2: B3, Day 3: B4, Day 4: B5 ...........

Does anyone knows if it is possible to automate this using Autoit?

Thank you

Link to post
Share on other sites
  • Moderators

Moved to the appropriate forum.

Moderation Team

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 post
Share on other sites
9 minutes ago, noobin said:

Does anyone knows if it is possible to automate this using Autoit?

Yes it is totally doable.  But there are few considerations to take account of :

1- What happened if the computer is restarted ?

2- Does it have to be running in background all the time ?

3- Is it working in hidden mode ?

4- What if the excel sheet is in use at that specific time ?

5- What is the time format to start the read (at second, at minute, at hour) ?

Link to post
Share on other sites

To be Honet I didn't really think about those questions, I was too focused on the excel part.

1- I didn't think about the restart of the computer, I guess that a final form would be a column with the date and do a range find everytime the app is started.

2- The program will be running in background all the time.

3- If you are asking about excel hidden mode, I think I will make it hidden. But not sure about this point

4- The excel sheet would not be opened

5- About the time format, i just need the hours and minutes

Link to post
Share on other sites

Please define "day". Is it day of month (1 - 28/29/30/31) or day of year (1 - 365/366)?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.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
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Link to post
Share on other sites

I think the best approach is to make a small autoit executable. Put that in task scheduler to start at the time you would like.  The script should read the cell specified in a fixed location in  the Excel spreadsheet.  Once you got the info, you treat it (you didn't say what) and finally write the new location.  You can close the script by then, no need to keep it running all the time. Something like this maybe :

#include <Excel.au3>

Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "", "Error creating the Excel application object.")
Local $sWorkbook = @ScriptDir & "\YourFileHere.xls"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "", "Error opening '" & $sWorkbook)

Local $sNewCellToRead = _Excel_RangeRead ($oWorkbook, 3, "A1") ; read the next cell to read (make a special place for it)
Local $sText = _Excel_RangeRead ($oWorkbook, 1, $sNewCellToRead) ; read the new cell B1, B2, B3, etc.
;do the thing you want with $sText
MsgBox ($MB_SYSTEMMODAL,"","Value in " & $sNewCellToRead & " is " & $sText)
$sNewCellToRead = StringLeft ($sNewCellToRead,1) & Int(StringMid ($sNewCellToRead,2))+1
_Excel_RangeWrite ($oWorkbook, 3, $sNewCellToRead, "A1")
_Excel_BookSave ($oWorkbook)
_Excel_Close ($oExcel)

 

Link to post
Share on other sites

Do you need to read cell B2 on 01/01 (day/month format), cell B32 on the 31/01 and cell B33 on the 01/02 and so on?
Or do you need to read the first cell on the day the script has been run for the first time?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.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
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Link to post
Share on other sites

It is the place where you're gonna put B31.  Make is somewhere it doesn't interfere with your spreadsheet.  It is just an example.  I decided to put it in A1 on the third sheet.

Link to post
Share on other sites

If you can use a column in that spreadsheet to mark that it was used it would be much easier to keep track.  you could then just read the entire spreadsheet into memory, and scroll through the array until you find one that isn't marked as done.  once you do use it through, you must update that row so it doesn't get used next time.

This example uses a spreadsheet with 2 columns:  A = value to use, B = UsedOnDate

#include <Excel.au3>

$sFileLoc = "C:\Temp\MyFile.xlsx"
$oXL = _Excel_Open()
$oXLBook = _Excel_BookOpen($oXL, $sFileLoc)

$aResult = _Excel_RangeRead($oXLBook, Default, $oXLBook.ActiveSheet.Usedrange.Columns("A:B"))   ;Read columns A & B

For $i = 0 to UBound($aResult) - 1
    If $aResult[$i][1] = "" Then ExitLoop   ;Found a record where the Column B doesnt have a used on date
Next

$sTextToUse = $aResult[$i][0]
ConsoleWrite("A" & $i + 1 & " Contains " & $sTextToUse & @CRLF)

;Do what you need with the $sTextToUse here



;Update the row we just used with todays date so it doesnt get picked up again
_Excel_RangeWrite($oXLBook, $oXLBook.Activesheet, @MON & "/" & @MDAY & "/" & @YEAR, "B" & $i + 1)
_Excel_BookSave($oXLBook)
_Excel_BookClose($oXLBook, True)                                                            ;Close the selected spreadsheet
_Excel_Close($oXL, True, True)                                                                  ;Close Excel

 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...