alguty8712 Posted May 30, 2020 Share Posted May 30, 2020 I'm looking for a way to jump to the next in case the replacement is empty, or disable alerts on each replacement. Func _Iniciar() Global $oExcel_1 = ObjGet("", "Excel.Application") Global $wkbook = $oExcel_1.ActiveWorkBook ;Establece Libro Activo WinActivate($oExcel_1 ) _Excel_RangeReplace($wkbook , Default, Default, "AM", "") if Not @error then _Excel_RangeReplace($wkbook , Default, Default, "AM", "") EndIf _Excel_RangeReplace($wkbook , Default, Default, "PM", "") if Not @error then _Excel_RangeReplace($wkbook , Default, Default, "PM", "") EndIf _Excel_RangeReplace($wkbook , Default, Default, "::", ":") if Not @error then _Excel_RangeReplace($wkbook , Default, Default, "::", ":") EndIf end Func Any suggestion please Link to comment Share on other sites More sharing options...
Subz Posted May 30, 2020 Share Posted May 30, 2020 To the next what? You're replacing all searches within the active worksheet: Here is the code using the Excel udf #include <Excel.au3> Global $g_oExcel = _Excel_Open() Global $g_sWorkbook = @ScriptDir & "\SpreadsheetTest.xlsx" Global $g_oWorkbook $g_oWorkbook = _Excel_BookAttach($g_sWorkbook) If @error Then $g_oWorkbook = _Excel_BookOpen($g_oExcel, $g_sWorkbook) _Iniciar() Func _Iniciar() _Excel_RangeReplace($g_oWorkbook , Default, Default, "::", ":") If Not @error then _Excel_RangeReplace($g_oWorkbook , Default, Default, "::", ":") EndIf _Excel_RangeReplace($g_oWorkbook , Default, Default, "AM", "") If Not @error then _Excel_RangeReplace($g_oWorkbook , Default, Default, "AM", "") EndIf _Excel_RangeReplace($g_oWorkbook , Default, Default, "PM", "") If Not @error then _Excel_RangeReplace($g_oWorkbook , Default, Default, "PM", "") EndIf EndFunc Link to comment Share on other sites More sharing options...
alguty8712 Posted May 30, 2020 Author Share Posted May 30, 2020 The issue is that when it cannot find what to replace, an msgbox appears and as there are several conditions it is annoying +++One possibility I have found is to use RageFind if _Excel_RangeFind($wkbook, " a.m") <> "" Then _Excel_RangeReplace($wkbook , Default, Default, " a.m", "") EndIf if _Excel_RangeFind($wkbook, " AM") <> "" Then _Excel_RangeReplace($wkbook , Default, Default, " AM", "") EndIf Link to comment Share on other sites More sharing options...
water Posted May 30, 2020 Share Posted May 30, 2020 Can you please describe what you try to achieve? Why do you need to run the same replace statement two times? The Excel UDF does not show a MsgBox. So the MsgBox is displayed either by your script or Excel. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
alguty8712 Posted May 30, 2020 Author Share Posted May 30, 2020 I want to leave only the time in a cells, but the files have variations in the time example (AM, AM., AM, to M, etc.) I want the system to search for all matches and replace, but when I can't find a message from shows "Excel found no results" I was looking for something like (Display alerts = False) Link to comment Share on other sites More sharing options...
Subz Posted May 30, 2020 Share Posted May 30, 2020 Did you test the script I posted? As Water pointed out, the Excel UDF doesn't display alerts. Link to comment Share on other sites More sharing options...
alguty8712 Posted May 30, 2020 Author Share Posted May 30, 2020 I did, Link to comment Share on other sites More sharing options...
Subz Posted May 30, 2020 Share Posted May 30, 2020 Can you post an example spreadsheet, so we can test? I created a basic spreadsheet and don't get any errors, also which version of Office are you using? Link to comment Share on other sites More sharing options...
water Posted May 30, 2020 Share Posted May 30, 2020 Use the Excel UDF as suggested by Subz. _Excel_Open by default suppresses all Alerts. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
alguty8712 Posted May 30, 2020 Author Share Posted May 30, 2020 10 minutes ago, water said: Use the Excel UDF as suggested by Subz. _Excel_Open by default suppresses all Alerts. and how would you use it with an open file Link to comment Share on other sites More sharing options...
water Posted May 30, 2020 Share Posted May 30, 2020 _Excel_Open starts Excel or connects to an already running instance of Excel. To connect to an already opened workbook you need to call _Excel_BookAttach in addition. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
alguty8712 Posted May 30, 2020 Author Share Posted May 30, 2020 Share the full script, for help , Attach doesn't work for me or I don't know which item to link to expandcollapse popup#Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Outfile_x64=Revisar Excel.Exe #AutoIt3Wrapper_UseX64=y #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #include <Excel.au3> #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <GUIListBox.au3> #include <WindowsConstants.au3> #include <array.au3> #include <IE.au3> #include <MsgBoxConstants.au3> _Seleccione() Func _Seleccione() Local $Libros_FAT = WinList("[REGEXPTITLE:(?i)(.*Excel.*)]") Global $Form1 = GUICreate("Seleccione Formato Acompañamiento", 454, 233) $List1 = GUICtrlCreateList("", 16, 8, 430, 149, BitOR($LBS_STANDARD, $LBS_EXTENDEDSEL)) ; BitOR style taken from help file For $i = 1 To Ubound($Libros_FAT) - 1 _GUICtrlListBox_InsertString($List1,$Libros_FAT[$i][0]) Next _GUICtrlListBox_SetSel($List1, 0) ; Deja al primero seleccionado $Button1 = GUICtrlCreateButton("SELECCIONAR", 180, 176, 96, 25) $Button2 = GUICtrlCreateButton("CERRAR", 320, 176, 96, 25) GUISetState(@SW_SHOW) Local $aaa While 1 $aaa = GUIGetMsg() Select Case $aaa = $GUI_EVENT_CLOSE ExitLoop Case $aaa = $Button1 $selItems = _GUICtrlListBox_GetSelItemsText($List1) ; Read the selected item characteristics to an array Global $oExcel = $selItems[1] GuiDelete($Form1) _Iniciar() ExitLoop Case $aaa = $Button2 Exit EndSelect WEnd EndFunc Func _Iniciar() Global $oExcel_1 = ObjGet("", "Excel.Application") Global $wkbook = $oExcel_1.ActiveWorkBook ;Establece Libro Activo WinActivate($oExcel_1 ) if _Excel_RangeFind($wkbook, " a.m") <> @error Then _Excel_RangeReplace($wkbook , Default, Default, " a.m", "") EndIf if _Excel_RangeFind($wkbook, " AM") <> "" Then _Excel_RangeReplace($wkbook , Default, Default, " AM", "") EndIf if _Excel_RangeFind($wkbook, " PM") <> "" Then _Excel_RangeReplace($wkbook , Default, Default, " PM", "") EndIf if _Excel_RangeFind($wkbook, "::") <> "" Then _Excel_RangeReplace($wkbook , Default, Default, "::", ":") EndIf if _Excel_RangeFind($wkbook, "a.m.") <> "" Then _Excel_RangeReplace($wkbook , Default, Default, "a.m.", "") EndIf if _Excel_RangeFind($wkbook, "p.m.") <> "" Then _Excel_RangeReplace($wkbook , Default, Default, "p.m.", "") EndIf if _Excel_RangeFind($wkbook, "a.m") <> "" Then _Excel_RangeReplace($wkbook , Default, Default, "a.m", "") EndIf if _Excel_RangeFind($wkbook, "p.m") <> "" Then _Excel_RangeReplace($wkbook , Default, Default, "p.m", "") EndIf if _Excel_RangeFind($wkbook, "AM") <> "" Then _Excel_RangeReplace($wkbook , Default, Default, "AM", "") EndIf if _Excel_RangeFind($wkbook, "PM") <> "" Then _Excel_RangeReplace($wkbook , Default, Default, "PM", "") EndIf if _Excel_RangeFind($wkbook, "am") <> "" Then _Excel_RangeReplace($wkbook , Default, Default, "am", "") EndIf if _Excel_RangeFind($wkbook, "am") <> "" Then _Excel_RangeReplace($wkbook , Default, Default, "am", "") EndIf _Excel_RangeReplace($wkbook , Default, $wkbook.Activesheet.Range("J6:L27"), "", "0") $wkbook.Activesheet.Range("F10:G25").Select Sleep(100) Send("!ORF") Sleep(200) SEND("h") Sleep(400) Send("{ENTER}") Sleep(500) Sleep(1200) Celdas() EndFunc Func Celdas() $msgBox = MsgBox(4, "Proceso Pausado", "Desea Modificar tamaño de celdas") ; YES and NO so NO's ID = 7 and YES = 6 If $msgBox = 6 Then _Excel_RangeInsert($wkbook.ActiveSheet, "6:6") ; "Before row, number of rows to insert " Sleep(300) $wkbook.Activesheet.Range("a7:Ac27").Font.Size = 9 $wkbook.Activesheet.Range("a6:A27").RowHeight = 15 $wkbook.Activesheet.Range("D6:AC6").ColumnWidth = 15 $wkbook.Activesheet.Range("C6").ColumnWidth = 40 Else _Seleccione() EndIf EndFunc Link to comment Share on other sites More sharing options...
water Posted May 30, 2020 Share Posted May 30, 2020 Just my 2 cents worth: WinActivate($oExcel_1 ) Winactivate doesn't work the way you use it. Please check the help file. I suggest to use the naming convention suggested for AutoIt. $aaa doesn't tell anything. $oExcel_1 holds the Excel application object whereas $oExcel holds the selected item from the listbox. You need to do more error checking. Your script will crash if Excel doesn't run when you start your script. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now