Jump to content
VinMe

$xlCellTypeVisible - (Moved)

Recommended Posts

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)

Share this post


Link to post
Share on other sites

Moved to the appropriate forum.

Moderation Team


Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

thank you nine for the reply, i have tried with that but the issue still exist with the below part of code.

#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>
#include <StringConstants.au3>

Local $sWorkbook = FileOpenDialog("Choose file loc Extract file", @ScriptDir, "ALL Excel Files (*.xlsx), *.xls")
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
;~ $oWorkbook = _Excel_BookAttach($sWorkbook)
Local $sMSN = InputBox("MSN NO", "Enter MSN in XX FORMAT", "")
_Excel_FilterSet($oWorkbook, $oWorkbook.activesheet, "AF1", 32, "*" & $sMSN & "*")

Local $LastRow1= $oExcel.ActiveSheet.AutoFilter.Range.Rows.SpecialCells($xlCellTypeVisible).Count-1
MsgBox(0, "lastrow1", $LastRow1)

Local $oLocDS = $oWorkbook.ActiveSheet.Range("S1:S" & $LastRow1).SpecialCells(12) ;not getting complete filtered list
Local $oLocNr = $oWorkbook.ActiveSheet.Range("A1:A" & $LastRow1).SpecialCells(12) ;not getting complete filtered list

Local $aLocDS1 = _Excel_RangeRead($oWorkbook, Default, $oLocDS)
Local $aLocNr1 = _Excel_RangeRead($oWorkbook, Default, $oLocNr)

_ArrayDisplay($aLocDS1)
_ArrayDisplay($aLocNr1)

Attached input excel for your reference.

Result  which i need is set of filtered array set in variable $aLocDS1 and $aLocNr1

thank you in advance!!

BR

vin

vLOC_.XLSX

Edited by VinMe

Share this post


Link to post
Share on other sites

Ah I see.  Try this then :

#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>
#include <StringConstants.au3>

Local $sWorkbook = FileOpenDialog("Choose file loc Extract file", @ScriptDir, "ALL Excel Files (*.xlsx), *.xls")
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
;~ $oWorkbook = _Excel_BookAttach($sWorkbook)
Local $sMSN = InputBox("MSN NO", "Enter MSN in XX FORMAT", "")

_Excel_FilterSet($oWorkbook, $oExcel.ActiveSheet, "AF1", 32, "*" & $sMSN & "*")
Local $oFilter= $oExcel.ActiveSheet.AutoFilter.Range.Rows.SpecialCells($xlCellTypeVisible).copy

Local $oNewSheet = _Excel_SheetAdd ($oWorkbook, Default, False, 1, "Temp")
$oNewSheet.paste
$oNewSheet.Range("A1").Select

Local $LastRow = $oWorkbook.ActiveSheet.Usedrange.Rows.Count
Local $aLocDS = _Excel_RangeRead($oWorkbook, Default, "S1:S" & $LastRow)
Local $aLocNr = _Excel_RangeRead($oWorkbook, Default, "A1:A" & $LastRow)

_Excel_SheetDelete ($oWorkbook)

_ArrayDisplay($aLocDS)
_ArrayDisplay($aLocNr)

 

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

  • 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
      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!
    • By Rhidlor
      Quick question, when working with Excel, does each workbook require its own Excel instance, or can multiple workbooks be opened off of the same Excel instance? I tested both ways, on the surface they both seemed to work, just thought I'd ask here to make sure before proceeding any further. Thanks!
      Disclaimer: The following is pseudo code
      $excel_instance1 = _Excel_Open() $workbook1 = _Excel_BookOpen($excel_instance1, @ScriptDir & "\book1.xlsx") $workbook2 = _Excel_BookOpen($excel_instance1, @ScriptDir & "\book2.xlsx") Or
      $excel_instance1 = _Excel_Open() $workbook1 = _Excel_BookOpen($excel_instance1, @ScriptDir & "\book1.xlsx") $excel_instance2 = _Excel_Open() $workbook2 = _Excel_BookOpen($excel_instance2, @ScriptDir & "\book2.xlsx")  
×
×
  • Create New...