Search the Community

Hi, In help file, function return @error = 2 if $sSearch is empty. So If I want to search empty cell, I can't use AutoIt functions and I must check all sheet cells values ?

Hello, I face an Excel related error right after doing an _Excel_BookOpen. This is an EXCEL worksheet with filters defined, quite simple sheet. Once a week at Friday there is a scheduled task running on the file server "printing" the content to a PDF file for documentation puposes. As this is running 100% unattended it's a show stopper, if such dialog boxes show up. Already when I did that script a month ago I faced the issue, that to dialog boxes showed up telling something about a "name conflict", once for "_FilterDatabase", and a 2nd time for "PrintingArea" (maybe _PrintingArea). When some new name is entered, the script is going on with the PDF creation. But the autoit script is hanging with the _Excel_BookOpen, so I would need to start a 2nd. script to look for such bogus name conflict message boxes. While trying do track down what's going on in detail, the issue vanished again, I answerd the two boxes with "xxxx" and "yyyy" for new fiel names, the file seems to have been saved by me myself without intention (or automatically by EXCEL.AU3?) several other postings point into the direction of "this is an Excel Bug", seems to be not strictly Autoit related. Facts: Windows 7 Pro x64 Office 2010 SP2 32bit Localization = German (Win & Office) Autoit v3.3.14.5 Excel Workbook with three sheets without any Macros: "TBx Projektliste.xlsx", just 82 kByte Export-Excel-to-PDF-Projektstatus.au3 Any suggestions howto take care, that these "name conflicts" cannot occure?

I have a table that'd I'd like to lookup things in my script based on the input. Searching this forum I found some old posts about _ExcelReadSheetToArray(). After not getting it to work, I realized that is no longer in the UDF and _Excel_Range_Read is to be used instead. I also read the _Excel_RangeFind is another method to lookup data in a table. This method seems to use Excel to run these functions, whereas _Excel_Range_Read loads the entire range as an array and autoit does the work. I'm looking at loading a 30k row csv with 3 columns. Does anyone know if either this methods are better with this amount of data? Pros/cons? I'm leaning towards the _Excel_RangeFind so Excel can just run in the background and be the "database" vs. my script holding all that data in a massive array. Or maybe there's a completely different method? Let me know your thoughts!

Hello everyone, my problem is that when I try to run my already compiled program as administrator I get this error, but when I run the script it works correctly. this is my code #include <MsgBoxConstants.au3> #include <Excel.au3> ;I use this code to not open a new excel file Global $sWorkbook = @ScriptDir & "\facturacion.xlsx" Global $oWorkbook = _Excel_BookAttach($sWorkbook) ;this line of code is the problem $num = $oWorkBook.ActiveSheet.Range("C2").value MsgBox(0,0, $num) ;this line of code is the problem $num = $oWorkBook.ActiveSheet.Range("C2").value Is there another way to obtain the data from a cell in an Excel file? Thanks for your time 😊

Hi Fellow Automators, Long time listener, first time caller. I've resisted posting on the forums as long as possible for fear of public lynching, but I'm stuck and could really use some help. Note: If this is the wrong side of the forum for this topic, I apologize. I've built a GUI and script to make our lab data collection easier. The app has a number of input boxes and a 'record' button. The user fills out the input boxes with various notes and then presses 'record', which in turn presses 'record' on two other, separate apps simultaneously, pulls those recordings together into one folder, and then takes the text from the input boxes and adds it in a new row at the bottom of an existing .xlsx spreadsheet. Everything works great, except that every time I open the app to start collecting data for the day, the 'headers' for the $aArray are added to a new row and then the text is added below it. Now, if I don't close the app between collections, subsequent 'recordings' are added to the spreadsheet as expected. If I close the app and open it, the 'first' recording of the day adds the headers to a new row. I don't need new 'header' info because I've already got that in row 1 of the spreadsheet. If someone could tell me where I'm !#$%ing up, I would greatly appreciate it. #RequireAdmin #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <MsgBoxConstants.au3> #include <WindowsConstants.au3> #include <String.au3> #include <Process.au3> #include <FileConstants.au3> #include <WinAPIFiles.au3> #include <DirConstants.au3> #include <Array.au3> #include <AutoItConstants.au3> #include <File.au3> #include <WinAPIShPath.au3> #include <Excel.au3> ;Declaring the $aArray and location of the speadsheet at the top of the script Local $aArray[1][9] = [["TestID","DateTimeStamp","Tamb_C","BGTemp_C", "GasType", "TrueFlow_slm", "Lens-BGDist_in", "Lens-LeakDist_In", "AddNotes"]] Local $sDataFilePath = @ScriptDir & "\Notes\DualCaptureNotes.xlsx" Func CaptureVideo() Global $TestID = GuiCtrlRead($TestIDInput) Global $timestamp = @YEAR & "-" & @MON & "-" & @MDAY & "-" & @HOUR & "-" & @MIN & "-" & @SEC Global $Cam1Dir = "C:\Archive" Global $Cam2Dir = "C:\ALD" ;Capture Cam1 Data WinActivate("Cam1 App") ControlFocus("Cam1 App", "Save",'WindowsForms10.Window.8.app.0.2099316_r7_ad114') ControlSend("Cam1 App", "Save", 'WindowsForms10.Window.8.app.0.2099316_r7_ad114', "{SPACE}") ;Capture Cam2 Data WinActivate("Cam2 App") ControlClick("Cam2 App", "", 'WindowsForms10.Window.8.app.0.1b0ed41_r7_ad122', '', 1, 10, 10) ;Wait to ensure data files have been fully written to their default locations Sleep(2000) ;Self-explanatorily named functions MoveData() RecordNotesToArray() RecordArraytoExcel() WinActivate("DualCapture") EndFunc ;==>CaptureVideo ;Skipping ahead to the .xlsx part.... Func RecordNotesToArray() _ArrayAdd($aArray, GUICtrlRead($TestIDInput) & "|" & $timestamp & "|" & GUICtrlRead($Tamb_CInput) & "|" & GUICtrlRead($BGTemp_CInput) & "|" & GUICtrlRead($GasTypeInput) & "|" & GUICtrlRead($TrueFlow_slmInput) & "|" & GUICtrlRead($Dist_BG_inInput) & "|" & GUICtrlRead($Dist_Leak_inInput) & "|" & GUICtrlRead($AddNotesInput)) EndFunc ;==>RecordNotesToArray() Func RecordArraytoExcel() Local $oExcel = _Excel_Open() Local $oWorkBook If Not FileExists($sDataFilePath) Then $oWorkBook = _Excel_BookNew($oExcel) Else $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath) EndIf $oWorkBook.Worksheets("DataTable").Columns("A:I").AutoFit $LastRow = $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row $Rowrange = "A"&$LastRow+1 Consolewrite($Rowrange & @crlf) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray, $Rowrange) If FileExists($sDataFilePath) Then _Excel_BookSave($oWorkBook) Else _Excel_BookSaveAs($oWorkBook, $sDataFilePath) EndIf _Excel_BookClose($oWorkBook) _Excel_Close($oExcel) EndFunc ;==>RecordArrayToExcel() I appreciate your time and any help you can provide. Best, Johnny

Hi, I am looking for the possibility to extract a xls (not xlsx)-Excelfile to a csv . My problem: I do NOT have any Appliaction from MS-OFFICE. All searchings (since 3 days now) allways wants me to install ms-office or excel. I CAN'T DO THIS !!! Any help?! Ths'x alot

