Jump to content
Sign in to follow this  
Anas

Excel, Hidden WB, SpecialCells(xlCellTypeVisible)

Recommended Posts

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?

$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)

Share this post


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
Sign in to follow this  

  • Similar Content

    • By VinMe
      Dear all, i am unable to open a xml file to excel in the "xml table format" Please help me out in where i am missing
      Local $strFileToOpen = _WinAPI_OpenFileDlg('Select xml file', @WorkingDir, 'All Files(*.*)', 1, '', '', BitOR($OFN_PATHMUSTEXIST, $OFN_FILEMUSTEXIST, $OFN_HIDEREADONLY)) Global $xlXmlLoadImportToList = 2 ; Places the contents of the XML data file in an XML table $oExcel = _Excel_Open() $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList) If $strFileToOpen <> False Then     Local $oWorkbook1 = _Excel_BookOpen($oExcel, $strFileToOpen) EndIf Error i am getting is:
      ......\81e_Compare_v1.au3" (46) : ==> The requested action with this object has failed.:
      $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList)
      $oWorkbook1=$oExcel.Workbooks^ ERROR
      >Exit code: 1    Time: 7.338
    • By VinMe
      Dear all, 
      I am unable to get the right result after applying the filter to the excel. please let me know on the same.
      issue: After applying the filter the output $lastRow11 not giving the right output of complete visible rows. (its breaking at row skips)
       
      ;DATA EXTRACTION FROM LOC EXCEL
      ;=============================================================================
      $oWorkbook = _Excel_BookAttach($sWorkbook)
      Local $sMSN = InputBox("MSN NO", "Enter MSN in XX FORMAT", "")
      ;~ Local $LastRow1 = ($oWorkbook.ACTIVESHEET.Range("A1").SpecialCells($xlCellTypeLastCell).Row)
      $LastRow1 = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
      MsgBox(0, "lastrow1", $LastRow1)
      _Excel_FilterSet($oWorkbook, $oWorkbook.activesheet, "AF1", 32, "*" & $sMSN & "*")
      Local $oLocDS = $oWorkbook.ActiveSheet.Range("S1:S" & $LastRow1).SpecialCells($xlCellTypeVisible)
      Local $LastRow11 = $oLocDS.rows.count    ;error output
      MsgBox(0, "lastrow11", $LastRow11)
      Local $aLocDS1 = _Excel_RangeRead($oWorkbook, Default, $oLocDS)
      Local $oLocNr = $oWorkbook.ActiveSheet.Range("A1:A" & $LastRow1).SpecialCells($xlCellTypeVisible)
      Local $aLocNr1 = _Excel_RangeRead($oWorkbook, Default, $oLocNr)
      _ArrayDisplay($aLocDS1)
      _ArrayDisplay($aLocNr1)
      _ArrayTrim($aLocDS1, 6, 1)
      _ArrayTrim($aLocNr1, 6, 1)
      _ArrayTrim($aLocNr1, 6, 0)
      _ArrayDisplay($aLocDS1)
      _ArrayDisplay($aLocNr1)
    • By VinMe
      I am unable to execute the below script, my requirement is to copy the content from active excel sheet and to display the same.
      Please let me know where i am missing!
      #include <Excel.au3>
      #include <MsgBoxConstants.au3>
      #include <Array.au3>
      #include <StringConstants.au3>
      Local $oExcel = _Excel_Open()
      $LastRow2 = $oExcel.UsedRange.Rows.Count
      $Tissue = _Excel_RangeRead($oExcel, Default, "E1:E" & $LastRow2)
      $TshNr = _Excel_RangeRead($oExcel, Default, "F1:F" & $LastRow2)
      _ArrayDisplay($Tissue)
      _ArrayDisplay($TshNr)
    • By _leo_
      Hey there! 😃
      I am having a problem with the _Excel_RangeFind. I am trying to search for a value in a particular cell range. The script copies the value from the internet. Copying and saving as a variable is working fine, but as soon as it should find the value in excel, nothing happens. ( I am not getting an error)
      #include <Excel.au3> Func Excel() Send("{CTRLDOWN}") Send("{c}") Send("{CTRLUP}") Local $sName = ClipGet() ;Text Local $sShortName = StringTrimRight ( $sName, 1) ;delete one letter Local $bOpenWorkBook = False, $oExcel = _Excel_Open() Local $sFilePath = "C:\Users\Acer\OneDrive\xyz.xlsx" Local $oWorkbook $oWorkbook = _Excel_BookAttach($sFilePath) If @error Then $oWorkbook = _Excel_BookOpen($oExcel, $sFilePath) $bOpenWorkBook = True EndIf sleep(15000) Send("{LWINDown}") Send ("{up}") ;maximize window Send("{LWINup}") sleep(1000) _Excel_RangeFind ($oWorkbook, $sShortName, "A3:A56") EndFunc Is anyone familiar with this problem or am I just missing some basic stuff? 
      Thanks for help!
    • By _leo_
      Hey guys
      I'm new to autoit, so this could be a simple question. I'm trying to read the value from the currently selected cell in Excel. I read on the forum and tired to find videos, but I couldn't quite get to it. 
      This is what I have got so far:
       
      Local $oExcel_1 = _Excel_Open()
      Local $var = "C:\Users\Acer\xy"
      Local $oWorkbook = _Excel_BookOpen($oExcel_1,$var)
       
      Local $_read1 = _Excel_RangeRead($oWorkbook, Default.Application.ActiveCell.Address)
       
      Whatever I try, I either get an error or it only reads "0".
       
      Thank you very much for any helpful thoughts!
×
×
  • Create New...