Jump to content
TheDcoder

Trying to loop through every row in a Excel sheet

Recommended Posts

Hello :)
I am relatively new to the world of Microsoft Office and the Excel UDF.

I am trying to loop through every row in a spreadsheet and get the text/values from each column in the given row... so far I have looked into the Help file for the Excel UDF and the wiki page for Excel UDF but I have no idea about how this is done :(... This is all I have in my script:

Global $oExcel = _Excel_Open(False, False, False, False, True)
Global Const $sSpreadsheet = @ScriptDir & '\data.xlsx'
Global $oSpreadsheet = _Excel_BookOpen($oExcel, $sSpreadsheet, True, False)

; ...

I am placing my bet on the _Excel_Range functions... especially _Excel_RangeRead. I don't know how $vRange works so I would be glad if someone can point me in the right direction :D. What I would ideally like is to get all of the contents of the spreadsheet (it's just a normal text one) in a 2D array.

Thanks in Advance!

Edited by TheDcoder
Add tags to the thread

AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Spoiler

My contributions to the AutoIt Community

If I have hurt or offended you in anyway, Please accept my apologies, I never (regardless of the situation) mean to do that to anybody!!!

3fHNZJ.gif

PLEASE JOIN ##AutoIt AND HELP THE IRC AUTOIT COMMUNITY!

Share this post


Link to post
Share on other sites

You can use something like:

#include <Array.au3>
#include <Excel.au3>
Global $oExcel = _Excel_Open(False, False, False, False, True)
Global Const $sSpreadsheet = @ScriptDir & '\data.xlsx'
Global $oSpreadsheet = _Excel_BookOpen($oExcel, $sSpreadsheet, True, False)
Global $aSpreadsheet = _Excel_RangeRead($oSpreadsheet)
_ArrayDisplay($aSpreadsheet)

 

Share this post


Link to post
Share on other sites

Wow! :o
It was so easy! :thumbsup:
Also, if possible, is there way to exclude blank rows in the _Excel_RangeRead function?


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Spoiler

My contributions to the AutoIt Community

If I have hurt or offended you in anyway, Please accept my apologies, I never (regardless of the situation) mean to do that to anybody!!!

3fHNZJ.gif

PLEASE JOIN ##AutoIt AND HELP THE IRC AUTOIT COMMUNITY!

Share this post


Link to post
Share on other sites

No unfortunately, well not that I've ever seen without manipulating the document first, it's probably easier just to loop through the array and remove the empty rows.

Share this post


Link to post
Share on other sites

I see... Okay, thanks for the help :)


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Spoiler

My contributions to the AutoIt Community

If I have hurt or offended you in anyway, Please accept my apologies, I never (regardless of the situation) mean to do that to anybody!!!

3fHNZJ.gif

PLEASE JOIN ##AutoIt AND HELP THE IRC AUTOIT COMMUNITY!

Share this post


Link to post
Share on other sites

I came upon this function a while ago if you want to have a premade way of removing blanks:

Func _ArrayRemoveBlanks(ByRef $arr) ;self explanatory
  $idx = 0
  For $i = 0 To UBound($arr) - 1
    If $arr[$i] <> "" Then
      $arr[$idx] = $arr[$i]
      $idx += 1
    EndIf
  Next
  ReDim $arr[$idx]
EndFunc

Just makes it super simple, I copy this into my scripts whenever I need to work with arrays.


UHJvZmVzc2lvbmFsIENvbXB1dGVyZXI=

Share this post


Link to post
Share on other sites

@anthonyjr2 That's a strange way to remove blanks from an (1D) array... I wonder why it rewrites a row with the same contents? ($arr[$idx] = $arr[$i])


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Spoiler

My contributions to the AutoIt Community

If I have hurt or offended you in anyway, Please accept my apologies, I never (regardless of the situation) mean to do that to anybody!!!

3fHNZJ.gif

PLEASE JOIN ##AutoIt AND HELP THE IRC AUTOIT COMMUNITY!

Share this post


Link to post
Share on other sites