The _XLSXReadToArray Fuction Reads the EXCEL XLSX Sheet into an Array, not needed that Office is installed and do not use Object just use the REGEXP so_XLSXReadToArray works very fast, in a test done by me here with BulletinSearch_20121008_170143.xlsx (1,5 Mb, Rows = 12148 & Column = 15), the _XLSXReadToArray Return Array in 10 seconds, instead to Excel 2013 them have served more than 20 seconds to open the file ect ect, Normally should not happen ever of not finding the "[Content_Types].xml" or other files mentioned in "[Content_Types].xml", but if anything happens already Added the >_FileListToArrayEx to try to search the *.xml files in "Temp Extract Diretory" #include-once ; #UDF# ======================================================================================================================= ; Title .........: XLSX Read To Array ; AutoIt Version : 3.3.8.1 ; Language ......: English ; Description ...: Fuction Reads the EXCEL XLSX Sheet into an Array ; Author(s) .....: DXRW4E ; Notes .........: ; =============================================================================================================================== ; #CURRENT# ===================================================================================================================== ;~ _XLSXReadToArray ;~ _XLSXSheetGetColumnNumber ;~ _SSNToDate ;~ _DateToSSN ;~ _FileListToArrayEx ; =============================================================================================================================== If Not ObjEvent("AutoIt.Error") Then Global Const $_XLSXZip_COMErrorFunc = ObjEvent("AutoIt.Error", "_XLSXZip_COMErrorFunc") Global $DateSSN[27] = [0, 31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0, 31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335] ; #FUNCTION# ================================================================================================================================ ; Name...........: _XLSXReadToArray ; Description ...: The _XLSXReadToArray Fuction Reads the EXCEL XLSX Sheet into an Array ; Syntax.........: _XLSXReadToArray($XLSXFilePath, $iFlag) ; Parameters ....: $XLSXFilePath - Path and filename of the XLSX file to be read. ; $iFlag - Optional ; |$iFlag = 0 (Default) None ; |$iFlag = 1 if there are also add HyperLinks in the Array ; Strings\Test in Column\Rows will separate from HyperLink by @LF, example "Value" & @LF & http://www.autoitscript.com/forum/ ; |$Cols - Optional, Columns Number to Read (read only X column) ; |$Rows - Optional, Rows Number to Read (read only X Row) ; |$iSheet - Optional, Number of Sheet*.xml to Read, Default is 1 ; Return values .: Success - Return ; Array ($Array[0][0] = Rows Number & @Extended = Column Nmmber) ; If Set $Cols and $Rows Return is String Data, if Return Strigs = "" @Extended is Set to 1 ; @Error - Set Error ; |1 = XLSX file not found or invalid (Can not Read\Extract XLSX file) ; |2 = [Content_Types].xml not found or invalid ; |3 = sheet1.xml file not found or invalid ; |4 = Sheet Dimension Not found Or is Greater than 15999999 (Array Size Limit) ; |5 = No SheetDate (Columns & Rows) Found ; Author ........: DXRW4E ; Modified.......: ; Remarks .......: ; Related .......: ; Link ..........: ; Example .......: __XLSXReadToArray("C:\file.xlsx") ; Note ..........: ; =========================================================================================================================================== Func _XLSXReadToArray($XLSXFilePath, $iFlag = 0, $Cols = 0, $Rows = 0, $iSheet = 1) If ($Cols * ($Rows + 1)) > 15999999 Then Return SetError(4, 0, "") Local $XLSXExtractDir = @WindowsDir & "\Temp\XLSX_" & @YEAR & @MON & @MDAY & @HOUR & @MIN & @SEC & @MSEC, $XLSXZip, $oShell, $X $XLSXZip = FileCopy($XLSXFilePath, $XLSXExtractDir & "\__xlsx.zip", 9) If Not $XLSXZip Then Return SetError(1, DirRemove($XLSXExtractDir, 1), "") $oShell = ObjCreate("shell.application") $oShell.Namespace($XLSXExtractDir).CopyHere($oShell.Namespace($XLSXExtractDir & "\__xlsx.zip").items, 20) Local $ContentTypesXML = StringReplace(FileRead($XLSXExtractDir & "\[Content_Types].xml"), "/", "\", 0, 1) If Not $ContentTypesXML Then $ContentTypesXML = _FileListToArrayEx($XLSXExtractDir, "*Content*Types*.xml", 37) If Not @Error Then $ContentTypesXML = StringReplace(FileRead($XLSXExtractDir & "\" & $ContentTypesXML[0]), "/", "\", 0, 1) If Not $ContentTypesXML Then Return SetError(2, DirRemove($XLSXExtractDir, 1), "") EndIf Local $SharedStringsXMLPath = StringRegExp($ContentTypesXML, '(?si)<Override\s*PartName="([^"]*\\sharedStrings\.xml)"', 1) If @Error Then $SharedStringsXMLPath = _FileListToArrayEx($XLSXExtractDir, "sharedStrings.xml", 165) If Not @Error Then $SharedStringsXMLPath = $XLSXExtractDir & $SharedStringsXMLPath[0] Local $SheetXMLPath = StringRegExp($ContentTypesXML, '(?si)<Override\s*PartName="([^"]*\\sheet' & $iSheet & '\.xml)"', 1) If @Error Then $SheetXMLPath = _FileListToArrayEx($XLSXExtractDir, "sheet" & $iSheet & ".xml", 165) If @Error Then $SheetXMLPath = StringRegExp($ContentTypesXML, '(?si)<Override\s*PartName="([^"]*\\sheet[^"]*\.xml)"', 1) If @Error Then $SheetXMLPath = _FileListToArrayEx($XLSXExtractDir, "sheet*.xml", 165) If Not @Error Then $SheetXMLPath = $XLSXExtractDir & $SheetXMLPath[0] Local $WorkSheet = FileRead($SheetXMLPath) If Not $WorkSheet Then Return SetError(3, DirRemove($XLSXExtractDir, 1), "") Local $SharedStringsXML = FileRead($SharedStringsXMLPath) ;; Example Get File Path using the StringRegExpReplace(), but more slowly than StringRegExp Mod. ;; Local $WorkBookXMLPath = $XLSXExtractDir & StringRegExpReplace($ContentTypesXML, '(?si).*<Override\s+PartName="([^"]*\\workbook\.xml)".*', "$1") ;; Local $StylesXMLPath = $XLSXExtractDir & StringRegExpReplace($ContentTypesXML, '(?si).*<Override\s+PartName="([^"]*\\styles\.xml)".*', "$1") ;; Local $SharedStringsXMLPath = $XLSXExtractDir & StringRegExpReplace($ContentTypesXML, '(?si).*<Override\s+PartName="([^"]*\\sharedStrings\.xml)".*', "$1") ;; Local $SheetXMLPath = $XLSXExtractDir & StringRegExpReplace($ContentTypesXML, '(?si).*<Override\s+PartName="([^"]*\\sheet1\.xml)".*', "$1") ;; ;; ;; read other ect ect ect ;; Local $WorkBookXMLPath = StringRegExp($ContentTypesXML, '(?si)<Override\s*PartName="([^"]*\\workbook\.xml)"', 1) ;; If @Error Then $WorkBookXMLPath = _FileListToArrayEx($XLSXExtractDir, "workbook.xml", 165) ;; If Not @Error Then $WorkBookXMLPath = $XLSXExtractDir & $WorkBookXMLPath[0] ;; $WorkBookXML = FileRead($WorkBookXMLPath) ;; ;Example using the StringRegExpReplace() ;; Local $SheetName = $XLSXExtractDir & StringRegExpReplace($WorkBookXML, '(?si).*<sheet\s+name="([^"]*)".*', "$1") ;; ;; Local $AppXMLPath = StringRegExp($ContentTypesXML, '(?si)<Override\s*PartName="([^"]*\\app\.xml)"', 1) ;; If @Error Then $AppXMLPath = _FileListToArrayEx($XLSXExtractDir, "app.xml", 165) ;; If Not @Error Then $AppXMLPath = $XLSXExtractDir & $AppXMLPath[0] ;; $AppXML = FileRead($AppXMLPath) ;; ;Example using the StringRegExpReplace() ;; Local $AppVersion = $XLSXExtractDir & StringRegExpReplace($AppXML, '(?si).*<AppVersion>([^<]*)</AppVersion>.*', "$1") ;; ;; Local $CoreXMLPath = StringRegExp($ContentTypesXML, '(?si)<Override\s*PartName="([^"]*\\core\.xml)"', 1) ;; If @Error Then $CoreXMLPath = _FileListToArrayEx($XLSXExtractDir, "core.xml", 165) ;; If Not @Error Then $CoreXMLPath = $XLSXExtractDir & $CoreXMLPath[0] ;; $CoreXML = FileRead($CoreXMLPath) ;; ;Example using the StringRegExpReplace() ;; Local $Modified = $XLSXExtractDir & StringRegExpReplace($CoreXML, '(?si).*<dcterms\:modified[^>]([^<]*)</dcterms:modified>.*', "$1") ;; ;; Local $StylesXMLPath = StringRegExp($ContentTypesXML, '(?si)<Override\s*PartName="([^"]*\\styles\.xml)"', 1) ;; If @Error Then $StylesXMLPath = _FileListToArrayEx($XLSXExtractDir, "styles.xml", 165) ;; If Not @Error Then $StylesXMLPath = $XLSXExtractDir & $StylesXMLPath[0] ;; $StylesXML = FileRead($StylesXMLPath) ;; ;ect ect ect ect ;; ;; Local $SheetViews = StringRegExp($WorkSheet, '(?si)<sheetView\s+tabSelected="([^"]*)".*?\sworkbookViewId="([^"]*)".*?\stopLeftCell="([A-Z]{1,3})([0-9]+)".*?\sactiveCell="([A-Z]{1,3})([0-9]+)".*?\sdefaultRowHeight="([^"]*)"', 1) ;; If @Error Then Local $SheetViews[7] ;; $SheetViews[2] = _XLSXSheetGetColumnNumber($SheetViews[2]) ;; $SheetViews[4] = _XLSXSheetGetColumnNumber($SheetViews[4]) ;; ;;;; $SheetViews.... ;; ;;;; $SheetViews[0] = tabSelected ;; ;;;; $SheetViews[1] = workbookViewId ;; ;;;; $SheetViews[2] = top Left Cell - Column Number ;; ;;;; $SheetViews[3] = top Left Cell - Rows Nmmber ;; ;;;; $SheetViews[4] = active Cell - Column Number ;; ;;;; $SheetViews[5] = active Cell - Rows Nmmber ;; ;;;; $SheetViews[6] = default Row Height DirRemove($XLSXExtractDir, 1) Local $nCols = Number($Cols), $nRows = "[0-9]+", $X = StringRegExp($WorkSheet, '(?si)<([^:><]*:?)?worksheet\s+', 1) If Not @Error Then $X = $X[0] If $Rows > 0 Then ;;;; StringRegExp($WorkSheet, '(?s)<' & $X & 'row\s+r="' & $Rows & '".*?</' & $X & 'row>', 1) $nRows = $Rows $Rows = 1 EndIf Local $SheetDimension = StringRegExp($WorkSheet, '(?si)<' & $X & '(?:dimension|autoFilter)\s+ref="([A-Z]{1,3})([0-9]+):([A-Z]{1,3})(?i)([0-9]+)', 1) If Not @Error Then $Cols = _XLSXSheetGetColumnNumber($SheetDimension[2]) If $nRows = "[0-9]+" Then $Rows = $SheetDimension[3] EndIf $SheetDimension = StringRegExp($WorkSheet, '(?si)<' & $X & 'col\s+min="?(\d+)[^>]*></' & $X & 'cols>', 1) If Not @Error And $SheetDimension[0] > $Cols Then $Cols = $SheetDimension[0] If $nRows = "[0-9]+" Then $SheetDimension = StringRegExp($WorkSheet, '(?si).*<' & $X & 'c\s+r="?[A-Z]*(\d+)', 1) If Not @Error And $SheetDimension[0] > $Rows Then $Rows = $SheetDimension[0] EndIf If $nCols > ($Cols + 1) Then Return SetError(5, 0, "") If $Cols < 1 Or $Rows < 1 Or ($Cols * ($Rows + 1)) > 15999999 Then Return SetError(4, 0, "") Local $SheetData = StringRegExp($WorkSheet, '(?s)<' & $X & 'c\s+r="([A-Z]{1,3})(?i)(' & $nRows & ')"\s*(?:s=")?([0-9]*)"?\s*(?:t=")?([^">]*)"?\s*><' & $X & 'v>([^<]*)\s*</' & $X & 'v>\s*</' & $X & 'c>', 3) If @Error Then Return SetError(5, 0, "") If $nCols Then $Cols = 1 Local $SheetDataA[($Rows + 1)][$Cols] = [[UBound($SheetData) - 1]], $ColumnName, $ColumnNumber, $ColumnSize, $SharedStringsXMLSize If $SharedStringsXML Then Local $S = StringRegExp($SharedStringsXML, '(?si)<([^:><]*:?)?sst\s+', 1) If Not @Error Then $S = $S[0] $SharedStringsXML = StringRegExp($SharedStringsXML, '(?si)<' & $S & 'si>(?:<' & $S & 'r>.*?)?<' & $S & 't(?:/|\s[^>]*)?>(.*?)(?:</' & $S & 't>)?(?:</' & $S & 'r>)?</' & $S & 'si>', 3) If Not @Error Then $SharedStringsXMLSize = UBound($SharedStringsXML) For $i = 0 To $SharedStringsXMLSize - 1 If StringInStr($SharedStringsXML[$i], "<", 1) Then $SharedStringsXML[$i] = StringRegExpReplace($SharedStringsXML[$i], '</' & $S & 't>.*?<' & $S & 't>', "") If StringInStr($SharedStringsXML[$i], "&", 1) Then $SharedStringsXML[$i] = StringReplace(StringReplace(StringReplace($SharedStringsXML[$i], "&lt;", "<", 0, 1), "&gt;", ">", 0, 1), "&amp;", "&", 0, 1) Next EndIf EndIf For $i = 0 To $SheetDataA[0][0] Step 5 $ColumnSize = StringLen($SheetData[$i]) - 1 If Not $ColumnSize Then $ColumnNumber = Asc($SheetData[$i]) - 65 Else $ColumnName = StringToASCIIArray($SheetData[$i]) $ColumnNumber = $ColumnName[$ColumnSize] - 65 $ColumnNumber += 26 * ($ColumnName[$ColumnSize - 1] - 64) ;(26 ^ 1) * ($ColumnName[1] - 64) If $ColumnSize > 1 Then $ColumnNumber += 676 * ($ColumnName[0] - 64) ;(26 ^ 2) * ($ColumnName[0] - 64) ;;;$ColumnNumber = _XLSXSheetGetColumnNumber($SheetData[$i], 1) EndIf If $nCols Then If $nCols <> ($ColumnNumber + 1) Then ContinueLoop $ColumnNumber = 0 EndIf If $Rows = 1 Then $SheetData[$i + 1] = 1 If $SheetData[$i + 3] = "s" And $SharedStringsXMLSize > $SheetData[$i + 4] Then $SheetDataA[$SheetData[$i + 1]][$ColumnNumber] = $SharedStringsXML[$SheetData[$i + 4]] ElseIf $SheetData[$i + 2] = 2 Then $SheetDataA[$SheetData[$i + 1]][$ColumnNumber] = _SSNToDate($SheetData[$i + 4]) Else $SheetDataA[$SheetData[$i + 1]][$ColumnNumber] = $SheetData[$i + 4] EndIf Next $SheetDataA[0][0] = $Rows If $iFlag Then $HyperLinks = StringRegExp($WorkSheet, '(?si)<' & $X & 'hyperlink\s+ref="([A-Z]{1,3})(?i)(' & $nRows & ')".*?\s+display="([^"]*)"', 3) ;;$HyperLinks = StringRegExp($WorkSheet, '(?si)<' & $X & 'hyperlink\s+ref="([A-Z]{1,3})(?i)' & $nRows & '"\s+r:id="([^"]*)"\s+display="([^"]*)"', 3) If Not @Error Then Local $HyperLinksSize = UBound($HyperLinks) - 1 For $i = 0 To $HyperLinksSize Step 3 $ColumnNumber = _XLSXSheetGetColumnNumber($HyperLinks[$i], 1) If $Rows = 1 Then $HyperLinks[$i + 1] = 1 $SheetDataA[$HyperLinks[$i + 1]][$ColumnNumber] &= @LF & $HyperLinks[$i + 2] Next EndIf EndIf If $nCols And $Rows = 1 Then Return SetError(0, $SheetDataA[1][0] = "", $SheetDataA[1][0]) Return SetError(0, UBound($SheetDataA, 2), $SheetDataA) EndFunc ;==>_XLSXReadToArray ; #FUNCTION# ================================================================================================================= ; Name...........: _XLSXSheetGetColumnNumber ; Description ...: The _XLSXSheetGetColumnNumber Fuction return Column Number of EXCEL XLSX Sheet ; Syntax.........: _XLSXSheetGetColumnNumber($ColumnName) ; Parameters ....: $ColumnName - [A-Z] Uppercase Caracter\String, are not Supported line with more than 3 characters ; $iFlag - Optional ; |$iFlag = 0 (Default) Column Number ; |$iFlag = 1 Column Number - 1 (for Array Index 0) ; Return values .: Success - Return Column Number ; Failure - @Error ; Author ........: DXRW4E ; Modified.......: ; Remarks .......: Limit is 18278 (A = 1 & AB = 27 & ZZZ = 18278) ; Related .......: ; Link ..........: ; Example .......: _XLSXSheetGetColumnNumber("ABC") ; Note ..........: ; ============================================================================================================================ Func _XLSXSheetGetColumnNumber($ColumnName, $iFlag = 0) If Not StringRegExp($ColumnName, '^[A-Z]{1,3}$') Or $iFlag < 0 Or $iFlag > 1 Then Return SetError(1, 0, 0) Local $ColumnNumber, $SheetDimension = StringLen($ColumnName) - 1 If Not $SheetDimension Then $ColumnNumber = Asc($ColumnName) - 64 - $iFlag Else $ColumnName = StringToASCIIArray($ColumnName) $ColumnNumber = $ColumnName[$SheetDimension] - 64 - $iFlag $ColumnNumber += 26 * ($ColumnName[$SheetDimension - 1] - 64) ;(26 ^ 1) * ($ColumnName[1] - 64) If $SheetDimension > 1 Then $ColumnNumber += 676 * ($ColumnName[0] - 64) ;(26 ^ 2) * ($ColumnName[0] - 64) EndIf Return $ColumnNumber EndFunc ;==>_XLSXSheetGetColumnNumber ; #FUNCTION# ================================================================================================================= ; Name...........: _SSNToDate ; Description ...: The _SSNToDate Fuction return Date from sequential serial number ; Syntax.........: _SSNToDate($iDay) ; Parameters ....: $iDay - sequential serial number (generated from DATE fuction on EXCEL, example 39637 = 7/8/2008) ; Return values .: Success - Return DATE ; Failure - @Error ; Author ........: DXRW4E ; Modified.......: ; Remarks .......: DATE String is Month/Day/Year, Year - is number is 1900 to 9999 ; Related .......: ; Link ..........: ; Example .......: _SSNToDate(39637) ; Note ..........: ; ============================================================================================================================ Func _SSNToDate($iDay) If $iDay < 1 Or $iDay > 2958465 Then Return SetError(1, 0, "") $DateSSN[0] = Int($iDay / 365) $DateSSN[25] = $DateSSN[0] / 4 $DateSSN[26] = IsFloat($DateSSN[25]) $iDay = $iDay - ($DateSSN[0] * 365) - Int($DateSSN[25]) - $DateSSN[26] If $iDay < 1 Then $DateSSN[0] -= 1 $DateSSN[25] = IsInt(($DateSSN[0] -1) / 4) $iDay += 365 + $DateSSN[25] $DateSSN[26] = Int($DateSSN[25] = 0) EndIf $DateSSN[2] -= $DateSSN[26] For $iMonth = 1 To 11 If $DateSSN[$iMonth] >= $iDay Then ExitLoop $iDay -= $DateSSN[$iMonth] Next $DateSSN[2] += $DateSSN[26] Return $iMonth & "/" & $iDay & "/" & (1900 + $DateSSN[0]) EndFunc ;==>_SSNToDate ; #FUNCTION# ================================================================================================================= ; Name...........: _DateToSSN ; Description ...: The _DateToSSN Fuction return sequential serial number that represent a particular Date ; Syntax.........: _DateToSSN($iYear, $iMonth, $iDay) ; Parameters ....: $iYear - Year - is number is 1900 to 9999 ; Required. The value of the year argument can include one to four digits. Excel interprets the year argument ; according to the date system your computer is using. By default, Microsoft Excel for Windows uses the 1900 date system. ; We recommend using four digits for the year argument to prevent unwanted results. For example, "07" could mean "1907" or "2007." Four digit years prevent confusion. ; If year is between 0 (zero) and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, DATE(108,1,2) returns January 2, 2008 (1900+108). ; If year is between 1900 and 9999 (inclusive), Excel uses that value as the year. For example, _DateToSSN((2008,1,2) returns January 2, 2008. ; If year is less than 0 or is 10000 or greater, _DateToSSN returns the @Error ; $iMonth - is number is 1 to 12, If Month is less than 0 or is 13 or greater, _DateToSSN returns the @Error ; $iDay - Required. A positive or negative integer representing the day of the month from 1 to 31. ; If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month. ; For example, _DateToSSN(2008,1,35) returns the serial number representing February 4, 2008. ; If day is less than 1, day subtracts the magnitude that number of days, plus one, from the first day of the month specified. ; For example, _DateToSSN(2008,1,-15) returns the serial number representing December 16, 2007. ; Return values .: Success - Return Sequential Serial Number ; Failure - @Error ; Author ........: DXRW4E ; Modified.......: ; Remarks .......: Sequential Serial Number, _DateToSSN(2008, 7, 8) Return 39637, that represent 7/8/2008 ; ; NOTE - Excel stores dates as sequential serial numbers so that they can be used in calculations. January 1, 1900 is serial number 1, ; and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900. ; ; _DateToSSN NOT SUPPORT FOR NOW, THIS ; $iMonth Required. A positive or negative integer representing the month of the year from 1 to 12 (January to December). ; If month is greater than 12, month adds that number of months to the first month in the year specified. For example, ; DATE(2008,14,2) returns the serial number representing February 2, 2009. ; If month is less than 1, month subtracts the magnitude of that number of months, plus 1, from the first month in the ; year specified. For example, DATE(2008,-3,2) returns the serial number representing September 2, 2007. ; Related .......: ; Link ..........: ; Example .......: _DateToSSN(39637) ; Note ..........: ; ============================================================================================================================ Func _DateToSSN($iYear, $iMonth, $iDay) If $iYear < 1900 Or $iYear > 9999 Or $iMonth < 1 Or $iMonth > 12 Then Return SetError(1, 0, "") $iYear -= 1900 $DateSSN[0] = $iYear / 4 If IsFloat($DateSSN[0]) And $iMonth < 3 Then $iDay += 1 Return ($iYear * 365) + Int($DateSSN[0]) + $DateSSN[$iMonth + 12] + $iDay EndFunc ;==>_DateToSSN ; #FUNCTION# ======================================================================================================================================================= ; Name...........: _FileListToArrayEx ; Description ...: Lists files and\or folders in a specified path (Similar to using Dir with the /B Switch) ; Syntax.........: _FileListToArrayEx($sPath[, $sFilter = "*"[, $iFlag = 0]]) ; Parameters ....: $sPath - Path to generate filelist for. ; $sFilter - Optional the filter to use, default is *. (Multiple filter groups such as "All "*.png|*.jpg|*.bmp") Search the Autoit3 helpfile for the word "WildCards" For details. ; $iFlag - Optional: specifies whether to return files folders or both Or Full Path (add the flags together for multiple operations): ; |$iFlag = 0 (Default) Return both files and folders ; |$iFlag = 1 Return files only ; |$iFlag = 2 Return Folders only ; |$iFlag = 4 Search SubDirectory ; |$iFlag = 8 Return Full Path ; |$iFlag = 16 $sFilter do Case-Sensitive matching (By Default $sFilter do Case-Insensitive matching) ; |$iFlag = 32 Disable the return the count in the first element - effectively makes the array 0-based (must use UBound() to get the size in this case). ; By Default the first element ($array[0]) contains the number of file found, the remaining elements ($array[1], $array[2], etc.) ; |$iFlag = 64 $sFilter is REGEXP Mod, See Pattern Parameters in StringRegExp (Can not be combined with flag 16) ; |$iFlag = 128 Return Backslash at the beginning of the file name, example Return "\Filename1.xxx" (Can not be combined with flag 8) ; Return values .: Failure - @Error ; |1 = Path not found or invalid ; |2 = Invalid $sFilter ; |3 = No File(s) Found ; Author ........: DXRW4E ; Modified.......: ; Remarks .......: The array returned is one-dimensional and is made up as follows: ; $array[0] = Number of Files\Folders returned ; $array[1] = 1st File\Folder ; $array[2] = 2nd File\Folder ; $array[3] = 3rd File\Folder ; $array[n] = nth File\Folder ; Related .......: ; Link ..........: ; Example .......: Yes ; Note ..........: Special Thanks to SolidSnake & Tlem ; ================================================================================================================================================================== Func _FileListToArrayEx($sPath, $sFilter = "*", $iFlag = 0) $sPath = StringRegExpReplace($sPath & "\", "[\\/]+", "\\") If Not FileExists($sPath) Then Return SetError(1, 1, "") If StringRegExp($sFilter, StringReplace('^\s*$|\v|[\\/:><"]|^\||\|\||\|$', "[" & Chr(BitAND($iFlag, 64) + 28) & '\/:><"]|^\||\|\||\|$', "\\\\")) Then Return SetError(2, 2, "") Local $hSearch, $sFile, $sFileList, $sSubDir = BitAND($iFlag, 4), $sDelim = "|", $sDirFilter = StringReplace($sFilter, "*", "") $hSearch = FileFindFirstFile($sPath & "*") If @Error Then Return SetError(3, 3, "") Local $hWSearch = $hSearch, $hWSTMP, $SearchWD, $Extended, $iFlags = StringReplace(BitAND($iFlag, 1) + BitAND($iFlag, 2), "3", "0") If BitAND($iFlag, 8) Then $sDelim &= $sPath If BitAND($iFlag, 128) Then $sDelim = "|\" If Not BitAND($iFlag, 64) Then $sFilter = StringRegExpReplace(BitAND($iFlag, 16) & "(?i)(", "16\(\?\i\)|\d+", "") & StringRegExpReplace(StringRegExpReplace(StringRegExpReplace(StringRegExpReplace($sFilter, "[^*?|]+", "\\Q$0\\E"), "\\E(?=\||$)", "$0\$"), "(?<=^|\|)\\Q", "^$0"), "\*+", ".*") & ")" While 1 $sFile = FileFindNextFile($hWSearch) If @Error Then If $hWSearch = $hSearch Then ExitLoop FileClose($hWSearch) $hWSearch -= 1 $SearchWD = StringLeft($SearchWD, StringInStr(StringTrimRight($SearchWD, 1), "\", 1, -1)) ElseIf $sSubDir Then $Extended = @Extended If ($iFlags + $Extended <> 2) Then If $sDirFilter Then If StringRegExp($sFile, $sFilter) Then $sFileList &= $sDelim & $SearchWD & $sFile Else $sFileList &= $sDelim & $SearchWD & $sFile EndIf EndIf If Not $Extended Then ContinueLoop $hWSTMP = FileFindFirstFile($sPath & $SearchWD & $sFile & "\*") If $hWSTMP = -1 Then ContinueLoop $hWSearch = $hWSTMP $SearchWD &= $sFile & "\" Else If ($iFlags + @Extended = 2) Or StringRegExp($sFile, $sFilter) = 0 Then ContinueLoop $sFileList &= $sDelim & $sFile EndIf WEnd FileClose($hSearch) If Not $sFileList Then Return SetError(3, 3, "") Return StringSplit(StringTrimLeft($sFileList, 1), "|", StringReplace(BitAND($iFlag, 32), "32", 2)) EndFunc ;==>_FileListToArrayEx Func _XLSXZip_COMErrorFunc() Return SetError(1, 0, "") EndFunc ;==>_XLSXZip_COMErrorFunc example #include <Array.au3> #include <_XLSXReadToArray.au3> local $a, $t, $Error, $Extended $t = TimerInit() InetGet("http://go.microsoft.com/fwlink/?LinkID=245778", @WindowsDir & "\Temp\BulletinSearch.xlsx") $a = _XLSXReadToArray(@WindowsDir & "\Temp\BulletinSearch.xlsx", 0, 0, 0) $t = TimerDiff($t) _ArrayDisplay($a, $t) ;;or $t = TimerInit() $a = _XLSXReadToArray(@WindowsDir & "\Temp\BulletinSearch.xlsx", 0, 2, 11) $Error = @Error $Extended = @Extended $t = TimerDiff($t) ConsoleWrite("Timer = " & $t & @LF) ConsoleWrite("Return = " & $a & " - $Error = " & $Error & " - $Extended = " & $Extended & @LF) exit _XLSXReadToArray.au3

