Jump to content

Need help to convert vba code to autoit code


Go to solution Solved by soonyee91,

Recommended Posts

Posted (edited)

This is what I have tried so far

Original vba code:

Sub DDD()
Dim rng As Range, lastRow As Long
Dim lastCol As Long, i As Long
Dim rw As Range, col As Range
Set rng = ActiveSheet.UsedRange

lastRow = rng.Rows(rng.Rows.Count).Row
lastCol = rng.Columns(rng.Columns.Count).Column

For i = lastRow To 1 Step -1
Set rw = Rows(i)
If Intersect(rw, Range("Print_Area")) Is Nothing Then
rw.EntireRow.Delete
End If
Next
For i = lastCol To 1 Step -1
Set col = Columns(i)
If Intersect(col, Range("Print_Area")) Is Nothing Then
col.EntireColumn.Delete
End If
Next
ActiveSheet.UsedRange
End Sub

Autoit Code: (Conversion not completed)

Syntax error on this line: "If $oExcel.Application.intersect($rw, $oExcel.Range("Print_Area")) = nothing Then"

Func _ClearOutsidePrintArea()
    
        Local $rng = $oExcel.Activesheet.UsedRange
        Local $lastrow = $rng.Rows($rng.rows.Count).Row
        Local $lastcol = $rng.Columns($rng.Columns.count).Column

        For $i = $lastrow To 1 Step -1
            Local $rw = $oExcel.rows($i)
            If $oExcel.Application.intersect($rw, $oExcel.Range("Print_Area")) = nothing Then
                $rw.EntireRow.Delete
            EndIf
        Next
        For $i2 = $lastcol To 1 Step -1
            Local $col = $oExcel.columns($i2)
            If $oExcel.Application.intersect($col, $oExcel.Range("Print_Area")) = nothing Then
                $col.EntireRow.Delete
            EndIf
        Next

EndFunc

So far no clue on how to fix the syntax error, any help is appreciated.

Edited by soonyee91
Posted

Try it with ="" instead of = nothing.

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

  Reveal hidden contents

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Posted

BrewManNH,

After set it to "" instead of nothing. The code can runs however the result is not the same as the vba result. I think there is problem in the code.

Posted

Or you could give the latest beta version of AutoIt a try. They support the Null keyword.

If $oExcel.Application.intersect($rw, $oExcel.Range("Print_Area")) = Null Then

My UDFs and Tutorials:

  Reveal hidden contents

 

  • Moderators
Posted

Do all cells/columns in the sheet have data? If so, you can minimize your code by using the following:

$rowCount = $oExcel.Cells.CurrentRegion.Rows.Count
$colCount = $oExcel.Cells.CurrentRegion.Columns.Count

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Posted

The Intersect method returns a range object. So could you please try:

$oIntersect = $oExcel.Application.intersect($rw, $oExcel.Range("Print_Area"))
If Not IsObj($oIntersect) Then

My UDFs and Tutorials:

  Reveal hidden contents

 

  • Solution
Posted (edited)

Water,

Thanks so much!!!! I know the intersect will return range object but never know how to deal with it. Learn a new thing from you!!!!

Problem solved! :thumbsup:

The success converted code is as follow: (Hope this help others! This function will delete/hide everything outside of printarea of excel.)

Func _ClearOutsidePrintArea()

    ;place this code in a worksheet loop if you want to perform this function for the every excel sheet

        Local $oPrintarea=$oExcel.Activesheet.PageSetup.PrintArea   ; Obtain print area from the active excel sheet

    If $oPrintarea="" Then      
        $oPrintarea=$oExcel.Activesheet.UsedRange.Address   ; Obtain Default excel print area if user never specify the print area
     Endif 

        Local $rng = $oExcel.Activesheet.UsedRange
        Local $lastrow = $rng.Rows($rng.rows.Count).Row
        Local $lastcol = $rng.Columns($rng.Columns.count).Column

        For $i = $lastrow To 1 Step -1
            Local $rw = $oExcel.rows($i)
            $oIntersect=$oExcel.Application.intersect($rw, $oExcel.Range($oPrintarea))
            If Not isobj($oIntersect) Then
                $rw.EntireRow.Delete   ; change the 'delete' to hide if you want to hide instead of delete
            EndIf
        Next
        For $i2 = $lastcol To 1 Step -1
            Local $col = $oExcel.columns($i2)
            $oIntersect1=$oExcel.Application.intersect($col, $oExcel.Range($oPrintarea))
            If not isobj($oIntersect1) Then
                $col.EntireColumn.Delete   ; change the 'delete' to hide if you want to hide instead of delete
            EndIf
        Next

EndFunc
Edited by soonyee91
Posted

:D

My UDFs and Tutorials:

  Reveal hidden contents

 

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...