[Solved] SpecialCells($xlCellTypeVisible) not working

[anthonyjr2]

I'm back for yet another Excel issue. Here's my code:

$oExcel = _Excel_Open(True, False, True, False, True)
        $newExcel = _Excel_BookNew($oExcel, 2)
        
        ;$pExcel is a BookOpenText from earlier that has been populated with data
        _Excel_SheetCopyMove($pExcel, Default, $newExcel, 2, False)
        _Excel_BookClose($pExcel, False)

        _Excel_FilterSet($newExcel, 3, Default, 36, "=U", $xlFilterValues)

        $oRange = $newExcel.ActiveSheet.UsedRange.SpecialCells($xlCellTypeVisible)
        Local $aResult[1][$oRange.columns.Count], $aContent
        ; Read the data of all Ranges in the Area and concatenate the returned arrays.
        For $oArea In $oRange.Areas
            $aContent = _Excel_RangeRead($newExcel, Default, $oArea, Default, True)
            _ArrayConcatenate($aResult, $aContent)
        Next
        _Excel_RangeWrite($newExcel, 1, $aResult)
        
        _Excel_BookSaveAs($newExcel, @ScriptDir & "\test.xlsx", Default, True)
        _Excel_BookClose($newExcel)
        _Excel_Close($oExcel)

No matter what range I try to use I keep getting:

"C:\script.au3" (117) : ==> The requested action with this object has failed.:
$oRange = $newExcel.ActiveSheet.UsedRange.SpecialCells($xlCellTypeVisible)
$oRange = $newExcel.ActiveSheet.UsedRange^ ERROR

It seems to be an issue with $xlCellTypeVisible since other SpecialCells values work fine. Also I'm using Excel 2007, which is old but still supposed to have this functionality.

 

Any suggestions? Or just a way to copy out this data from the filter if this snippet from the wiki won't work.

 

EDIT: Adding in COM error I'm getting, not sure why it's happening still though

script.au3 (113) : ==> COM Error intercepted !
    err.number is:      0x80020009
    err.windescription: Exception occurred.

    err.description is:     Microsoft Office Excel cannot create or use the data range reference because it is too complex. Try one or more of the following:

• Use data that can be selected in one contiguous rectangle.
• Use data from the same sheet.
    err.source is:      Microsoft Office Excel
    err.helpfile is:    C:\Program Files (x86)\Microsoft Office\Office12\1033\XLMAIN11.CHM
    err.helpcontext is:     0
    err.lastdllerror is:    0
    err.scriptline is:  113
    err.retcode is:     0x800A03EC

 

Edited September 6, 2018 by anthonyjr2

[junkew]

Check if $xlCellTypeVisible = 12

use worksheets(1) instead of activesheet

write it like

$ws=.....

try it first in vba

[anthonyjr2]

Not sure if you saw the COM error I posted, but it seems to be a range issue, rather than me being on the wrong tab or something. I made those changes but it still doesn't work.

EDIT: Alright, I sorted it first and that seems to allow me to filter it. Man this stuff really bugs me sometimes lol.

Edited September 6, 2018 by anthonyjr2