Tech Guru

Trusted Source Technology

applescript – How do I find Excel sheet with wrong used range?

applescript – How do I find Excel sheet with wrong used range?

How do I transform this VBA code to AppleScript? The code finds employed final row and column from used variety and compares it with the previous row and column applying Uncover. I would like a textual content file to be produced with a checklist of worksheets that have issues.

Sub DefinedPrintArea2() 'with.find
    Dim sht As Worksheet
    Dim lastrow As Lengthy, lastcol As Extensive
    Dim myfile As String
    Dim textfile As Integer
    myfile = SelectFolder & "take a look at file3.txt"         'select the folder exactly where you want tosave the textual content folder
    textfile = FreeFile
    Open myfile For Output As textfile
    For Each and every sht In ActiveWorkbook.Worksheets
        With Sheets(sht.Title)
        If Software.WorksheetFunction.CountA(.Cells) <>  Then
        lastrow = .Cells.Uncover(What:="*", _
                      Soon after:=.Selection("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
        lastcol = .Cells.Find(What:="*", _
                      Just after:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByColumns, _
                      SearchDirection:=xlPrevious, _
            lastrow = 1
            lastcol = 1
        Conclusion If
        Conclude With
        lastrow2 = sht.UsedRange.SpecialCells(xlCellTypeLastCell).Row
        lastcol2 = sht.UsedRange.SpecialCells(xlCellTypeLastCell).Column
        If lastrow > 40 Or lastrow2 > 40 Then
        lastrow = 40
        lastrow2 = 40
        Conclude If
        If lastrow <> lastrow2 Or lastcol <> lastcol2 Then
            Print #textfile, "you will find a problem with the sheet " & sht.Title & " , please check"
            Print #textfile, "final row employing employed array : " & lastrow2 & ", past column working with employed assortment : " & lastcol2
            Print #textfile,
        Stop If
        ActiveSheet.Range(Cells(1, 1), Cells(lastrow, lastcol)).Select
        sht.PageSetup.PrintArea = Choice.Tackle
        Near #textfile
Close Sub

My AppleScript code so much:
inform application “Microsoft Excel”
established tid to AppleScript’s textual content product delimiters
established myRange to “”

set wb to active workbook
established monitor updating to false
established maxV to 
set max_ctr to 
repeat with ctr from 1 to the depend of worksheets in wb
    select worksheet ctr
    explain to worksheet ctr
        set ccount to get count of columns of utilised array
        established rcount to get rely of rows of used range
        established rowxcolumn to rcount * ccount
        if rowxcolumn > maxV then
            set maxV to rowxcolumn
            established max_ctr to ctr
            established m_rcount to rcount
            established m_ccount to ccount
        stop if
    finish tell
    set sname to the identify of worksheet ctr
    established lastrow to count of rows of applied assortment of worksheet ctr
    set lastcol to depend of columns of utilised range of worksheet ctr
    if lastrow is bigger than 40 then
        established lastrow to 40
    end if
    set AppleScript's text item delimiters to ":"
    established columnLetters to textual content item 1 of (get tackle of (column lastcol) of worksheet ctr without having column complete)
    established identify of (get resize vary "A1" of worksheet ctr row size lastrow column dimension lastcol) to "Print_Region"
close repeat
select worksheet max_ctr of wb
display screen dialog "Max sheet is " & name of worksheet max_ctr & ". Max locale is " & m_rcount & ", " & m_ccount
find worksheet max_ctr
activate item cell m_rcount of column m_ccount

set monitor updating to correct
set AppleScript's textual content merchandise delimiters to tid

close convey to

I require to uncover AppleScript equivalent for this and tie it up:
lastrow = .Cells.Uncover(What:=”*”, _
Right after:=.Array(“A1”), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _