Excel, Hidden WB, SpecialCells(xlCellTypeVisible)

[Anas]

Hello,

Is it possible to use SpecialCells(xlCellTypeVisible) method on a hidden workbook?

I want to apply AutoFilter on a hidden workbook, then copy the filtered data to a new workbook, but unless the workbook window is set to visible, SpecialCells fails to see anything.

the only workaround is to show the workbook window, copy the cells, then hide it again, but this causes a flicker on any workbook opened after the hidden one (even with screenupdating set to false)

any suggestions/workarounds?

expandcollapse popup

$oApp = ObjCreate("Excel.Application")
With $oApp
    .Visible = 0
    .DisplayAlerts = 0
    .SheetsInNewWorkbook = 1
    $oExcel = .WorkBooks.Add()
    .Windows($oExcel.Name).Visible = 0
EndWith

For $x = 1 To 5
    For $y = 1 To 5
        $oExcel.Activesheet.Cells($x, $y).Value = 'Row' & $x
    Next
Next

$oExcel.Activesheet.UsedRange.AutoFilter(1, 'Row3')

$Flicker_Workaround = 1 ;<======== Enabling this will fix the problem
If $Flicker_Workaround Then
    $oExcel2 = $oApp.WorkBooks.Add()
    $oApp.Visible = 1
    $oApp.Windows($oExcel2.Name).Visible = 1
    Sleep(1000) ;allows the new workbook window to be drawn

    $oApp.ScreenUpdating = 1
    $oApp.Windows($oExcel.Name).Visible = 1

    $Cells = $oExcel.Activesheet.Autofilter.Range.SpecialCells(12)
    ConsoleWrite(@CRLF & $Cells.Count & @CRLF & @CRLF)

    $oApp.Windows($oExcel.Name).Visible = 0
    Sleep(1000) ;to notice the flicker
    $oExcel2.Close(0)
Else
    $Cells = $oExcel.Activesheet.Autofilter.Range.SpecialCells(12)
    ConsoleWrite(@CRLF & IsObj($Cells) & @CRLF & @CRLF)
EndIf
$oExcel.Close(0)