I was looking for a UDF using which I could Add and Update Pivot tables and Pivot Charts in Excel easily and could not find one that I could use. So I build this UDF. It has the following functions : _ExcelPivot_CreateCache ; Easily Create a pivot table data cache from a Sheet _ExcelPivot_CreateTable ; Create a table from a cache at a specified location on the sheet _ExcelPivot_RefreshTable ; Refresh the datatable data with a new cache _ExcelPivot_AddField ; Add a Field and Aggregate function to the Datatable _ExcelPivot_AddFilter ; Adds in the Filter to a specific field _ExcelPivot_ClearFilter ; Removes the filter for a field or all the filters in the table _ExcelPivot_GetRange ; Get specific areas of a Pivot as a Range Object _ExcelPivot_AddChart ; Add a Pivot Chart linked to a specific Pivot table Attached the UDF to this post. Please do let me know if I can improve or add additional functions to it. A detailed example on the usage is below. The excel file and the example can be downloaded from the Example.zip file attached. #include "ExcelPivot.au3" $oExcel = _Excel_Open() $oBook = _Excel_BookOpen($oExcel, @ScriptDir & "\TestPivot.xlsx") ;Create a Sheet to put the pivot into $pSheet = _Excel_SheetAdd($oBook, -1, False, 1) $pSheet.Name = "Pivot" ;Get the cache for the pivot table $pCache = _ExcelPivot_CreateCache($oBook, "Data") ;Add in the Pivot Table from the Cache _ExcelPivot_CreateTable($pCache, $pSheet, "A1", "FruitsPivot") ;Add in the Fields into the Pivot _ExcelPivot_AddField($pSheet, "FruitsPivot", "Category", "Filter") _ExcelPivot_AddField($pSheet, "FruitsPivot", "Product", "Row") _ExcelPivot_AddField($pSheet, "FruitsPivot", "Amount", "Value", "Sum", 1) ;Add in a Running total to the Pivot _ExcelPivot_AddField($pSheet, "FruitsPivot", "Amount", "Value", "Sum", 2, "PercentageRunningTotal", "Product") ;Filter only the fruits _ExcelPivot_Filter($pSheet, "FruitsPivot", "Category", "Fruit") ;Draw a Paretto Chart $chart = _ExcelPivot_AddChart($oBook, $pSheet, "FruitsPivot", "ColumnClustered", "Paretto", "E2", 570) $chart.Chart.FullSeriesCollection(1).ApplyDataLabels $chart.Chart.FullSeriesCollection(2).ChartType = 4 ;Change the percentage to a line graph $chart.Chart.FullSeriesCollection(2).AxisGroup = 2 ;Move it to secondary axis $chart.Chart.Axes(2, 2).MaximumScale = 1 ;Adjust to scale to 100% max ExcelPivot.au3 Example.zip

