Sign in to follow this  
Followers 0
soonyee91

No idea on how to rectify this problem

31 posts in this topic

#1 ·  Posted (edited)

I have this vba script which I have convert it to autoit script. However it give me error code on:

 ==> The requested action with this object has failed.:
$rng = $oExcel.Range($oExcel.ActiveSheet.PageSetup.PrintArea)
$rng = $oExcel.Range($oExcel.ActiveSheet.PageSetup.PrintArea)^ ERROR
 
This is the original vba scrip:
Sub Test()
Dim rng As Range
Dim FirstEmptyRow As Long
Dim FirstEmptyCol As Integer

With ActiveSheet.PageSetup
Set rng = Range(.PrintArea)
End With

FirstEmptyCol = rng.Cells(rng.Cells.Count).Column + 1
FirstEmptyRow = rng.Rows.Count + rng.Cells(1).Row

Range(Cells(1, FirstEmptyCol), Cells(1, 256)).EntireColumn.clear
Range(Cells(FirstEmptyRow, 1), Cells(Rows.Count, 1)).EntireRow.clear
End Sub

 This is the autoit script which I convert from it:

; $rng is to obtain the area of Printarea
$rng = $oExcel.Range($oExcel.ActiveSheet.PageSetup.PrintArea)

$FirstEmptyCol = $rng.Cells($rng.Cells.Count).Column + 1
$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

Anyone got ideas on where is the place I do wrong?

Edited by soonyee91

Share this post


Link to post
Share on other sites



Just use

$rng = $oExcel.ActiveSheet.PageSetup.PrintArea

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

This only solve the 1st syntax

After that I encounter another problem:

==> Variable must be of type "Object".:
$FirstEmptyCol = $rng.Cells($rng.Cells.Count).Column + 1
$FirstEmptyCol = $rng^ ERROR
 
So how to make sure the variable is object?

Share this post


Link to post
Share on other sites

To check if the range is an object use:

If IsObj($rng) Then ...

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

Based on my testing, the $rng is not and object

However, autoit declare the following coding is object based.

How should I convert the following lines to non -object script?

$FirstEmptyCol = $rng.Cells($rng.Cells.Count).Column + 1 $FirstEmptyRow = $rng.Rows.Count + $rng.Cells(1).Row
$FirstEmptyRow = rng.Rows.Count + rng.Cells(1).Row
Edited by soonyee91

Share this post


Link to post
Share on other sites

Can you please tell me what you try to achieve with this code?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Sorry for not pointing the direct problem.

My intention is to clear everything outside of the excel print area.

The first syntax solved

Second syntax I think the vba is count all the empty row and column

last syntax is to clear everything in the empty row and column

; the following vba code is to obtain the range of Printarea
  With ActiveSheet.PageSetup
  Set rng = Range(.PrintArea)
  End With
Solved using: $rng = $oExcel.ActiveSheet.PageSetup.PrintArea

; I believe the following code is to count the number of empty column/row
FirstEmptyCol = rng.Cells(rng.Cells.Count).Column + 1
FirstEmptyRow = rng.Rows.Count + rng.Cells(1).Row

;The last part is to clear everything that has define earlier in variable
Range(Cells(1, FirstEmptyCol), Cells(1, 256)).EntireColumn.clear
Range(Cells(FirstEmptyRow, 1), Cells(Rows.Count, 1)).EntireRow.clear

Share this post


Link to post
Share on other sites

Example to set the PrintArea and then clear all cells outside the printarea:

$oExcel.ActiveSheet.PageSetup.PrintArea = "A1:C3"

    Local $rng = $oExcel.Range($oExcel.ActiveSheet.PageSetup.PrintArea)

    ; FirstEmptyCol: Number of the first column right to the PrintArea
    ; FirstEmptyRow: Number of the first row below the PrintArea
    Local $FirstEmptyCol = ($rng.Columns.Count) + 1
    Local $FirstEmptyRow = $rng.Rows.Count + $rng.Cells(1).Row
    
    ; Clear all cells outside the printarea
    $oExcel.Range($rng.Cells(1, $FirstEmptyCol), $rng.Cells(1, 256)).EntireColumn.Clear()
    $oExcel.Range($FirstEmptyRow & ":" & $oExcel.ActiveSheet.UsedRange.Rows.Count).EntireRow.Clear()

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

I try to use the code you provided just now. Facing this problem:

==> The requested action with this object has failed.:
$oExcel.Range($rng.Cells(1, $FirstEmptyCol), $rng.Cells(1, 256)).EntireColumn.Clear
$oExcel.Range($rng.Cells(1, $FirstEmptyCol), $rng.Cells(1, 256)).EntireColumn.Clear^ ERROR
Any reason on this?
Edited by soonyee91

Share this post


Link to post
Share on other sites

You set $oExcel to the Excel application object, right?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

Yes, I set it as excel application object. So, you mean if I want this code to work I have to do open the excel file without setting it as object?

Edited by soonyee91

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

Open the Excel application and set $oExcel to the application object.

Open the workbook you need and display the sheet you want to process.

My script code processes the active worksheet so no object for the sheet is needed.

I tested the posted script with Excel 2010.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

I do try your code without setting it as object. It works very well with .xls or .xlsx.

The problem is I have originally set my $oExcel as object in the beginning of the code.

Is there a way to rewrite the sentence to allow it to run as $oExcel in object condition?

Or Should I rewrite the my coding to suit this code functionality?

Share this post


Link to post
Share on other sites

You just need to make sure that $oExcel contains the Excel application object. If this is used in your script for a workbook or worksheet, then replace $oExcel with $oAppl in my code and set it to the Excel application object.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

I think I figure out why the code keep on failing! It is not because of the Object issue.

When I try to read the variable that contain in the $rng. I found out that if there is an active worksheet that contain no printarea. It will return 0 and stored in the variable.

How can I continue the execution of the code even the variable content is equal to zero???

What I say is for example. I have 3 sheets. 2 sheets with print area that set up earlier, 1 sheet with print area not set up.

2 sheets will return $rng = $?$?:$?$

1 sheet will return $rng =0

And this zero cause my code to fail.

Anyway to fix it?

Share this post


Link to post
Share on other sites

If there is no printarea then the whole script is useless. How should it clear all columns and rows outside the printarea when there is no printarea?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

What I mean is if there is 0 in $rng do not proceed with the script? Because I will loop though multiple sheet in activeworkbook

Share this post


Link to post
Share on other sites

I would check if the range is an object.

Local $rng = $oExcel.Range($oExcel.ActiveSheet.PageSetup.PrintArea)
If Not IsObj($rng) Then Return ; If you put everything into a function

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

How should I create this function? I'm not familiar with creating function

Should it be like this:

Func _ClearOutsidePrintArea ($rng)
If @error Then return SetError(1,0,'')
$rng =$oExcel.ActiveSheet.PageSetup.PrintArea$FirstEmptyCol = ($oExcel.Range($rng).Columns.Count) + 1
$FirstEmptyRow = $oExcel.Range($rng).Rows.Count + $oExcel.Range($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

Is this how I Should write function?

Share this post


Link to post
Share on other sites

Something like this:

Func _ClearOutsidePrintArea()
    Local $rng =$oExcel.ActiveSheet.PageSetup.PrintArea
    If @error or Not IsObj($rng) Then Return SetError(1,0,'')
    Local $FirstEmptyCol = ($oExcel.Range($rng).Columns.Count) + 1
    Local $FirstEmptyRow = $oExcel.Range($rng).Rows.Count + $oExcel.Range($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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - 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  
Followers 0