Jump to content
kcvinu

How to loop through all sheets in a workbook

Recommended Posts

Hi all, 

I would like to know how to loop through all sheets in an excel workbook.

Thanks in advance.


My Contributions

UDF Link Viewer   --- A tool to visit the links of some most important UDFs 

 Includer_2  ----- A tool to type the #include statement automatically 

 Digits To Date  ----- date from 3 integer values

PrintList ----- prints arrays into console for testing.

 Alert  ------ An alternative for MsgBox 

 MousePosition ------- A simple tooltip display of mouse position

GRM Helper -------- A littile tool to help writing code with GUIRegisterMsg function

Access_UDF  -------- An UDF for working with access database files. (.*accdb only)

 

Share this post


Link to post
Share on other sites

Have you tried coding anything? If so, please post so that we may have a better idea of how to help you.


Snips & Scripts


My Snips: graphCPUTemp ~ getENVvars
My Scripts: Short-Order Encrypter - message and file encryption V1.6.1 ~ AuPad - Notepad written entirely in AutoIt V1.9.4

Feel free to use any of my code for your own use.                                                                                                                                                           Forum FAQ

 

Share this post


Link to post
Share on other sites

@Mikah , No. I have not tried yet. Because i have no idea. But i am playing with range read and range find functions. 


My Contributions

UDF Link Viewer   --- A tool to visit the links of some most important UDFs 

 Includer_2  ----- A tool to type the #include statement automatically 

 Digits To Date  ----- date from 3 integer values

PrintList ----- prints arrays into console for testing.

 Alert  ------ An alternative for MsgBox 

 MousePosition ------- A simple tooltip display of mouse position

GRM Helper -------- A littile tool to help writing code with GUIRegisterMsg function

Access_UDF  -------- An UDF for working with access database files. (.*accdb only)

 

Share this post


Link to post
Share on other sites

Have a look at _Excel_SheetList


Snips & Scripts


My Snips: graphCPUTemp ~ getENVvars
My Scripts: Short-Order Encrypter - message and file encryption V1.6.1 ~ AuPad - Notepad written entirely in AutoIt V1.9.4

Feel free to use any of my code for your own use.                                                                                                                                                           Forum FAQ

 

Share this post


Link to post
Share on other sites

Loop through them and do what? Your question is clear as mud.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

Hi, I have found the answer by myself.

Local $SheetCount = $WBook.Worksheets.Count
for $i = 1 to $SheetCount
    Local $CellValue = _Excel_RangeRead($WBook,$WBook.Worksheets($i),"B" & $CellNum[0])
    $OutArray[$i] = $CellValue
    
Next

 

 


My Contributions

UDF Link Viewer   --- A tool to visit the links of some most important UDFs 

 Includer_2  ----- A tool to type the #include statement automatically 

 Digits To Date  ----- date from 3 integer values

PrintList ----- prints arrays into console for testing.

 Alert  ------ An alternative for MsgBox 

 MousePosition ------- A simple tooltip display of mouse position

GRM Helper -------- A littile tool to help writing code with GUIRegisterMsg function

Access_UDF  -------- An UDF for working with access database files. (.*accdb only)

 

Share this post


Link to post
Share on other sites