I am having a issue of whenever I try to _Excel_RangeWrite a formula that references another workbook I am getting an error @4 and @extended -2147352567 #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookNew($oExcel) _Excel_RangeWrite($oWorkBook,Default,"=IF(D2=D1,"",VLOOKUP(D2,'J:\Temporary Files\FolderName\FileName.xlsx'!$A:$B,2,0))","W2",False) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "String successfully written.") I am not a 100% sure, but I am guessing this is because I have "" and '' in the formula. However, I am not sure how this can best be resolved.

I have an Autoit script that lists files from a folder into an array list. Is there a way to separate the filenames by an underscore and include the id, version, name and date into separate columns in Excel. Example of filename: 12345_v1.0_TEST Name [12345]_01.01.2022.html 12345 would be in one column v1.0 would be in another column TEST Name [12345] would be in another column 01.01.2022 would be in another column .html would be in another column Note: filenames always change each day. Here is my code that lists the files into column C and then writes the column Headers into Column D, E, F, G. Just need some help with separating them into columns by the _ delimiter

I am trying to autofill a range. I am getting stuck and I don't understand what I am doing wrong. My goal is to auto fill some formulas that are next to a pivot table in columns A-C. _Excel_RangeWrite($oNewWorkBook,Default,"2000","D3") _Excel_RangeWrite($oNewWorkBook,Default,"=(B3-D3)","E3") _Excel_RangeWrite($oNewWorkBook,Default,"100","F3") _Excel_RangeWrite($oNewWorkBook,Default,"=(C3-F3)","G3") $oNewWorkbook.ActiveSheet.Range("D3:G3").Select With $oNewWorkbook .Selection.AutoFill(.Range("D3:G77"),0) EndWith The data is not auto filling. Hoping someone can point me in the right direction.

