Jump to content

Recommended Posts

Posted

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

  • Moderators
Posted

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

 

Posted
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) ?

Posted

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

Posted

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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.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
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted

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)

 

Posted

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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.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
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted

I need to read cell B31 on 01/01, cell B32 on 02/01and B33 on 03/01.

It's just a way to reduce mistakes at work, At a certain hour I have to write into a program some names, so to avoid errors I'm trying to automate it.

Posted

Sorry Nine i don't understand theis part of the code : Local $sNewCellToRead = _Excel_RangeRead ($oWorkbook, 3, "A1")? ; read the next cell to read (make a special place for it)

at this line is the programm suposed to read cell A1?

 

Posted

Thank you, I have been studying autoit for 2 months and i'm not really good at it, I'm having some troubles with your code, but I will figure it out. 

Thank you Nine

Posted

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

 

Posted

Thank you BigDaddyO, I cant't use the code because of the subscripts range exceed, but it helped me a lot and you gave me some interesting ideas, so thank you very much ;D

 

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