noobin Posted December 24, 2019 Share Posted December 24, 2019 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 comment Share on other sites More sharing options...
Moderators Melba23 Posted December 24, 2019 Moderators Share Posted December 24, 2019 Moved to the appropriate forum. Moderation Team 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 columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
Nine Posted December 24, 2019 Share Posted December 24, 2019 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) ? “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy Link to comment Share on other sites More sharing options...
noobin Posted December 26, 2019 Author Share Posted December 26, 2019 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 comment Share on other sites More sharing options...
water Posted December 26, 2019 Share Posted December 26, 2019 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Nine Posted December 26, 2019 Share Posted December 26, 2019 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) “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy Link to comment Share on other sites More sharing options...
noobin Posted December 26, 2019 Author Share Posted December 26, 2019 Day of year and month (16-03-2020) Link to comment Share on other sites More sharing options...
noobin Posted December 26, 2019 Author Share Posted December 26, 2019 Thank you Nine, I am going to try it right away Link to comment Share on other sites More sharing options...
water Posted December 26, 2019 Share Posted December 26, 2019 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
noobin Posted December 26, 2019 Author Share Posted December 26, 2019 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. Link to comment Share on other sites More sharing options...
Nine Posted December 26, 2019 Share Posted December 26, 2019 At year end do you restart at B31 or do you continue further “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy Link to comment Share on other sites More sharing options...
noobin Posted December 26, 2019 Author Share Posted December 26, 2019 I will continue further Link to comment Share on other sites More sharing options...
Nine Posted December 26, 2019 Share Posted December 26, 2019 Ok, then my script works for you “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy Link to comment Share on other sites More sharing options...
noobin Posted December 26, 2019 Author Share Posted December 26, 2019 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? Link to comment Share on other sites More sharing options...
Nine Posted December 26, 2019 Share Posted December 26, 2019 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. “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy Link to comment Share on other sites More sharing options...
noobin Posted December 26, 2019 Author Share Posted December 26, 2019 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 Link to comment Share on other sites More sharing options...
BigDaddyO Posted December 26, 2019 Share Posted December 26, 2019 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 comment Share on other sites More sharing options...
noobin Posted December 27, 2019 Author Share Posted December 27, 2019 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now