Hi, I have 5 notepad files with lot of data in each of it. The data are arranged in lines and i wish to get it pasted/copied into excel. Say, i need to read notepad1 and paste the contents into sheet1 of excel and notepad2 to sheet2 and so on. If i read the notepad and paste it line by line, it is taking lot of time. Is there a way by which i can paste the whole of notepad file into excel sheet(and get it pasted line by line as shown in the attached excel), instead of using code to write it line by line?.. I was using Filewriteline(data, line i) initially to write to excel. The 'i' value was incremented with for loop and the excel was updated, but this takes lot of time. The expected excel format is attached here. any help is appreciated. thanks Tmp.xls

Hello, the script below will read column A from an excel file - and if a value matches in the browser, it will click the corresponding link and click on a specific button to paste the data, then writes "Completed" in Column B. It will continue to read from the excel file and do the same thing for all the remaining rows. #Include "Chrome.au3" #Include "wd_core.au3" #Include "wd_helper.au3" #Include "WinHttp.au3" #include <MsgBoxConstants.au3> #include <File.au3> #include <IE.au3> #include <Array.au3> #include <INet.au3> #include <AutoItConstants.au3> #include <WinAPIFiles.au3> #include <GDIPlus.au3> #include <Excel.au3> #Include "WinHttp.au3" #Include "_HtmlTable2Array.au3" Local $sDesiredCapabilities, $sSession SetupChrome() _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_LoadWait($sSession) _WD_Navigate($sSession, "table1.html") _WD_LoadWait($sSession) _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='main']") Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='main']") ;ConsoleWrite ("mat-table " & $sElement & @CRLF) Local $aArray1 = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//td[contains(@class,'data')]", $sElement, True) sleep(1000) For $i = 0 to UBound($aArray1) - 1 $aArray1[$i] = _WD_ElementAction($sSession, $aArray1[$i], 'text') Next ;_ArrayDisplay($aArray1) ;Email variables $SmtpServer = "" ; address for the smtp-server to use - REQUIRED $FromName = "Hermes" ; name from who the email was sent $FromAddress = "sender@gmail.com" ; address from where the mail should come $ToAddress = "recipient@gmail.com" ; destination address of the email - REQUIRED, use commas (,) to add more email addresses $Subject = "File not found" ; subject from the email - can be anything you want it to be $Body = "File not found!" ; the messagebody from the mail - can be left blank but then you get a blank mail $AttachFiles = "" ; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed $CcAddress = "" ; address for cc - leave blank if not needed $BccAddress = "" ; address for bcc - leave blank if not needed $Importance = "High" ; Send message priority: "High", "Normal", "Low" $Username = "" ; username for the account used from where the mail gets sent - REQUIRED $Password = "" ; password for the account used from where the mail gets sent - REQUIRED $IPPort = 25 ; port used for sending the mail $ssl = 0 ; enables/disables secure socket layer sending - put to 1 if using httpS $tls = 0 ; enables/disables TLS when required Local $oAppl = _Excel_Open() Local $sWorkbook = "c:\test.xlsx" Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ;open excel and pass both parameters If FileExists($sWorkbook) Then ;Check if the file exist. Local $oAppl = _Excel_Open() Local $sWorkbook = "c:\test.xlsx" Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ;open excel and pass both parameters Local $aArray2 = _Excel_RangeRead($oWorkbook,Default,$oWorkbook.ActiveSheet.Usedrange.Columns("A:A")) Local $iIdx Local $Skipline = 0 ;0==> first line Do Local $temprf For $i = 0 To UBound($aArray2) - 1 $temprf &= $aArray2[$i] _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, ".//a[contains(@class,'edit') and contains(text(),'Edit')]") Local $aElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//a[contains(@class,'edit') and contains(text(),'Edit')]", $sElement, True) $iIdx = _ArraySearch($aArray1, $aArray2[$i]) If @error Then ContinueLoop _WD_ElementAction($sSession, $aElement[$iIdx], 'click') If $i < $Skipline Then ContinueLoop $oRange = $oWorkbook.ActiveSheet.Range("B" & $i + 1 & ":XFD" & $i + 1) _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange) ;Paste Local $oTest4 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "pastebutton") _WD_ElementAction($sSession, $oTest4, 'click') Sleep(1000) ;Save Button Local $save3 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "button.button") _WD_ElementAction($sSession, $save3, 'click') _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Completed", "B" & $i+1) sleep(1000) Next Until (Not @error) _Excel_Close($oWorkbook) Else _INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $ToAddress, $Subject, $Body, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl, $tls) Exit EndIf _WD_LoadWait($sSession) ;Attaching files to emails Func _INetSmtpMailCom($s_SmtpServer, $s_FromName, $s_FromAddress, $s_ToAddress, $s_Subject = "", $as_Body = "", $s_CcAddress = "", $s_BccAddress = "", $s_Importance="Normal", $s_Username = "", $s_Password = "", $IPPort = 25, $ssl = 0, $tls = 0) Local $objEmail = ObjCreate("CDO.Message") $objEmail.From = '"' & $s_FromName & '" <' & $s_FromAddress & '>' $objEmail.To = $s_ToAddress Local $i_Error = 0 Local $i_Error_desciption = "" If $s_CcAddress <> "" Then $objEmail.Cc = $s_CcAddress If $s_BccAddress <> "" Then $objEmail.Bcc = $s_BccAddress $objEmail.Subject = $s_Subject If StringInStr($as_Body, "<") And StringInStr($as_Body, ">") Then $objEmail.HTMLBody = $as_Body Else $objEmail.Textbody = $as_Body & @CRLF EndIf $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = $s_SmtpServer If Number($IPPort) = 0 then $IPPort = 25 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = $IPPort ;Authenticated SMTP If $s_Username <> "" Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = $s_Username $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = $s_Password EndIf ; Set security params If $ssl Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True If $tls Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendtls") = True ;Update settings $objEmail.Configuration.Fields.Update ; Set Email Importance Switch $s_Importance Case "High" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "High" Case "Normal" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Normal" Case "Low" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Low" EndSwitch $objEmail.Fields.Update ; Sent the Message $objEmail.Send $objEmail="" EndFunc ;==>_INetSmtpMailCom Local $aDir = _FileListToArrayRec(@TempDir, "scoped_dir*;chrome_*", $FLTAR_FOLDERS, $FLTAR_NORECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH) Sleep(2000) For $i = 1 To $aDir[0] DirRemove($aDir[$i], $DIR_REMOVE) Next _WD_LoadWait($sSession) _WD_Shutdown() Func SetupChrome() _WD_Option('Driver', 'chromedriver.exe') _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}' EndFunc ;==>SetupChrome If the excel file doesn't exists in the folder, it will send an email to a specific recipient. What i am trying figure out now is if the excel crashes while the script/loop is running, I want to relaunch the excel file continue to the last row before the excel crashed. So if the value of column B is not marked as "completed", it should continue from that row Appreciate any help that I can get to achieve this. table1.html test.xlsx

