soonyee91 Posted May 31, 2013 Share Posted May 31, 2013 (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 May 31, 2013 by soonyee91 Link to comment Share on other sites More sharing options...
water Posted May 31, 2013 Share Posted May 31, 2013 Just use $rng = $oExcel.ActiveSheet.PageSetup.PrintArea My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
soonyee91 Posted May 31, 2013 Author Share Posted May 31, 2013 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? Link to comment Share on other sites More sharing options...
water Posted May 31, 2013 Share Posted May 31, 2013 To check if the range is an object use: If IsObj($rng) Then ... My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
soonyee91 Posted May 31, 2013 Author Share Posted May 31, 2013 (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 May 31, 2013 by soonyee91 Link to comment Share on other sites More sharing options...
water Posted May 31, 2013 Share Posted May 31, 2013 Can you please tell me what you try to achieve with this code? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
soonyee91 Posted May 31, 2013 Author Share Posted May 31, 2013 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 Link to comment Share on other sites More sharing options...
water Posted May 31, 2013 Share Posted May 31, 2013 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
soonyee91 Posted May 31, 2013 Author Share Posted May 31, 2013 (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 May 31, 2013 by soonyee91 Link to comment Share on other sites More sharing options...
water Posted May 31, 2013 Share Posted May 31, 2013 You set $oExcel to the Excel application object, right? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
soonyee91 Posted May 31, 2013 Author Share Posted May 31, 2013 (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 May 31, 2013 by soonyee91 Link to comment Share on other sites More sharing options...
water Posted May 31, 2013 Share Posted May 31, 2013 (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 June 1, 2013 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
soonyee91 Posted June 1, 2013 Author Share Posted June 1, 2013 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? Link to comment Share on other sites More sharing options...
water Posted June 1, 2013 Share Posted June 1, 2013 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
soonyee91 Posted June 1, 2013 Author Share Posted June 1, 2013 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? Link to comment Share on other sites More sharing options...
water Posted June 1, 2013 Share Posted June 1, 2013 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
soonyee91 Posted June 1, 2013 Author Share Posted June 1, 2013 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 Link to comment Share on other sites More sharing options...
water Posted June 1, 2013 Share Posted June 1, 2013 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
soonyee91 Posted June 1, 2013 Author Share Posted June 1, 2013 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? Link to comment Share on other sites More sharing options...
water Posted June 1, 2013 Share Posted June 1, 2013 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now