That's one possible way to solve the problem ...


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-02-09 - Version 1.4.1.0) - Download - General Help & Support - Example Scripts - Wiki
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 (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

@MikahS , I think _ExcelSheetList is best for me. Thank you.


My Contributions

UDF Link Viewer   --- A tool to visit the links of some most important UDFs 

 Includer_2  ----- A tool to type the #include statement automatically 

 Digits To Date  ----- date from 3 integer values

PrintList ----- prints arrays into console for testing.

 Alert  ------ An alternative for MsgBox 

 MousePosition ------- A simple tooltip display of mouse position

GRM Helper -------- A littile tool to help writing code with GUIRegisterMsg function

Access_UDF  -------- An UDF for working with access database files. (.*accdb only)

 

Share this post


Link to post
Share on other sites

My pleasure.


Snips & Scripts


My Snips: graphCPUTemp ~ getENVvars
My Scripts: Short-Order Encrypter - message and file encryption V1.6.1 ~ AuPad - Notepad written entirely in AutoIt V1.9.4

Feel free to use any of my code for your own use.                                                                                                                                                           Forum FAQ

 

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 rdaneelol
      I have a strange behavior in an Autoit program.  
      The program works perfectly in the original environment I created the program for - for Windows 7 and Office  2010.
      My workplace is migrating to Windows 10 with Office 2016.  When I run this program in that new environment,  the code actually executes without any errors, however, the excel process which was opened to read a spreadsheet/workbook does not close while the program is running.  If you exit the program, the excel process ends at that point...
      I could ignore this behavior - one little excel process hanging out there is not going to kill anything, however - it just bugs me !
      While troubleshooting the issue, I placed a number of error checks after the excel close - and the close actually reports that it is successful. 
      I tried a few varieties of closing the excel process, and none of the methods tried seemed to actually work as well at the one in my code - the only downside being that it has to wait until the program finishes to actually close.
      Any ideas on why an excel close would hang until program exit ?
       
      Local $oExcel = _Excel_Open(False, False, False, False, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, $sUserRoleMenuXLS, False, true ) If @error Then     MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error opening workbook '" & $sUserRoleMenuXLS & @CRLF & "@error = " & @error & ", @extended = " & @extended)     _Excel_Close($oExcel, False, False)  Exit EndIf ;  this section will find the user id in the first 3 columns of the user/menu spreadsheet, and if it finds it - returns the value stored in the cell 2 locations to the right... With $oExcel.ActiveSheet.Range("A:C").Find (@UserName)     $Match = .Find (@UserName)     If (Not IsObj($Match)) or (stringlen($Match.Offset(0, 2).value) = 0)    Then         MsgBox($MB_SYSTEMMODAL, 'UIPLauncher Error', 'No Menu assigned for user - Contact Support.') ; Display a warning if the script isn't compiled or the file doesn't exist.         ;_Excel_BookClose($oWorkbook, False)         _Excel_Close($oExcel, false, false)         Exit     Else         Local  $cResult = $Match.Offset(0, 2).value     EndIf EndWith $oWorkbook.Saved = True _Excel_BookClose($oWorkbook, False) _Excel_Close($oExcel, false, false) ;close does not seem to work on windows 10 and office 2016  
    • By VinMe
      $aTdslist = _Excel_RangeRead($oWorkbook1, Default, "B1:B" & $LastRow) $aTDSLOC = _Excel_RangeRead($oWorkbook1, Default, "H1:H" & $LastRow) MsgBox(0, "TAKSY OUTPUT LAST ROW", $LastRow) _ArrayTrim($aTDSLOC, 1, 1) _Excel_RangeWrite($oWorkbook1, $oWorkbook1.ActiveSheet, $aTDSLOC, "H2") $oWorkbook1.Sheets("DS_List").Activate $mLastRow = $oWorkbook1.ActiveSheet.UsedRange.Rows.Count $aMdslist = _Excel_RangeRead($oWorkbook1, Default, "B1:B" & $mLastRow) MsgBox(0, "MASTER OUTPUT LAST ROW", $mLastRow) _Excel_RangeReplace($oWorkbook1, Default, "F2:F" & $mLastRow, "N/A", "n/a") ;check the requiremetn $aMLOC = _Excel_RangeRead($oWorkbook1, Default, "F1:F" & $mLastRow) ;LOC NUMBER NEEDS TO BE REPLACED WITH.. _ArrayDisplay($aTDSLOC) _ArrayDisplay($aMLOC) _ArrayDisplay($aMdslist) ;DATA COMPARISION BETWEEN TAKSY LIST WITH LOC FROM SAP TO MASTER LIST WITH LOC. $oWorkbook1.Sheets("OUTPUT").Activate For $i = 1 To $LastRow - 1 For $j = 1 To $mLastRow - 1 If $aTdslist[$i] == $aMdslist[$j] And $aTDSLOC[$i] <> $aMLOC[$j] Then $oExcel.ActiveSheet.Range("H" & $i + 2).Interior.ColorIndex = 3 EndIf If $aTdslist[$i] == $aMdslist[$j] And $aMLOC[$j] == "n/a" And _Excel_RangeRead($oWorkbook1, Default, "I" & ($i + 2)) == "NO KIT" Then $oExcel.ActiveSheet.Range("H" & $i + 2).Interior.ColorIndex = 2 _Excel_RangeWrite($oWorkbook1, $oWorkbook1.ActiveSheet, "*", "H" & ($i + 2)) EndIf Next Next MsgBox($MB_SYSTEMMODAL, "Status", "Loc extraction, comparision and consolidation is Done", 2) DEAR TEAM, I am facing issue w.r.t subscript errors kindly help me in solving...
      456.xlsx
    • By VinMe
      $oWorkbook = _Excel_BookAttach($sWorkbook) $oWorkbook.sheets("Sheet1").activate Local $sMSN = InputBox("MSN NO", "Enter MSN in XX FORMAT", "") _Excel_FilterSet($oWorkbook, $oWorkbook.activesheet, "AF1", 32, $sMSN) can anyone help me here, excel filer is not working with the above code.. attached excel
      input: 12
      Thank you in advance!!
      to check.xlsx
    • By Rskm
      Hi, I am using excel as input media for my program. The excel file (i tried with .xls, .xlsx and .xlsm format) has inputs which the autoit script reads during the run and performs few calculations. Some times (not always), after the run, when i try to open the excel file manually, the file doesnt open at all in excel. see the screenshot attached. However, if the execute the autoit script, the scripts still reads the existing data from that excel and performs the calcs. I copied the excel file to another computer and there too, it doesnt open.  So, after this, i cannot edit the excel forever (if i need to change any inputs). It is only this particular file that got affected. other excel files works normal.  What could be the problem here.  please help as this is a new challenge for me during my program development. 

    • By Taxyo
      Hi,
       
      I've been trying to automate modification of an excel file and the last thing I am stuck on is deleting all the rows where the value of Column 13 is 0. 
      I believe the error is due to me not fully understanding the syntax so this is where I'm stuck: 
       
      Func Hotkey2() Global $aUsedRange = _Excel_RangeRead($oWorkbook, 1) _ArrayDisplay($aUsedRange) For $iRow = UBound($aUsedRange) - 1 to 3 Step -1 If $aUsedRange[$iRow][13] = 0 Then _Excel_RangeDelete($oWorkbook.Worksheets(1), $aUsedRange[$iRow] & ":" & $aUsedRange[$iRow], default, 1) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example 2", "Error deleting rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) EndIf Next EndFunc  
      While my script properly locates the row which contains value 0 in Column 13, I am not sure how to set it to the corresponding row in the excel workbook?  My above experiment gives me $vRange error and I've been toying around with it to no avail. The only way I get the Script to delete a row is by actually specifying "4:4" or "6:8" etc. 
      Where am I going wrong?
       
      Thanks! 
×
×
  • Create New...