My _Excel_RangeCopyPaste is not working as intended. What I am trying to accomplish is copy the range B:E using _Excel_RangeCopyPaste in the first row and repeat the same for row 2 and so on. ;Skip from reading header columns Local $Skipline = 0 ;0==> first line Local $temprf For $i = 0 To UBound($aArray2) - 1 If $Skipline = $i Then ContinueLoop $temprf &= $aArray2[$i] _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange) Local $oTest = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "PasteButton") ;This button will paste values from the Clipboard once clicked _WD_ElementAction($sSession, $oTest, 'click')) Next Here's the full code: #Include <Chrome.au3> #Include <wd_core.au3> #Include <wd_helper.au3> #Include <WinHttp.au3> #include <MsgBoxConstants.au3> #include <IE.au3> #include <Array.au3> #include <Excel.au3> Local $sDesiredCapabilities, $sSession SetupChrome() _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_LoadWait($sSession) _WD_Navigate($sSession, "test.html") Local $oAppl = _Excel_Open() Local $sWorkbook = "test.xlsx" Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ;open excel and pass both parameters Local $aArray2 = _Excel_RangeRead($oWorkbook,Default,$oWorkbook.ActiveSheet.Usedrange.Columns("A:A")) Local $oRange = $oWorkbook.ActiveSheet.Range("B:E") ;Skip from reading header columns Local $Skipline = 0 ;0==> first line Local $temprf For $i = 0 To UBound($aArray2) - 1 If $Skipline = $i Then ContinueLoop $temprf &= $aArray2[$i] _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange) Local $oTest = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "PasteButton") ;This button will paste values from the Clipboard once clicked _WD_ElementAction($sSession, $oTest, 'click')) Next _WD_Shutdown() Func SetupChrome() _WD_Option('Driver', 'chromedriver.exe') _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') Return '{"capabilities":{"alwaysMatch":{"goog:chromeOptions":{"w3c":true,' & _ '"excludeSwitches":["enable-automation"],"useAutomationExtension":false}}}}' EndFunc ;==>SetupChrome For the first row I am trying to copy just B:E with the following info Apple Banana Orange Mango and then repeat for row 2, row 3, etc. I've attached the spreadhseet. test.xlsxI have also attached the excel file for reference.

