Jump to content
Sign in to follow this  
soonyee91

No idea on how to rectify this problem

Recommended Posts

I think there is some problem in the func code. I can't seem to get what I want.

Is there a way to ignore the error and proceed with the script.

I do remember that Vba has this "On error resume next".

Is there a way to ignore this error in autoit even when $rng = 0?

Share this post


Link to post
Share on other sites

You need to add a COM error handler to your script. See ObjEvent in the help file for an example.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.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

So far I have able to trap the com error using the method you suggest to me.

However, it will clear the whole page of my sheet that does not contain printarea.

Can you point it out where is my mistake?

MyErrFunc will trap the error and continue to execute the code for that specific line which I don't want.

Is it possible to resume to next loop after error encounter(what I mean is just skip those error line??

Is there any ways to suppress that error and proceed?

Func MyErrFunc($oMyError)

Local $HexNumber
Local $strMsg

$HexNumber = Hex($oMyError.Number, 8) 
$strMsg = "Error Number: " & $HexNumber & @CRLF 
$strMsg &= "WinDescription: " & $oMyError.WinDescription & @CRLF 
$strMsg &= "Script Line: " & $oMyError.ScriptLine & @CRLF 
MsgBox(0, "ERROR", $strMsg)
SetError(1)
    
EndFunc

Func _ClearOutsidePrintArea()
Edited by soonyee91

Share this post


Link to post
Share on other sites

If you trap the error AutoIt simply continues with the next statement.

So you still have to check for success after you retrieved the PrintArea:

Local $rng =$oExcel.ActiveSheet.PageSetup.PrintArea
If @error or Not IsObj($rng) Then Return SetError(1,0,'')

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.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

Like you said:"If you trap the error AutoIt simply continues with the next statement."

How should I trap the error and simply skip it and continue with the next statement?

My code structure is open the selected opened workbok.

Then perform this clearPrintarea function .

But the problem is the code will not sustain empty printarea and do not suppress the code or skip.

Can you provide a better solution?

Or should I do in this way?? create 2 function, One is with the condition that has print area, another is with the condition that do not has the print area

Edited by soonyee91

Share this post


Link to post
Share on other sites

Some detailed information is needed.

  • Which version of Excel do you run?
  • Which version of AutoIt do you run?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.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

My intention of this script is to run excel version 2000 but I do try it in 2010(it works though)

My version of autoit is 3.3.8.1

Share this post


Link to post
Share on other sites

I'm running Excel 2010. If no PrintArea is set by the user then the whole sheet is returned. So no error handler seems to be needed.

This works fine for me with/without a pre-set PrintArea by the user.

#include <Excel.au3>
Global $oExcel = _ExcelBookOpen(@ScriptDir & "\_Excel4.xls")
;$oExcel.ActiveSheet.PageSetup.PrintArea = "A1:B2" ; manually set a PrintArea
_ClearOutsidePrintArea()
Exit

Func _ClearOutsidePrintArea()
    Local $rng = $oExcel.Range($oExcel.ActiveSheet.PageSetup.PrintArea)
    If @error Or Not IsObj($rng) Then Return SetError(1, 0, '')
    Local $FirstEmptyCol = $rng.Columns.Count + 1
    Local $FirstEmptyRow = $rng.Rows.Count + $rng.Cells(1).Row
    $oExcel.Range($oExcel.Cells(1, $FirstEmptyCol), $oExcel.Cells(1, 256)).Entirecolumn.clear
    $oExcel.Range($oExcel.Cells($FirstEmptyRow, 1), $oExcel.Cells($oExcel.Rows.Count, 1)).EntireRow.clear
EndFunc   ;==>_ClearOutsidePrintArea
Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.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

A small correction is needed. If the worksheet is empty, then a COM error will be raised. So now the script has to look like:

#include <Excel.au3>
Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
Global $oExcel = _ExcelBookOpen(@ScriptDir & "\_Excel4.xls")
;$oExcel.ActiveSheet.PageSetup.PrintArea = "A1:B2" ; manually set a PrintArea
_ClearOutsidePrintArea()
Exit

Func _ClearOutsidePrintArea()
    Local $rng = $oExcel.Range($oExcel.ActiveSheet.PageSetup.PrintArea)
    If @error Or Not IsObj($rng) Then Return SetError(1, 0, '')
    Local $FirstEmptyCol = $rng.Columns.Count + 1
    Local $FirstEmptyRow = $rng.Rows.Count + $rng.Cells(1).Row
    $oExcel.Range($oExcel.Cells(1, $FirstEmptyCol), $oExcel.Cells(1, 256)).Entirecolumn.clear
    $oExcel.Range($oExcel.Cells($FirstEmptyRow, 1), $oExcel.Cells($oExcel.Rows.Count, 1)).EntireRow.clear
EndFunc   ;==>_ClearOutsidePrintArea

Func _ErrFunc()
EndFunc   ;==>_ErrFunc

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.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

Thanks man!!!! I when use your script and run it. No problem occurs.

However when I run on my original script problem comes out.

My final solution is moved my part of code to suite yours. I think most likely there is something wrong in my part.

After this moving section. Everything is solve. Thanks for guiding me all the way!!!

Share this post


Link to post
Share on other sites

It has taken a few rounds but I'm glad that your problem could be solved :D


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.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
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...