To be fair I never actually checked to see how it worked, I got it from here a long time ago:

But from looking at it now, what it's doing is going through and checking to see if each element is blank. If it isn't blank, it adds it to a new array. That way in the end the array will only contain non-blank elements. So that line is just putting the nonblank elements into a new array.


UHJvZmVzc2lvbmFsIENvbXB1dGVyZXI=

Share this post


Link to post
Share on other sites
5 minutes ago, anthonyjr2 said:

it adds it to a new array

I can only see one array and that is $arr ;)

5 minutes ago, anthonyjr2 said:

That way in the end the array will only contain non-blank elements.

No, what it's doing is using $idx to count all "non-blank" elements and and resizing/trimming the array using ReDim

Edited by TheDcoder

AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Spoiler

My contributions to the AutoIt Community

If I have hurt or offended you in anyway, Please accept my apologies, I never (regardless of the situation) mean to do that to anybody!!!

3fHNZJ.gif

PLEASE JOIN ##AutoIt AND HELP THE IRC AUTOIT COMMUNITY!

Share this post


Link to post
Share on other sites

@anthonyjr2 Your statement only makes it look smarter to be honest :D. There are several flaws... not to mention the obvious unnecessary rewriting of elements.... or that is what I thought until now. It actually moves all non-empty elements up, shifts all gaps to the bottom and then truncates the array. I have to admit, it's clever... but not very effective! Still does the job though, so I guess we can use it when feeling lazy :).


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Spoiler

My contributions to the AutoIt Community

If I have hurt or offended you in anyway, Please accept my apologies, I never (regardless of the situation) mean to do that to anybody!!!

3fHNZJ.gif

PLEASE JOIN ##AutoIt AND HELP THE IRC AUTOIT COMMUNITY!

Share this post


Link to post
Share on other sites

I suggest to loop through the array; write the line numbers of empty rows to a string so you get a valid range for _ArrayDelete; then call _ArrayDelete a single time passing the mentioned string.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

@water That is what I had in mind :).

I am urged to create another function which can remove all blank elements with the most efficient way to do it... but if I do that, I will be pushing my personal projects again... they have crossed the deadline a long time ago :(


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Spoiler

My contributions to the AutoIt Community

If I have hurt or offended you in anyway, Please accept my apologies, I never (regardless of the situation) mean to do that to anybody!!!

3fHNZJ.gif

PLEASE JOIN ##AutoIt AND HELP THE IRC AUTOIT COMMUNITY!

Share this post


Link to post
Share on other sites

Congrats :D


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

Yep, I can understand :).

P.S Congratulations on the 200th post


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Spoiler

My contributions to the AutoIt Community

If I have hurt or offended you in anyway, Please accept my apologies, I never (regardless of the situation) mean to do that to anybody!!!

3fHNZJ.gif

PLEASE JOIN ##AutoIt AND HELP THE IRC AUTOIT COMMUNITY!

Share this post


Link to post
Share on other sites

I think it depends on what the end result you want for example do you only want to remove blank rows or blank cells?  For example would you want to just remove Row 3 or all blanks and move them up?

|   A   |   B   |
|   A1  |   A1  |
|       |   A2  |
|       |       |
|   A4  |       |
|   A5  |   A5  |

Anyway I figured out how to do it within Excel and then export it to an Array, just need to comment/uncomment the Row or Cell section in the code to see both examples.

#include <Array.au3>
#include <Excel.au3>
Global Const $xlByRows = 1
Global Const $xlByColumns = 2
Global Const $xlPrevious = 2
Global Const $xlUp = -4162
Global $oExcel = _Excel_Open(False, False, False, False, True)
Global $sWorkbook = @ScriptDir & '\Filename.xlsx'
Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, True, False)

;~ #### Begin Remove All Blank Rows #### ~;
    Global $oEntireRow, $iLastRow = $oWorkbook.ActiveSheet.Cells.Find('*', $oWorkbook.ActiveSheet.Cells(1, 1), Default, Default, $xlByRows, $xlPrevious).Row
    For $i = $iLastRow To 1 Step - 1
        $oEntireRow = $oWorkbook.ActiveSheet.Cells($i, 1).EntireRow
        If $oExcel.WorksheetFunction.CountA($oEntireRow) = 0 Then $oEntireRow.Delete
    Next
;~ #### Begin Remove All Blank Rows #### ~;

;~ #### Begin Remove All Blank Cells #### ~;
;~  $oWorkbook.ActiveSheet.UsedRange.Cells.SpecialCells($xlCellTypeBlanks).Delete($xlUp)
;~ #### End Remove All Blank Cells #### ~;

Global $aWorkbook = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.UsedRange)
_Excel_BookClose($oWorkbook, False)
_Excel_Close($oExcel)

 _ArrayDisplay($aWorkbook)

 

Share this post


Link to post
Share on other sites

@Subz It works! I have no idea how... but it does work :D. Although I have noticed that it can take a while (around 5 seconds for me) for the code to filter all blank rows :)


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Spoiler

My contributions to the AutoIt Community

If I have hurt or offended you in anyway, Please accept my apologies, I never (regardless of the situation) mean to do that to anybody!!!

3fHNZJ.gif

PLEASE JOIN ##AutoIt AND HELP THE IRC AUTOIT COMMUNITY!

Share this post


Link to post
Share on other sites

instead of the loop you can use the specials as well (works especially well if you have to only check one column, should be similar for checking the whole row).  Would look something like:

#include <Array.au3>
#include <Excel.au3>
Global $oExcel = _Excel_Open(False, False, False, False, True)
Global Const $sSpreadsheet = @ScriptDir & '\filename.xlsx'
Global $oSpreadsheet = _Excel_BookOpen($oExcel, $sSpreadsheet, True, True)


$oExcel.activesheet.Range("A1:A10").SpecialCells($xlCellTypeBlanks).EntireRow.Delete = True


Global $aSpreadsheet = _Excel_RangeRead($oSpreadsheet , Default , Default , 1)
_Excel_BookClose($oSpreadsheet, FALSE)
_Excel_Close($oExcel , FALSE , TRUE)
_ArrayDisplay($aSpreadsheet)

 


,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites

@iamtheky That would work if you had a key column (with blank rows to show new range, see example below), but wouldn't work with the example I posted above, which is why I used the loop and the CountA function against each row (for deleting rows anyway).  Which is why it's slow to get a result,  As mentioned above it really depends on the workbook layout and also how you want the information returned.

|   A   |   B   |
|   A1  |   B1  |
|   A2  |   B2  |
|       |       |
|   A4  |   B4  |
|   A5  |       |
|   A6  |   B6  |
|       |       |
|   A8  |   B8  |

 