Hi, My autoit program generates excel output file. How do i set the author name for this excel file. thanks

I have an html table that displays data along with an excel spreadsheet that has the same data as the html table. I am wanting to only match the Title column in my html table with the Title column in my Excel spreadsheet. If the titles match, click on the Edit hyperlink and continue to loop to next row. The issue I'm experience is its not matching correctly. So far i've written the codes below: <table border="1" class="test"> <tr> <th> UniqueID</th> <th> Title</th> <th> UserID</th> <th> Address</th> <th> Gender </th> </tr> <tr> <td> 1 </td> <td> Title1 </td> <td> 12345 </td> <td> Manila </td> <td> <span> Male </span> </td> </tr> <tr> <td align="center" colspan="5"> <a href="#" class="testlink">Edit</a> </td> </tr> <tr> <td> 2 </td> <td> Title2 </td> <td> 67891 </td> <td> Valenzuela </td> <td> <span> Female </span> </td> </tr> <tr> <td align="center" colspan="5" > <a href="#" class="testlink">Edit</a> </td> </tr> <tr> <td> 3 </td> <td> Title3 </td> <td> 88888 </td> <td> Ohio </td> <td> <span> Male </span> </td> </tr> <tr> <td align="center" colspan="5" > <a href="#" class="testlink">Edit</a> </td> </tr> <tr> <td> 4 </td> <td> Title4 </td> <td> 77777 </td> <td> California </td> <td> <span> Female </span> </td> </tr> <tr> <td align="center" colspan="5" > <a href="#" class="testlink">Edit</a> </td> </tr> <tr> <td> 5 </td> <td> Title5 </td> <td> 33333 </td> <td> Arizona </td> <td> <span> Male </span> </td> </tr> <tr> <td align="center" colspan="5" > <a href="#" class="testlink">Edit</a> </td> </tr> </table> #Include "Chrome.au3" #Include "wd_core.au3" #Include "wd_helper.au3" #Include "Excel.au3" #Include "_HtmlTable2Array.au3" #Include "Array.au3" Local $sDesiredCapabilities, $sSession SetupChrome() _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_LoadWait($sSession) _WD_Navigate($sSession, "index.html") Sleep(6000) Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, "test.xlsx") ; Get the table element $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='test']") ; Retrieve HTML $sHTML = _WD_ElementAction($sSession, $sElement, "Property", "outerHTML") ;Local $aTable = _HtmlTableGetWriteToArray($sHTML) Local $aArray1 = _Excel_RangeRead($oWorkbook,1,$oWorkbook.ActiveSheet.Usedrange.Columns("B:B")) Local $aArray2 = _HtmlTableGetWriteToArray($sHTML) ;_ArrayDisplay($aArray1) ;_ArrayDisplay($aArray2) For $i = UBound($aArray1) - 1 To 0 step - 1 For $j = UBound($aArray2) - 1 to 0 step - 1 If $aArray1[$i][1] == $aArray2[$j][1] Then _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, "//a[contains(@class,'testlink') or contains(text(),'Edit')]") $test1 = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//a[contains(@class,'testlink') or contains(text(),'Edit')]") _WD_ElementAction($sSession, $test1, 'click') ;_ArrayDisplay($aArray1) ;_ArrayDelete($aArray1 , $i) ;exitloop EndIf Next Next _WD_Shutdown() Func SetupChrome() _WD_Option('Driver', 'chromedriver.exe') _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}' EndFunc ;==>SetupChrome Would appreciate if anyone can provide tips, or point me in the right direction in doing it. test.xlsx

Hello, I'm struggling and not sure what I'm missing with how to use the _Excel_RangeRead function. Based on: https://www.autoitscript.com/autoit3/docs/libfunctions/_Excel_RangeRead.htm The second parameter should allow me to select different sheet names, however I can't seem to get it to work. I was thinking something like this (see below) would be all I need to get an array for the sheet named as "test sheet". $array = _Excel_RangeRead($oWorkbook, "test sheet") Can someone please help tell me what I'm missing here? Thank you,

