No idea on how to rectify this problem

[soonyee91]

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 May 31, 2013 by soonyee91

[water]

Just use

$rng = $oExcel.ActiveSheet.PageSetup.PrintArea

[soonyee91]

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?

[water]

To check if the range is an object use:

If IsObj($rng) Then ...

[soonyee91]

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 May 31, 2013 by soonyee91

[water]

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

[soonyee91]

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

[water]

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()

[soonyee91]

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 May 31, 2013 by soonyee91

[water]

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

[soonyee91]

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 May 31, 2013 by soonyee91

[water]

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 June 1, 2013 by water

[soonyee91]

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?

[water]

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.

[soonyee91]

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?

[water]

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?

[soonyee91]

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

[water]

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

[soonyee91]

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?

[water]

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