skip alerts on _Excel_RangeReplace

[alguty8712]

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

 

[Subz]

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

 

[alguty8712]

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

 

[water]

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.

[alguty8712]

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)

 

[Subz]

Did you test the script I posted?  As Water pointed out, the Excel UDF doesn't display alerts.

[alguty8712]

 I did,

 

[Subz]

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?

[water]

Use the Excel UDF as suggested by Subz. _Excel_Open by default suppresses all Alerts.

[alguty8712]

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

[water]

_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.

[alguty8712]

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

 

[water]

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.