Jump to content
alguty8712

skip alerts on _Excel_RangeReplace

Recommended Posts


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

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites


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

 

Share this post


Link to post
Share on other sites

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 2021-04-14 - Version 1.5.3.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-04-13 - Version 1.6.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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)

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-04-14 - Version 1.5.3.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-04-13 - Version 1.6.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

_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 2021-04-14 - Version 1.5.3.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-04-13 - Version 1.6.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Share the  full script, for help , 
Attach doesn't work for me or I don't know which item to link to

 

#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

 

Share this post


Link to post
Share on other sites

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 2021-04-14 - Version 1.5.3.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-04-13 - Version 1.6.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...