Share this post


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

  • Similar Content

    • By TheDcoder
      Hello everyone, I am working on a project which requires reading a few values from Excel, the catch is that I need it to be very fast... unfortunatley I found out that read operations using the supplied Excel UDF are very slow, more than 150 ms for each operation on average
      Here is my testing setup that I made:
      #include <Excel.au3> #include <MsgBoxConstants.au3> Global $iTotalTime = 0 Test() Func Test() Local $oExcel = _Excel_Open() Local $oBook = _Excel_BookAttach("Test.xlsx", "FileName", $oExcel) Local $sSheet = "Sheet1" If @error Then Return MsgBox($MB_ICONERROR, "Excel Failed", "Failed to attach to Excel") Local $iNum For $iRow = 1 To 6 Time() Local $iNum = Number(_Excel_RangeRead($oBook, $sSheet, "A" & $iRow)) If ($iNum = 1) Then ConsoleWrite("Row " & $iRow & " is 1 and value of column B is " & _Excel_RangeRead($oBook, $sSheet, "B" & $iRow)) Else ConsoleWrite("Row " & $iRow & " is not 1") EndIf ConsoleWrite(". Reading took: ") Time() Next ConsoleWrite("The whole operation took " & $iTotalTime & " milliseconds." & @CRLF) EndFunc Func Time() Local Static $hTimer Local Static $bRunning = False If $bRunning Then Local $iTime = Round(TimerDiff($hTimer), 2) $iTotalTime += $iTime ConsoleWrite($iTime & @CRLF) Else $hTimer = TimerInit() EndIf $bRunning = Not $bRunning EndFunc And Test.xlsx in CSV format:
      1,-1 -1,1 1,-1 1,1 -1,-1 1,1 Here is the actual xlsx but it should expire in a week: https://we.tl/t-EVkxGp1kc6
      And finally output from my script:
      Row 1 is 1 and value of column B is -1. Reading took: 276.06 Row 2 is not 1. Reading took: 163.36 Row 3 is 1 and value of column B is -1. Reading took: 302.58 Row 4 is 1 and value of column B is 1. Reading took: 294.65 Row 5 is not 1. Reading took: 152.33 Row 6 is 1 and value of column B is 1. Reading took: 284.92 The whole operation took 1473.9 milliseconds.  
      Taking ~1.5 seconds for reading 6 rows of data is bad for my script, which needs to run as fast as possible . It would be nice if I can bring this down to 100 ms somehow, I am not very experienced working with MS office so I thought about asking you folks for help and advice on how I can optimize my script to squeeze out every bit of performance that I can get from this script
       
      Thanks for the help in advance!
    • By SlackerAl
      I have been using some AutoIt scripts to manipulate Excel for a few weeks now. Today they stopped working. I have rebooted the PC and I'm not aware of any other significant changes. I can start and use Excel conventionally without a problem, but any attempt to create an excel object from AutoIt fails. E.g
      #include <MsgBoxConstants.au3> #include <Excel.au3> Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Open Example 1", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Produces:
      @error = 1
      @extended = -2147221005
      I can't find that COM error listed anywhere... Anyone have any ideas?
    • By Eggie6
      Hi,
      I'm trying to write an formula to the excel, but it actually does not write anything into the cell, any ideas?
      $formula1="=MID(B"&$utakmice&",SEARCH("-",B"&$utakmice&")-1,1)" _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $formula1, "C"&$smth)  
    • By XinYoung
      Hello all,
      Preface: Column A is full of courses. Column B is full of usernames. If course (A1) exists, we check to see if username (B1) is enrolled. If user is found, the user is un-enrolled. Then Loop.
      I am working on a script that loops through an Excel file and pastes the content from A1 into a textbox in IE, does some stuff, then pastes the content from B1 into a different textbox. Then it loops around until all used rows in columns A and B have been accounted for.
      For some reason, column A loops properly but column B doesn't. B1 is pasted over and over again. So, as it loops, B1 is constantly being pasted, first accompanying A1's loop, then A2, and so on.
      The OpenExcel() func opens the Excel file the user specifies in an earlier function. It's supposed to gather the entire used range of columns A and B. The SearchCourse() func only uses column A, pasting its content into a Search tool in IE. This seems to be working fine. SearchResult() puts "Course Not Found" into column C if the search fails. If the search is successful, however, we move onto... EnterCourse(). This simply gets us to the place where column B's content comes into play. UnenrollNow(). Here, we paste the variable $_userName into a textbox. I don't know why it's always B1  Func OpenExcel() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True) $oExcel.Sheets("CopyCourses").Activate ;~ Get all used cells in columns A and B $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B")) ;~ Create the $aSearchResult array ReDim $aSearchResult[UBound($aSearchItems)] ;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1) For $i = 0 To UBound($aSearchItems) - 1 ;~ Column 0 ConsoleWrite($aSearchItems[$i][0] & @CRLF) ;~ Column 1 ConsoleWrite($aSearchItems[$i][1] & @CRLF) $aSearchResult[$i] = SearchCourse($aSearchItems[$i][0], $aSearchItems[$i][1]) Next _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") ;~ When the loop is complete, run the Finished function. Finished() EndIf EndFunc ;==>OpenExcel Func SearchCourse($_sSearchResult, $_userName) If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else _IENavigate($oIE, $urlBBCourseSearch) _IELoadWait($oIE) Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch") Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText") _IEFormElementSetValue($oSearchString, $_sSearchResult) _IEFormSubmit($oForm) _IELoadWait($oIE) Local $oBBTable = _IETableGetCollection($oIE, 2) $aBBTableData = _IETableWriteToArray($oBBTable) Return SearchResult() EndIf EndFunc ;==>SearchCourse Func SearchResult() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $sResult $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0) If $iSearchIndex = -1 Then $sResult = "Course Not Found" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Else EnterCourse() $sResult = "UnEnrolled!" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") EndIf Return $sResult EndIf EndFunc ;==>SearchResult Func EnterCourse() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $clickFail = "DATE CREATED" _IELinkClickByIndex($oIE, 34) _IELoadWait($oIE) $sourceCode = _IEBodyReadHTML($oIE) If StringInStr($sourceCode, $clickFail) <> 0 Then _IELinkClickByIndex($oIE, 35) _IELoadWait($oIE) EndIf $oLink = _IEGetObjById($oIE, "controlpanel.users.and.groups_groupExpanderLink") _IEAction($oLink, "click") Sleep(500) _IELinkClickByText($oIE, "Users") _IELoadWait($oIE) $aSearchResult[$i] = UnenrollNow($aSearchItems[$i][0], $aSearchItems[$i][1]) EndIf EndFunc ;==>EnterCourse Func UnenrollNow($_sourceCourseId, $_userName) If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $UserError = "No users found" Local $sResult $criteriaUsername = _IEGetObjById($oIE, "userInfoSearchKeyString") _IEFormElementOptionSelect($criteriaUsername, 0, 1, 'byIndex') $criteriaUsername = _IEGetObjById($oIE, "userInfoSearchOperatorString") _IEFormElementOptionSelect($criteriaUsername, 0, 1, 'byIndex') ;Paste whats copied from column B into the Username text box. Local $oForm = _IEGetObjByName($oIE, "userManagerSearchForm") Local $oSearchString = _IEFormElementGetObjByName($oForm, "userInfoSearchText") ;PROBLEM HERE... _IEFormElementSetValue($oSearchString, $_userName) ;^^^^^^ WHY IS $_userName ALWAYS B1 ??? Sleep(1000) _IEFormSubmit($oForm) _IELoadWait($oIE) $sourceCode = _IEBodyReadHTML($oIE) If StringInStr($sourceCode, $UserError) <> 0 Then $sResult = "User Not Found" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Return $sResult Else MsgBox(0, "Unenrollment READY!", "We're ready to unenroll foreal") $sResult = "DUN" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Return $sResult EndIf EndIf EndFunc ;==>UnenrollNow Please let me know if any further information is needed.
      If you see other problems or redundancies in my code, please let me know.
      Thank you!
       
    • By SlackerAl
      I have an issue when starting Excel with the following code
      #include <Excel.au3> #include <GUIConstantsEx.au3> Opt("GUIOnEventMode", 1) GUICreate("Excel Test", 600, 440) GUISetOnEvent($GUI_EVENT_CLOSE, "MenuExit") GUISetState(@SW_SHOW) ; Create application object Local $oExcel = _Excel_Open() If @error Then Exit MsgBox(0, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ; sit here forever with an option to react every 10ms While 1 Sleep(10) WEnd Exit Func MenuExit() GUIDelete() Exit EndFunc If the Excel is a standard install, everything is OK. If Excel has the Kutools add-in (https://www.extendoffice.com/product/kutools-for-excel.html) installed and active the excel process runs (and is visible in task manager until killed), but it never displays. Disabling the add-in restores normal functionality.
      If I add some additional code to interact with the excel application then still nothing happens if the add-in is active. However, if Excel is started first and then the AutoIt code is run, it is able to interact with the Excel session as normal.
      Summary: The Excel add-in Kutools prevents excel being started with the _Excel_Open() command from AutoIt. Any AutoIt side work-arounds for this?
×
×
  • Create New...