Jump to content

[Solved] SpecialCells($xlCellTypeVisible) not working

Recommended Posts

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)
        _Excel_RangeWrite($newExcel, 1, $aResult)
        _Excel_BookSaveAs($newExcel, @ScriptDir & "\test.xlsx", Default, True)

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 by anthonyjr2


Link to post
Share on other sites

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 by anthonyjr2


Link to post
Share on other sites

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