I'm having some issues with writing to column C when an element is found. It works on C2 but it does not continue to C3, C4, C5, etc..... I'm wanting to write "test" if the element //input[@id='username'] is found $someUser = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//input[@id='username']"). I have attached my HTML and Excel file along with my AutoIt code below: #Include "wd_core.au3" #Include "wd_helper.au3" #Include "wd_core.au3" #Include "File.au3" #Include "Array.au3" #Include "Excel.au3" Local $sDesiredCapabilities, $sSession _WD_Startup() $Ssession = _WD_CreateSession($sDesiredCapabilities) _WD_Navigate($sSession, "https://127.0.0.1/test.html") _WD_LoadWait($sSession) Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\<Username>\Downloads\test.xlsx") Local $aArrayTest1 = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A")) Local $aArrayTest2 = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.ActiveSheet.Usedrange.Columns("B:B")) For $i = 0 To UBound($aArrayTest1) - 1 _WD_Navigate($Ssession, $aArrayTest1[$i]) _WD_LoadWait($sSession) $someUser = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//input[@id='username']") _WD_SetElementValue($sSession, $someUser, $aArrayTest2[$i]) Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//input[@type='submit'][@value='Submit']") _WD_ElementAction($sSession, $sElement, 'click') _WD_LoadWait($sSession) Sleep(5000) If $someUser Then Local $aArray2D[2] = ["test"] _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $aArray2D, "C2") EndIf Next Func SetupChrome() _WD_Option('Driver', 'chromedriver.exe') _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}' EndFunc ;==>SetupChrome test.html test.xlsx

Hi @water in my excel file it doesn't work because it counts also all the cells that a somehow formated. Can this also ignores formated cells and only counts cells with data? thanks

Excel VBA's IDE registers a Control-y as "cut this line of code". For those prone to Undo/Redo (Ctrl+Z/Ctrl+Y) you may find frustration when your code in the editor does not redo, but in fact clears your active line of code while killing redo history. Though not perfect, I keep this tool running in background on startup. The purpose is to allow Cltr+Y to act normally throughout Windows and Office and only interact *differently* with the "Microsoft Visual Basic for Applications" window that is active. If the Standard Menu bar exists, it'll try to click the ReDo (Blue Arrow to the right), else "Alt+e, r" keystrokes (less desired). Here's the code: Opt('MustDeclareVars', 1) Opt("WinTitleMatchMode", 1) HotKeySet("^y", "TriggerRedo") While 1 Sleep(10) WEnd Func TriggerRedo() ConsoleWrite("TriggerRedo()" & @CRLF) Local $title = "Microsoft Visual Basic for Applications - " Local $hWnd If WinExists($title) And WinActive($title) Then ;~ Parent Window Handle $hWnd = WinGetHandle($title) Local $aWindowPos = WinGetPos($hWnd) ;~ Control Bar Handle, Position and If Visible Local $sControlID = "[CLASS:MsoCommandBar; TEXT:Standard;]" Local $hStandardBar = ControlGetHandle($hWnd, "", $sControlID) Local $bIsVisible = ControlCommand($hWnd, "", $sControlID, "IsVisible") If $hStandardBar And $bIsVisible Then ConsoleWrite("Using Mouse Click." & @CRLF) ;~ Determine Redo button location on visible Control Bar Local $aBarPos = ControlGetPos($hWnd, "", $sControlID) Local $mX = $aWindowPos[0] + $aBarPos[0] + 217 + Int(23/2) Local $mY = $aWindowPos[1] + $aBarPos[1] + 27 + Int(22/2) MouseClick("Left", $mX, $mY, 1, 0) Else ConsoleWrite("Using VBA Send Keys." & @CRLF) $sControlID = "[CLASS:MsoCommandBar; TEXT:Menu Bar;]" Local $hMenuBar = ControlGetHandle($hWnd, "", $sControlID) ControlSend($hWnd, "", $hMenuBar, "!e") ;~ Send("r") $sControlID = "[CLASS:MsoCommandBarPopup; TEXT:Edit;]" Local $hPopupBar = ControlGetHandle($hWnd, "", $sControlID) ControlSend($hWnd, "", $hPopupBar, "r") EndIf Else ConsoleWrite("Using NATIVE Send Keys." & @CRLF) HotKeySet("^y") Send("^y") ;~ may cause "yyy..." when held HotKeySet("^y", "TriggerRedo") EndIf EndFunc ;==>TriggerRedo Hope this inspires someone.

Hi, I'd like to change different colors for different portion of text in same cell of Excel application. Neither character length nor cell might not fixed. Here's the code I've tried to put together but not manage to pull it off. I'm appreciate it for any suggestion, thank you. $oExcel = ObjCreate("Excel.Application") With $oExcel ; open new workbook .Visible = True .WorkBooks.Add .ActiveWorkbook.Sheets(1).Select() EndWith $oExcel.Cells.Font.Color = 0x000000 $oExcel.ActiveFont.Color = -16776961 Send ("'I'd like this sentence to be red'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) $oExcel.ActiveCell.Selection.Font.Color = 0x000000 Send ("'I like this sentence to be black'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) $oExcel.ActiveFont.Color = -16776961 Send ("'I'd like this sentence to be red again'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) Send("{ENTER}")

Hi, I'm sure that this is a simple question regarding webdriver udf but my search on the forum did not result in the exact same scenario that I need. I will adapt it to my corporate needs but at first I want it to work with an example everybody can reproduce if needed. I navigate to https://www.daysoftheyear.com/ and want to find out what special day today is. Using the following Code returns the wanted text that today is 'shark awareness day' in the console but it is not pasted in notepad as it is not copied as it probably should be. #include "wd_core.au3" #include "wd_helper.au3" Local $sDesiredCapabilities, $sSession, $sID _WD_Option("Driver", "C:\Program Files (x86)\AutoIt3\chromedriver.exe") _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true }}}}' _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_Navigate($sSession, "https://www.daysoftheyear.com/?timezone_offset=nan") _WD_LoadWait($sSession) Sleep(3000) Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "/html/body/div[2]/header/div/div[1]/div/div[2]/h3/a") _WD_ElementAction($sSession, $sElement, 'text') ClipGet() Run("notepad.exe") Sleep(500) Send("^v") Scite returns this, therefore the text has been found but the last mile to paste it to notepad or excel does not work for me. __WD_Get: URL=HTTP://127.0.0.1:9515/session/b72166b774d7fefb258b3721fc1d4306/element/657e6b5d-31a9-46a8-85c4-acb7f72bf659/text __WD_Get: StatusCode=200; $iResult = 0; $sResponseText={"value":"Shark Awareness Day"}... _WD_ElementAction: {"value":"Shark Awareness Day"}... Thanks in advance!

Sorry if this is a repost, but having some trouble searching for the answer and pretty tired right now. I'm just wondering how to make a border around a range of cells in Excel. I want the regular lines created by selecting "All Borders" option around F2:G3 I saw this code: With $oExcel.ActiveSheet.range("F2:G3") .Select .Borders($xlEdgeBottom).LineStyle = $xlContinuous .Borders($xlEdgeBottom).Weight = $xlThick .Borders($xlEdgeBottom).ColorIndex = $xlAutomatic EndWith but I get errors of these variables not existing. It seems these no longer exist in the "#include <Excel.au3>" Is there another include file I need? I got this to work for highlighting cells, wondering if there is a option similar to this for all borders? $oExcel.ActiveSheet.Range("F2:G3").Interior.ColorIndex = 6 Thanks

One web created Excel sheet is crashing when calling the _Excel_BookOpen function. "C:\Program Files (x86)\AutoIt3\Include\Excel.au3" (227) : ==> Variable must be of type "Object".: $oExcel.Windows($oWorkbook.Name).Visible = $bVisible $oExcel.Windows($oWorkbook.Name)^ ERRORLocal $oWorkbook = $oExcel.Workbooks.Open($sFilePath, $bUpdateLinks, $bReadOnly, Default, $sPassword, $sWritePassword) in line 225 seems to load the file but no error is set. Is there any way to catch those errors to avoid app crash? ObjEvent("AutoIt.Error", "ErrFunc") doesn't catch it! Tested on 3.3.14.2 and 3.3.15.0 and Office 2013.

Hey, as part of a larger Programm I am trying to copy the values from one cell in an Excel file to another. The code I am using is the folowing. Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_RangeCopyPaste($oExcel.ActiveSheet, "V4:W43", "X4", False, $xlPasteValues, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "2 Rows successfully pasted from the clipboard to row 1.") As I understood it the option $xlPasteValues should do the trick but it is still copying the inserted formulas. I attached a test code and excel file. Thanks for the help Simon Copy Values Test.zip