Autoit Excel read cells - (Moved)

[noobin]

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

[Melba23]

Moved to the appropriate forum.

Moderation Team

[Nine]

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

[noobin]

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

[water]

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

[Nine]

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)

 

[noobin]

Day of year and month (16-03-2020)

[noobin]

Thank you Nine, I am going to try it right away

[water]

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?

[noobin]

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.

[Nine]

At year end do you restart at B31 or do you continue further

[noobin]

I will continue further

 

[Nine]

Ok, then my script works for you

[noobin]

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?

 

[Nine]

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.

[noobin]

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

[BigDaddyO]

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

 

[noobin]

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