Search the Community

Showing results for tags 'excel'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • General
    • Announcements and Site News
    • Chat
    • Administration
  • AutoIt v3
    • AutoIt Help and Support
    • AutoIt Technical Discussion
    • AutoIt Example Scripts
  • Scripting and Development
    • Developer General Discussion
    • Language Specific Discussion
  • IT Administration
    • Operating System Deployment
    • Windows Client
    • Windows Server
    • Office

Categories

  • AutoIt Team
    • Beta
    • MVP
  • AutoIt
    • Automation
    • Databases and web connections
    • Data compression
    • Encryption and hash
    • Games
    • GUI Additions
    • Hardware
    • Information gathering
    • Internet protocol suite
    • Maths
    • Media
    • PDF
    • Security
    • Social Media and other Website API
    • Windows
  • Scripting and Development
  • IT Administration
    • Operating System Deployment
    • Windows Client
    • Windows Server
    • Office

Categories

  • Forum
  • AutoIt

Calendars

  • Community Calendar

Found 159 results

  1. Hi guys, I was trying to automate a webpage after getting input from excel. The script worked fine for like 5 iterations but then stopped all of a sudden, I dont have the exact error msg now, but it was pointing to this in the IE UDF:- Return SetError($_IESTATUS_Success, $oTemp.GetElementsByTagName($sTagName).length, When i looked up the forum there was a similar question which said that I have to fix KB 3008923 update by uninstalling it, but I am unable to do that. here is the link for download:- ( i guess ) https://support.microsoft.com/en-us/help/3024777/error-0x800706f7-occurs-after-you-install-kb-3004394-and-then-run-windows-update-in-windows-7-and-windows-server-2008-r2 Does anybody know the proper steps to solve this?? Plus I wont be able to send the code here because of privacy policy n all by the company. Desperately looking for a reply.....
  2. Hello everyone I would like to have some help regarding my issue. I am trying to extract some information from excel sheet, I use _Excel_RangeFind then I get the array for the value then I check the array. In case the array is true the next will be to get some information based on the extracted array if no array because the value is not found it gives a messages box that the value is not found. My problem that the button works for one time only if i tried any value which exist in the sheet it gives me that value not found. I suspect that there is a problem regarding While loop. here is my code #include <GUIConstantsEx.au3> #include <WinAPI.au3> #Include <GuiListBox.au3> #include <WindowsConstants.au3> #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> #include <ExcelConstants.au3> if FileExists ("result.txt") Then Sleep (100) Else readxl() EndIf Global $oExcel = _Excel_Open(False,False) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Global $oWorkbook = _Excel_BookOpen($oExcel, "D:\info.xlsx",False,False) If @error Then MsgBox($MB_SYSTEMMODAL, "Excel Error", "Error opening workbook '" & $oWorkbook & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf Local $lab1 = _Excel_RangeRead($oWorkbook, Default,"B1") Local $lab2 = _Excel_RangeRead($oWorkbook, Default,"C1") Local $lab3 = _Excel_RangeRead($oWorkbook, Default,"D1") Local $lab4 = _Excel_RangeRead($oWorkbook, Default,"E1") Global $sResult1,$sResult2,$sResult3,$sResult4,$sResult5,$sResult6 Global $asKeyWords = stringsplit (FileRead (@ScriptDir & "\result.txt"),@CRLF) Global Const $xlUp = -4162 ;~ _Main() Local $hGUI, $hList, $hInput, $aSelected, $sChosen, $hUP, $hDOWN, $hENTER, $hESC Local $sCurrInput = "", $aCurrSelected[2] = [-1, -1], $iCurrIndex = -1, $hListGUI = -1 $hGUI = GUICreate("Rimo System", 253, 270, 192, 124) Global $hInput = GUICtrlCreateInput("", 24, 48, 169, 21) Global $Label1 = GUICtrlCreateLabel("Rimo System", 80, 16, 150, 25) GUICtrlSetFont(-1, 14, 800, 0, "MS Serif") Global $Input2 = GUICtrlCreateInput("", 72, 144, 161, 21) Global $Input3 = GUICtrlCreateInput("", 72, 176, 161, 21) Global $Input4 = GUICtrlCreateInput("", 72, 208, 161, 21) Global $Input5 = GUICtrlCreateInput("", 72, 240, 161, 21) $Input6 = GUICtrlCreateInput("", 72, 272, 161, 21) $Input7 = GUICtrlCreateInput("", 72, 304, 161, 21) $Button1 = GUICtrlCreateButton("Get Info", 72, 88, 89, 33) $Label2 = GUICtrlCreateLabel("Label2", 16, 144, 36, 17) GUICtrlSetData( -1,$lab1) $Label3 = GUICtrlCreateLabel("Label3", 16, 176, 36, 17) GUICtrlSetData( -1,$lab2) $Label4 = GUICtrlCreateLabel("Label4", 16, 208, 36, 17) GUICtrlSetData( -1,$lab3) $Label5 = GUICtrlCreateLabel("Label5", 16, 240, 36, 17) GUICtrlSetData( -1,$lab4) $Label6 = GUICtrlCreateLabel("", 16, 272, 36, 17) $Label7 = GUICtrlCreateLabel("", 16, 304, 36, 17) $Button2 = GUICtrlCreateButton("Cancel", 112, 416, 121, 25) GUISetState(@SW_SHOW, $hGUI) Global $sSearch = guictrlread ($hInput) $hUP = GUICtrlCreateDummy() $hDOWN = GUICtrlCreateDummy() $hENTER = GUICtrlCreateDummy() $hESC = GUICtrlCreateDummy() Dim $AccelKeys[4][2] = [["{UP}", $hUP], ["{DOWN}", $hDOWN], ["{ENTER}", $hENTER], ["{ESC}", $hESC]] GUISetAccelerators($AccelKeys) While 1 Switch GUIGetMsg() Case $GUI_EVENT_CLOSE ExitLoop Case $Button2 Exit Case $Button1 Global $aResult = _Excel_RangeFind($oWorkbook, guictrlread($hInput) ,"A2:A2000") Global $aExtract = _ArrayExtract($aResult, 0, 0, 2, 2) if _elementExists($aExtract,0) Then getdata() Else MsgBox(0,"","Value Does Not Exist") EndIf Case $hESC If $hListGUI <> -1 Then ; List is visible. GUIDelete($hListGUI) $hListGUI = -1 Else ExitLoop EndIf Case $hUP If $hListGUI <> -1 Then ; List is visible. $iCurrIndex -= 1 If $iCurrIndex < 0 Then $iCurrIndex = 0 EndIf _GUICtrlListBox_SetCurSel($hList, $iCurrIndex) EndIf Case $hDOWN If $hListGUI <> -1 Then ; List is visible. $iCurrIndex += 1 If $iCurrIndex > _GUICtrlListBox_GetCount($hList) - 1 Then $iCurrIndex = _GUICtrlListBox_GetCount($hList) - 1 EndIf _GUICtrlListBox_SetCurSel($hList, $iCurrIndex) EndIf Case $hENTER If $hListGUI <> -1 And $iCurrIndex <> -1 Then ; List is visible and a item is selected. $sChosen = _GUICtrlListBox_GetText($hList, $iCurrIndex) EndIf Case $hList $sChosen = GUICtrlRead($hList) EndSwitch Sleep(10) $aSelected = _GetSelectionPointers($hInput) If GUICtrlRead($hInput) <> $sCurrInput Or $aSelected[1] <> $aCurrSelected[1] Then ; Input content or pointer are changed. $sCurrInput = GUICtrlRead($hInput) $aCurrSelected = $aSelected ; Get pointers of the string to replace. $iCurrIndex = -1 If $hListGUI <> -1 Then ; List is visible. GUIDelete($hListGUI) $hListGUI = -1 EndIf $hList = _PopupSelector($hGUI, $hListGUI, _CheckInputText($sCurrInput, $aCurrSelected)) ; ByRef $hListGUI, $aCurrSelected. EndIf If $sChosen <> "" Then GUICtrlSendMsg($hInput, 0x00B1, $aCurrSelected[0], $aCurrSelected[1]) ; $EM_SETSEL. _InsertText($hInput, $sChosen) $sCurrInput = GUICtrlRead($hInput) GUIDelete($hListGUI) $hListGUI = -1 $sChosen = "" EndIf WEnd GUIDelete($hGUI) Func _CheckInputText($sCurrInput, ByRef $aSelected) Local $sPartialData = "" If (IsArray($aSelected)) And ($aSelected[0] <= $aSelected[1]) Then Local $aSplit = StringSplit(StringLeft($sCurrInput, $aSelected[0]), " ") $aSelected[0] -= StringLen($aSplit[$aSplit[0]]) If $aSplit[$aSplit[0]] <> "" Then For $A = 1 To $asKeyWords[0] If StringLeft($asKeyWords[$A], StringLen($aSplit[$aSplit[0]])) = $aSplit[$aSplit[0]] And $asKeyWords[$A] <> $aSplit[$aSplit[0]] Then $sPartialData &= $asKeyWords[$A] & "|" EndIf Next EndIf EndIf Return $sPartialData EndFunc ;==>_CheckInputText Func _PopupSelector($hMainGUI, ByRef $hListGUI, $sCurr_List) Local $hList = -1 If $sCurr_List = "" Then Return $hList EndIf $hListGUI = GUICreate("", 280, 160, 23, 62, $WS_POPUP, BitOR($WS_EX_TOOLWINDOW, $WS_EX_TOPMOST, $WS_EX_MDICHILD), $hMainGUI) $hList = GUICtrlCreateList("", 0, 0, 170, 150, BitOR(0x00100000, 0x00200000)) GUICtrlSetData($hList, $sCurr_List) GUISetControlsVisible($hListGUI) ; To Make Control Visible And Window Invisible. GUISetState(@SW_SHOWNOACTIVATE, $hListGUI) Return $hList EndFunc ;==>_PopupSelector Func _InsertText(ByRef $hEdit, $sString) #cs Description: Insert A Text In A Control. Returns: Nothing #ce Local $aSelected = _GetSelectionPointers($hEdit) GUICtrlSetData($hEdit, StringLeft(GUICtrlRead($hEdit), $aSelected[0]) & $sString & StringTrimLeft(GUICtrlRead($hEdit), $aSelected[1])) Local $iCursorPlace = StringLen(StringLeft(GUICtrlRead($hEdit), $aSelected[0]) & $sString) GUICtrlSendMsg($hEdit, 0x00B1, $iCursorPlace, $iCursorPlace) ; $EM_SETSEL. EndFunc ;==>_InsertText Func _GetSelectionPointers($hEdit) Local $aReturn[2] = [0, 0] Local $aSelected = GUICtrlRecvMsg($hEdit, 0x00B0) ; $EM_GETSEL. If IsArray($aSelected) Then $aReturn[0] = $aSelected[0] $aReturn[1] = $aSelected[1] EndIf Return $aReturn EndFunc ;==>_GetSelectionPointers Func GUISetControlsVisible($hWnd) ; By Melba23. Local $aControlGetPos = 0, $hCreateRect = 0, $hRectRgn = _WinAPI_CreateRectRgn(0, 0, 0, 0) Local $iLastControlID = _WinAPI_GetDlgCtrlID(GUICtrlGetHandle(-1)) For $i = 3 To $iLastControlID $aControlGetPos = ControlGetPos($hWnd, '', $i) If IsArray($aControlGetPos) = 0 Then ContinueLoop $hCreateRect = _WinAPI_CreateRectRgn($aControlGetPos[0], $aControlGetPos[1], $aControlGetPos[0] + $aControlGetPos[2], $aControlGetPos[1] + $aControlGetPos[3]) _WinAPI_CombineRgn($hRectRgn, $hCreateRect, $hRectRgn, 2) _WinAPI_DeleteObject($hCreateRect) Next _WinAPI_SetWindowRgn($hWnd, $hRectRgn, True) _WinAPI_DeleteObject($hRectRgn) EndFunc Func _elementExists($array, $element) If $element > UBound($array)-1 Then Return False ; element is out of the array bounds Return True ; element is in array bounds EndFunc Func getdata() ;~ Local $sResult1 = _Excel_RangeRead($oWorkbook, Default,StringReplace(StringReplace ($aExtract[0],"$",""),"A","B")) ;~ Local $sResult2 = _Excel_RangeRead($oWorkbook, Default,StringReplace(StringReplace ($aExtract[0],"$",""),"A","C")) ;~ Local $sResult3 = _Excel_RangeRead($oWorkbook, Default,StringReplace(StringReplace ($aExtract[0],"$",""),"A","D")) ;~ Local $sResult4 = _Excel_RangeRead($oWorkbook, Default,StringReplace(StringReplace ($aExtract[0],"$",""),"A","E")) ;~ Local $sResult5 = _Excel_RangeRead($oWorkbook, Default,StringReplace(StringReplace ($aExtract[0],"$",""),"A","F")) ;~ Local $sResult6 = _Excel_RangeRead($oWorkbook, Default,StringReplace(StringReplace ($aExtract[0],"$",""),"A","G")) guictrlsetdata($Input2,$sResult1) guictrlsetdata($Input3,$sResult2) guictrlsetdata($Input4,$sResult3) guictrlsetdata($Input5,$sResult4) guictrlsetdata($Input6,$sResult5) guictrlsetdata($Input7,$sResult6) _Excel_Close($oExcel,Default,True) EndFunc Func readxl() Global $oExcel = _Excel_Open(False,False) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Global $oWorkbook = _Excel_BookOpen($oExcel, "D:\info.xlsx",False,False) LOcal Const $xlUp = -4162 With $oWorkbook.ActiveSheet ; process active sheet $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) ; get a Range that contains the last used cells $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count ; get the the row count for the range starting in row/column 1 and ending at the last used row/column $iLastCell = .Cells($iRowCount + 1, "B").End($xlUp).Row ProgressOn("Copying Cells", "Copying Cells progress", "0%") For $i = 2 to $iLastCell Local $total = Int(($i/$iLastCell)*100) Local $sResult3 = _Excel_RangeRead($oWorkbook, Default, "A" & $i) FileWriteLine("result.txt",$sResult3) ProgressSet(($i/$iLastCell)*100, $total & "%") Next ;~ FileWrite("result.txt",$sResult3) ProgressSet(100, "Done", "Complete") Sleep (1500) ProgressOff() _Excel_Close($oExcel,Default,True) EndWith EndFunc
  3. change linked image paths in excel 2007 Open XML Files with AutoIt and 7-zip: #include <File.au3> ;Change this Local $sFind = "C:\Users\MyUserName\Documents\MyImageFolder\My%20Image1.png" Local $sReplace = "C:\Users\ANOTHERUSERNAME\Documents\AnotherImageFolder\My%20Image1.png" Local Const $sMessage = "Directory to change excel image paths" Local $sFileSelectFolder = FileSelectFolder($sMessage, "") Local $sTempDir = @ScriptDir & "\testdir" ;Required 7-zip Local $PathZipProgram = @ProgramFilesDir & "\7-Zip\" If Not(FileExists($PathZipProgram & "\7z.exe")) Then MsgBox(16, "", "7z.exe not found in path " & $PathZipProgram) Exit EndIf ;look for excel files in selected directory and all subdirectories Local $SFileList = _FileListToArrayRec($sFileSelectFolder, "*.xls.;*.xlsm", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH) If Not @error Then For $i = 1 To $SFileList[0] DirRemove($sTempDir, 1) ;use x command to keep the folder stucture, -aoa Overwrite All existing files without prompt, use -r to unzip the subfolders from the zip file RunWait('"' & $PathZipProgram & '7z.exe" x -aoa -r "' & $SFileList[$i] & '" -o"' & $sTempDir & '" -y', $PathZipProgram, @SW_HIDE) __ReplaceImagePaths($sTempDir, $sFind, $sReplace) RunWait('"' & $PathZipProgram & '7z.exe" a -r "' & $SFileList[$i] & '" "' & $sTempDir & '\*" -tzip -y', $PathZipProgram, @SW_HIDE) Next Else MsgBox(16, "Error", "No files were found in the folder specified.") EndIf DirRemove($sTempDir, 1) Func __ReplaceImagePaths($sTempDir, $sFind, $sReplace) ;List all files with .xml.rels extension in the directory \xl\drawings\_rels Local $aFileList = _FileListToArray($sTempDir & "\xl\drawings\_rels", "*.xml.rels", 1, True) If @error = 1 Then ;MsgBox (0, "", "Path was invalid") SplashTextOn("Title", "Path was invalid", -1, -1, -1, -1, 1, "", 24) Sleep(2000) SplashOff() Exit EndIf If @error = 4 Then ;MsgBox (0, "No files", "No files were found") SplashTextOn("Title", "No files were found", -1, -1, -1, -1, 1, "", 24) Sleep(2000) SplashOff() Exit EndIf Local $iRetval ;Loop through the array For $i = 1 To $aFileList[0] $iRetval = _ReplaceStringInFile($aFileList[$i], $sFind, $sReplace) Next EndFunc Some references: https://stackoverflow.com/questions/37145369/change-path-to-picture-links-in-excel http://www.jkp-ads.com/Articles/Excel2007FileFormat.asp
  4. 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 . 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!
  5. hello guys, I'm doing one excel pivot connected to a mysql script to display it in a table array . I need you to open the excel file , the pivot is updated . where do I fix it?
  6. So, I have stumbled upon a problem with countring rows in excel files. The script I am working on will count the total number of rows of every file in the folder it was placed into. It is to my understanding that UsedRange counts rows even if they are empty, provided that a user has previously edited them. In other words, even if the file has two rows, the script might return 1000, if I edit and then leave empty cell (A,1000). How can I avoid this happening? Thanks in advance! #include <Array.au3> #include <File.au3> #include <MsgBoxConstants.au3> #include <Excel.au3> Global $Files = _FileListToArray (@ScriptDir, "*") $oExcel = _Excel_Open() $TotalNumberOfRows = 0 for $i=2 to $Files[0] Sleep(1000) $sWorkbook = @ScriptDir & "\" & $Files[$i] Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) Sleep(2000) $NumberOfRows = $oExcel.ActiveSheet.UsedRange.Rows.Count $TotalNumberOfRows= $TotalNumberOfRows + $NumberOfRows MsgBox(1, "", "Number of invoices: " & $TotalNumberOfRows) Sleep(100) _Excel_BookClose ($oWorkbook, False) Next
  7. Is there an easy way to format a cell to date or currency? I found some old threads that were not much help. I have looked at the wiki but I only see how to format as a number I assume there is not a way like I did for making the text bold. Any suggest or help would be appreciated. I am able to get the format changed to text using $oExcel.Activesheet.range("A1:L1").NumberFormat = "@" #include <Excel.au3> Global $sBox Func InputHowMany() While 1 $sBox = Number(InputBox("How many?", "How many?")) If $sBox = 0 Then $iMsg = MsgBox(1, 'Oops', 'Please enter a valid number') If $iMsg = 2 Then Exit Else Return $sBox - 1 EndIf WEnd EndFunc InputHowMany() $oExcel = _Excel_Open() $oExcel = _Excel_BookNew($oExcel) Local $t = 2 Local $w = 1 Local $c = 301 $oExcel.Activesheet.range("A1:L1").font.bold = True Do _Excel_RangeWrite($oExcel,Default, "Status", "A1") _Excel_RangeWrite($oExcel,Default, "Last Name", "B1") _Excel_RangeWrite($oExcel,Default, "Last Name", "C1") _Excel_RangeWrite($oExcel,Default, "SSN", "D1") _Excel_RangeWrite($oExcel,Default, "DOB", "E1") _Excel_RangeWrite($oExcel,Default, "Email", "F1") _Excel_RangeWrite($oExcel,Default, "Mailing Address", "G1") _Excel_RangeWrite($oExcel,Default, "City", "H1") _Excel_RangeWrite($oExcel,Default, "State", "I1") _Excel_RangeWrite($oExcel,Default, "Zip Code", "J1") _Excel_RangeWrite($oExcel,Default, "Gender", "K1") _Excel_RangeWrite($oExcel,Default, "Phone", "L1") _Excel_RangeWrite($oExcel,Default, '=B' & $c, "B" & $t) _Excel_RangeWrite($oExcel,Default, '=C' & $c, "C" & $t) _Excel_RangeWrite($oExcel,Default, '=D' & $c, "D" & $t) _Excel_RangeWrite($oExcel,Default, '=E' & $c, "E" & $t) _Excel_RangeWrite($oExcel,Default, '=F' & $c, "F" & $t) _Excel_RangeWrite($oExcel,Default, '=G' & $c, "G" & $t) _Excel_RangeWrite($oExcel,Default, '=H' & $c, "H" & $t) _Excel_RangeWrite($oExcel,Default, '=I' & $c, "I" & $t) _Excel_RangeWrite($oExcel,Default, '=J' & $c, "J" & $t) _Excel_RangeWrite($oExcel,Default, '=K' & $c, "K" & $t) _Excel_RangeWrite($oExcel,Default, '=L' & $c, "L" & $t) $c = $c + 1 $t = $t + 1 $w = $w + 1 Until $w > $sBox
  8. I have an issue with disk space on a server so I wrote a simple little script to check specific directories and save the sizes to an Excel spreadsheet. For this script, I am still using version 3.3.8.1. Everything works fine, I just have a question. #cs ---------------------------------------------------------------------------- AutoIt Version: 3.3.8.1 #ce ---------------------------------------------------------------------------- #include<date.au3> #include<excel.au3> #include<array.au3> $file = FileOpen(@ScriptDir & "\CMScriptDirList.txt", 0) If @error Then Exit ;--folder view still OK $oexcel = _ExcelBookOpen(@ScriptDir & "\CMScriptDirSizesCopy.xlsx") If @error Then Exit ;--folder view now parent folder $excelArray = _ExcelReadSheetToArray($oexcel) $lastrow = $excelArray[0][0] $lastcol = $excelArray[0][1] _ExcelWriteCell($oexcel, _NowCalc(), 1, $lastcol + 1) While 1 $line = FileReadLine($file) If @error Then ExitLoop $size = DirGetSize(StringStripWS($line, 3)) / 1024 / 1024 $iIndex = _ArraySearch($excelArray, $line, 0, 0, 0, 0, 1, 1) If @error Then ContinueLoop _ExcelWriteCell($oexcel, $size, $iIndex, $lastcol + 1) WEnd FileClose($file) _ExcelWriteFormula($oexcel, "=SUM(R2C" & $lastcol + 1 & ":R38C" & $lastcol + 1 & ")", 39, $lastcol + 1) _ExcelWriteFormula($oexcel, "=R39C" & $lastcol + 1 & "/1024", 40, $lastcol + 1) $oexcel.ActiveSheet.columns($lastcol).copy ;used to copy the format of the original last column of the spreadsheet $oexcel.ActiveSheet.columns($lastcol + 1).PasteSpecial(-4122, Default, Default, Default) ;this just pastes the format of the original last column to the new last column $oexcel.ActiveSheet.Range("A1").Select ;select cell A1 just to unselect the entire column from previous command $oexcel.columns.AutoFit ;auto sizes the column width _ExcelBookClose($oexcel, 1) ;save file when closing The script is compiled and sitting is a sub-directory on the server in question. The text file and the spreadsheet that are used are both in this same folder as well. When I navigate to the folder and run the script by double-clicking on the executable, the process runs but the folder view where I ran the script will go back up one level so when the script completes, I am in the parent folder from where I started. I have added message boxes throughout the script and have determined that the folder view goes back up one level at some point after the @error check for the file open and before the @error check for the ExcelBookOpen (where the comments are). I just wanted to know if someone can tell me why and if there is a way to prevent it. (Note: still using v3.3.8.1 on this machine but slowly converting scripts to v3.3.14.2).
  9. I am a newbie in AutoIt. May I know what is the code used to expand the width and height of excel cells because I want to insert pictures in the cell.I tried AutoFit but that doesnt work as I cant specify for the width and height. Only for column width I could expand by using .ColumnWidth but for the row I am not able to expand the row? How to do?? What is the code used ??Please help me and thank you.
  10. I am a newbie in AutoIt. May I know what is the code used to expand the width and height of excel cells because I want to insert pictures in the cell.I tried AutoFit but that doesnt work as I cant specify for the width and height. Only for column width I could expand by using .ColumnWidth but for the row I am not able to expand the row? How to do?? What is the code used ??Please help me and thank you.
  11. Good morning I would like to know if I can use the Excel UDF to manipulating a .csv file without having Office installed on the PC I'm going to work... I read somewhere that it could be done, but I'm here to ask and be sure of what I remember... I'd like to post another question... How can I retrieve the handle of a windows from a PID of an .exe? I have my script that does a ShellExecute ( which returns the PID of the .exe ), and then, switching a parameter read from a .ini file, adapt the Window on the screen ( Maximize, Minimize, On Top )... I tried, but without having success with this: #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Outfile_x64=prova.exe #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <MsgBoxConstants.au3> #include <AutoItConstants.au3> #include <WinAPIEx.au3> #include <Array.au3> Local $sFileConfigurazione = @ScriptDir & "\configurazione_exe.ini" If(FileExists($sFileConfigurazione)) Then Local $aSezioniIni = IniReadSection($sFileConfigurazione, "CONFIGURAZIONE_EXE") If @error Then MsgBox($MB_ICONERROR, "Errore!", "Errore durante l'apertura del file: " & @CRLF & $sFileConfigurazione & @CRLF & "Errore: " & @error) Else ; Lancio dell'applicativo indicato nel file di configurazione Local $iPID = ShellExecute($aSezioniIni[1][1]) Local $hWnd If($iPID <> 0) Then Local $aWinList = WinList() For $i = 1 To $aWinList[0][0] If(WinGetProcess($aWinList[$i][1] = $iPID)) Then $hWnd = $aWinList[$i][1] EndIf Next Switch($aSezioniIni[2][1]) Case $aSezioniIni[2][1] = "MIN" WinSetState($hWnd, "", @SW_MINIMIZE) Case $aSezioniIni[2][1] = "MAX" WinSetState($hWnd, "", @SW_MAXIMIZE) Case $aSezioniIni[2][1] = "TOP" WinSetOnTop($hWnd, "", $WINDOWS_ONTOP) EndSwitch EndIf EndIf EndIf It just set on top the .exe I'm launching... Thanks
  12. Good morning I was looking for a method with rename an Excel Sheet, but I didn't find a lot... So, I decided to make it in another way... Since I have to create a new Workbook, I thought that, creating a new Workbook, deleting the existing sheet, and adding a new one, would be almost the same... But I'm encountering a lot of issues, both when I delete the sheet and when I add the new sheet... This is what I do: ; Create the Excel Object... Local $oExcel_PRV_HW = _Excel_Open(False) If @error Then MsgBox($MB_ICONERROR, "Errore!", "Errore durante la creazione dell'oggetto Excel." & @CRLF & "Errore: " & @error & @CRLF & "Esteso: " & @extended) Else ; Create the Workbook with 1 worksheet... Local $oWorkbook_New = _Excel_BookNew($oExcel_PRV_HW, 1) ; Save the Workbook in order to open it and work with it... _Excel_BookSaveAs($oWorkbook_New, $sFilePreventivo, $xlOpenXMLWorkbook, True) If @error Then MsgBox($MB_ICONERROR, "Errore!", "Errore durante il salvataggio della cartella di lavoro." & @CRLF & "Errore: " & @error) EndIf ; Open the Workbook to work with... Local $oWorkbook_PRV_HW = _Excel_BookOpen($oExcel_PRV_HW, $sFilePreventivo) If @error Then MsgBox($MB_ICONERROR, "Errore!", "Errore durante l'apertura del file '" & $sFilePreventivo & "'." & @CRLF & "Errore: " & @error) Else ; Here I would add the _Excel_SheetDelete() and _Excel_SheetAdd() as I did, but they return errors... EndIf EndIf Can someone help me out, please? Thanks
  13. I want to remove the vertical page breaks so that I have only "page 1" available. Here the link to MSDN how to do it in VBA which works properly: https://msdn.microsoft.com/EN-US/library/office/ff836174.aspx What I did and doesn't work: With $oExcel_Export2Excel.Worksheets(1) .VPageBreaks(1).DragOff.Direction = -4161 ;$xlToRight = -4161 .VPageBreaks(1).DragOff.RegionIndex = 1 EndWith Local $aParam[2] = [-4161, 1] $oExcel_Export2Excel.Worksheets(1).VPageBreaks(1).DragOff($aParam) $oExcel_Export2Excel.Worksheets(1).VPageBreaks(1).DragOff(-4161, 1) $oExcel_Export2Excel.Worksheets(1).VPageBreaks(1).DragOff("Direction").Value = -4161 $oExcel_Export2Excel.Worksheets(1).VPageBreaks(1).DragOff("RegionIndex").Value = 1 $oExcel_Export2Excel.Worksheets(1).VPageBreaks(1).DragOff.Direction = -4161 $oExcel_Export2Excel.Worksheets(1).VPageBreaks(1).DragOff.RegionIndex = 1 Any further idea? I'm using Office 2013 only!
  14. Hi all, I think i´m doing something wrong. In the following code, _excel_RangeFind() does not find all occurrences. From sheet1 to sheet9 it does not find the occurrence of row 1, and on sheet10 it find it, but puts it last. Where am I wrong? Thank you very much and sorry for my inglish. The code: #include <Excel.au3> Local $oAppl = _Excel_Open(True) Local $oWorkbook= _Excel_BookNew($oAppl, 10) Local $namesheet= "hoja";<-- Default name for sheet in spanish language: hoja1, hoja2, hoja3 etc. For $x= 1 to 10;<-- $x completes the name of the excel sheet: $namesheet & $x for $y= 1 to 5 _Excel_RangeWrite($oWorkbook, $namesheet & $x , "sofia" & " " & $namesheet & $x & " " & $y,"A" & $y) Next Next Local $search= _Excel_RangeFind($oWorkbook, "sof") _ArrayDisplay($search) This is using 3.3.12 version and office 2007.
  15. Hi Everyone, I am a beginner and I am currently learning and practicing what Autoit can do, so kindly pardon if it sound's silly. What my program does ----> I had written a program where I have a FOR (i=0 to n) loop which is running for n times. Inside the FOR loop, contents of array is written into excel using _Excel_RangeWrite . _Excel_RangeWrite($oExcelDoc, $oExcelDoc.Activesheet, $arrayname, "A1") Problem ------> During every loop run the contents of column A is only altered What i intend to do ------> For every loop run (i=0,1,2,3...) I want to write the array contents into respective next adjacent excel columns i.e) For i=0 loop, every array content should be written in A Column of excel For i = 1 loop, every array content should be written in B Column of excel. Can anyone give me an idea of how can i do this? Thanks
  16. Why is my code not writing X in cell D1? #include <Excel.au3> Global $r = 1,$oExcel Excel () Func Excel() While ProcessExists("EXCEL.EXE") $ms = MsgBox(5,"","Process error. You have an Excel sheet open. You must close it in order to let this program work. Please close it now.") If $ms=2 Then Exit WEnd Local $sExcelFile = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "(*.xlsx)") If FileExists($sExcelFile) Then $oExcel = _Excel_Open () $oWorkbook = _Excel_BookOpen($oExcel,$sExcelFile) ;this will open the chosen xls file. Else $oExcel = _Excel_Open() $oWorkbook = _Excel_BookNew($oExcel, 2);this is here to create the xls file if it does not exist. EndIf EndFunc Sleep (2000) _Excel_RangeWrite($oExcel,Default, "X", "D" & $r) Exit
  17. How to avoid save violation error in excel?
  18. Hi I want to select an excel file from a folder using selenium. The file name includes the data stamp. But, only that file will be existing in the folder. How to do it please help with the code
  19. I was wondering if it is possible to make this code skip to the next row if it reads a blank line? I tried this. If $aArray = "" Then $r += 1 EndIf #include <Excel.au3> Local $r = 1 Local $aArray = _Excel_RangeRead($oWorkbook) ;~ If $iBox is greater than no. of rows in $aArray then $iBox equals the number of rows in $aArray If $iBox > (UBound($aArray) - 1) Then $iBox = UBound($aArray) - 1 For $i = 1 To UBound($aArray) - 1 ;$i =0 Start from row A If $aArray = "" Then $r += 1 EndIf $sR0 = $aArray[$i][0] ;status $sR1 = $aArray[$i][1] ;first name $sR2 = $aArray[$i][2] ;Last name $sR3 = $aArray[$i][3] ;Last 4 SSN $sR4 = $aArray[$i][4] ;DOB $sR5 = $aArray[$i][5] ;Email Address $sR6 = $aArray[$i][6] ;Mailling Address $sR7 = $aArray[$i][7] ;City $sR8 = $aArray[$i][8] ;state $sR9 = $aArray[$i][9] ;Zip Code $sR10 = $aArray[$i][10] ;Gender $sR11 = $aArray[$i][11] ;Phone WinSetState ("ADM.MCK - Registration Management Desktop","",@SW_MAXIMIZE) WinWaitActive("[CLASS:Notepad]", "", 10) ControlSend("[CLASS:Notepad]", "", "", ("{F8}")) Sleep (500) ControlSend("[CLASS:Notepad]", "", "", ("{F8}")) Sleep (500) ControlSend("[CLASS:Notepad]", "", "", ("N")) ControlSend("[CLASS:Notepad]", "", "", ("{TAB}")) Sleep (3000) ControlSend("[CLASS:Notepad]", "", "Edit1", $sR1 & @CR) Sleep (200) ControlSend("[CLASS:Notepad]", "", "", ("{TAB}")) Sleep (200) ControlSend("[CLASS:Notepad]", "", "Edit1", $sR2 & @CR) Sleep (200) ControlSend("[CLASS:Notepad]", "", "", ("{TAB}")) Sleep (200) ControlSend("[CLASS:Notepad]", "", "Edit1", $sR6& @CR) Sleep (200) ControlSend("[CLASS:Notepad]", "", "", ("{TAB}")) Sleep (200) ControlSend("[CLASS:Notepad]", "", "Edit1", $sR7 & @CR) Sleep (200) ControlSend("[CLASS:Notepad]", "", "Edit1", $sR8 & @CR) Sleep (200) ControlSend("[CLASS:Notepad]", "", "Edit1", $sR9 & @CR) Sleep (200) ControlSend("[CLASS:Notepad]", "", "Edit1", $sR11& @CR) Sleep (200) ControlSend("[CLASS:Notepad]", "", "Edit1", $sR5 & @CR) ;Email Sleep (200) ControlSend("[CLASS:Notepad]", "", "Edit1", $sR5 & @CR) ;Email Sleep (200) ControlSend("[CLASS:Notepad]", "", "", (FormatDate($sR4) & @CR)) $r += 1
  20. Hi guys! How are you? Hope you're fine I've been using Excel UDF for a while, and I always wanted to post this question: "Why, everytime I set the paramter $bVisible = False of the _Excel_Open() function, IF I HAVE AN EXCEL SHEET OPENED, I still can see the Excel opening and doing what I wrote in the script? And, in this case, how can I avoid this?" Thanks
  21. I am not sure what is happing at all, unfortunatlly there is no way I can put a full running code. When I enter the first and last name it works fine, However when I get to the date of birth it puts in '19760703000000' I can't figure out why "7/3/1976" is the value before the formant and "07031976" is after the format. I want it to pull the value after the format. "07031976" $r = 1 Local $aArray = _Excel_RangeRead($oExcel, Default, Default,Default,False) For $i = 1 To UBound($aArray) - 1 ;$i =0 Start from row A $sR1 = $aArray[$i][0] ;status $sR2 = $aArray[$i][1] ;first name $sR3 = $aArray[$i][2] ;Last name $sR4 = $aArray[$i][4] ;DOB $sR5 = $aArray[$i][5] ;Email Address WinWaitActive ("[CLASS:Notepad]") ControlSend("[CLASS:Notepad]", "", "Edit1", $sR3 & ',' & $sR2 & @CR) Sleep (2000) ControlSend("[CLASS:Notepad]", "", "Edit1",("{TAB}")) Sleep (3000) ControlSend("[CLASS:Notepad]", "", "Edit1", $sR4 & @CR) ControlSend("[CLASS:Notepad]", "", "Edit1",("{ENTER}")) ControlSend("[CLASS:Notepad]", "", "Edit1",("{F12}") $r = $r + 1 If $r > $sBox Then Exit Endif Next auto it demo.xlsx - excel that I am using. Edit: I also want to mention I have tried Local $aArray = _Excel_RangeRead($oExcel, Default, Default,3) When I do this not even the name first and last name will write.
  22. Salve Amici, in un mio progetto vorrei utilizzare una dll per creare dei file excel senza utilizzare l'applicativo Excel. Ora dalla versione 2007 di Office la Microsoft utilizza per i file un nuovo formato aperto "Microsoft Open XML format". in PHP questo è molto semplice utilizzando la Libreria PHPExcel (http://www.codeplex.com/PHPExcel) Cercando in rete ho trovato una dll che dovrebbe fare lo stesso (https://code.google.com/archive/p/excellibrary/) ma non ho assolutamente le capacita di integrare la Dll in autoit, qualcuno mi può aiutare ? Sarebbe veramente molto efficiente poter creare e manipolare file excel in autoit senza dover caricare in memoria l'applicativo Excel. Grazie -.-.-.-.-. Hello friends, in my project I want to use a dll to create the excel file without using the Excel application. Now from the Microsoft Office 2007 version uses for the files a new open format "Microsoft Open XML format". PHP This is very simple using the Library PHPExcel (http://www.codeplex.com/PHPExcel) Searching the net I found a dll that should do the same (https://code.google.com/archive/p/excellibrary/) but I have absolutely the ability to integrate the .dll in autoit, anyone can help me? It would really be very efficient to create and manipulate Excel files into memory autoit without having to load the Excel application. Thank you
  23. I have price-list in xls. When I open it by _Excel_Open and _Excel_BookOpen and after that close by _Excel_Close, I have a message about "Save changes?" I try open it in read-only mode, but no changes made. I still see annoing message. Windows 10 prof, MS Office 2007. #include <Excel.au3> #include <MsgBoxConstants.au3> ConsoleWrite(@AutoItVersion) Global $sPriceFile = @ScriptDir&"\opt_pr_list-mini.xls" Local $oExcel = _Excel_Open(False, False, False, True)  If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, $sPriceFile, True )     ; readonly If @error Then     MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead", "Error opening workbook @error = " & @error & ", @extended = " & @extended)     _Excel_Close($oExcel)     Exit EndIf _Excel_Close($oExcel, False, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example 2", "Error closing the Excel application." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Exit What I do wrong? Thanks. opt_pr_list-mini.xls.zip
  24. Hi there, i'm looking for a script to take max number of a column in Excel. Ex : Column C , i have : 12 13 22 123 154 .... ..... ..... 134534 (About 134600 rows) So, How to know which is the max number in Column C. I have this code, but it take me a lot of time >"< So... please help me a faster code. $x = 3 $CloseCheck1 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+1) $CloseCheck2 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+2) $CloseCheck3 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+3) $CloseCheck4 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+4) $CloseCheck5 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+5) $CloseCheck6 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+6) $CloseCheck7 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+7) $CloseCheck8 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+8) $CloseCheck9 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+9) $CloseCheck10 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+10) Local $aArray = StringSplit($CloseCheck1&","&$CloseCheck2&","&$CloseCheck3&","&$CloseCheck4&","&$CloseCheck5&","&$CloseCheck6&","&$CloseCheck7&","&$CloseCheck8&","&$CloseCheck9&","&$CloseCheck10,",") $DMAX = _ArrayMax($aArray, 1, 1) $DMIN = _ArrayMin($aArray, 1, 1) $n = 11 While 1 $CloseCheckn = _Excel_RangeRead($oWorkbook, Default, "C"&$x+n) If $CloseCheckn > $DMAX Then Global $DMAX = $CloseCheckn EndIf If $CloseCheckn < $DMIN Then Global $MIN = $CloseCheckn EndIf If $CloseCheckn = "" Then ExitLoop EndIf $n = $n + 1 WEnd _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $DMAX, "P1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $DMIN, "P2") Thanks
  25. I'm trying the Excel.udf, my starting point are the examples found in the help. My problem is that i can't find any working example of _Excel_RangeInsert ... made many tries but i can't find the reason why ... New/existing Worbook nothing changes ... i tried _Excel_RangeWrite without any problem, but with _Excel_RangeInsert i always get an @error=3 and @extended=-2147352562 ... What could i be missing ? Here's the minimalistic snippet i'm playing with : Local $sWorkbook = @ScriptDir & "\pixel.xls" Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel ...", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) $dummy=_Excel_RangeInsert($oWorkbook.Activesheet, "1:3") If @error Then    $a=@extended    MsgBox($MB_SYSTEMMODAL, "Excel ...", "Error inserting" & @CRLF & "@error = " & @error & ", @extended = " & @extended) Else    MsgBox($MB_SYSTEMMODAL, "Excel ...", "Rows successfully inserted") EndIf ConsoleWrite ($dummy & chr(13) & $a & Chr(13)) I tried every possible (...) syntax for the range object, without success ... I use the latest releases of AutoIt and Office 97 ... Thanks for your help ...