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

Try this, works for me with AutoIt example :

Local $LastRow = $oExcel.ActiveSheet.AutoFilter.Range.Rows.SpecialCells($xlCellTypeVisible).Count-1

Use it after the filter has been applied...

ps. when you post code, use this tool.

Edited by Nine

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

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Cristin
      Dear all,
      first of all hi.
      I`m in trouble, again 😕
      I can`t find right solution, tried all possible combinations generated by my mind and nothing.
      I need to copy range of values depending by values from two different cells in excel sheet.
      I put an example in attachment.

      I have two values which is never changed (is changed only TEXT). I need to copy all the rows from VALUE 1 to VALUE 2 in new Sheets (Output 1, Output 2, ... , Output X).
      It is possible to do this using Autoit? 🤔
      Also in attachment you can find last example which I have tried and it`s something near, it is copying by _Excel_RangeFind row with needed VALUE 1 but I need to copy whole range from VALUE 1 to VALUE 2 😵 ( see in attachment examples).
      Thank you very much, in advance, for your support, solution really will make my life much easier 🙃
      Have a nice day all of you!
      Best Regards,
      Cristin
      Book1.xlsx something.au3
    • By sudeepjd
      I was looking for a UDF using which I could Add and Update Pivot tables and Pivot Charts in Excel easily and could not find one that I could use. So I build this UDF. 
      It has the following functions :
      _ExcelPivot_CreateCache ; Easily Create a pivot table data cache from a Sheet _ExcelPivot_CreateTable ; Create a table from a cache at a specified location on the sheet _ExcelPivot_RefreshTable ; Refresh the datatable data with a new cache _ExcelPivot_AddField ; Add a Field and Aggregate function to the Datatable _ExcelPivot_AddFilter ; Adds in the Filter to a specific field _ExcelPivot_ClearFilter ; Removes the filter for a field or all the filters in the table _ExcelPivot_GetRange ; Get specific areas of a Pivot as a Range Object _ExcelPivot_AddChart ; Add a Pivot Chart linked to a specific Pivot table Attached the UDF to this post.
      Please do let me know if I can improve or add additional functions to it.
      A detailed example on the usage is below. The excel file and the example can be downloaded from the Example.zip file attached.
      #include "ExcelPivot.au3" $oExcel = _Excel_Open() $oBook = _Excel_BookOpen($oExcel, @ScriptDir & "\TestPivot.xlsx") ;Create a Sheet to put the pivot into $pSheet = _Excel_SheetAdd($oBook, -1, False, 1) $pSheet.Name = "Pivot" ;Get the cache for the pivot table $pCache = _ExcelPivot_CreateCache($oBook, "Data") ;Add in the Pivot Table from the Cache _ExcelPivot_CreateTable($pCache, $pSheet, "A1", "FruitsPivot") ;Add in the Fields into the Pivot _ExcelPivot_AddField($pSheet, "FruitsPivot", "Category", "Filter") _ExcelPivot_AddField($pSheet, "FruitsPivot", "Product", "Row") _ExcelPivot_AddField($pSheet, "FruitsPivot", "Amount", "Value", "Sum", 1) ;Add in a Running total to the Pivot _ExcelPivot_AddField($pSheet, "FruitsPivot", "Amount", "Value", "Sum", 2, "PercentageRunningTotal", "Product") ;Filter only the fruits _ExcelPivot_Filter($pSheet, "FruitsPivot", "Category", "Fruit") ;Draw a Paretto Chart $chart = _ExcelPivot_AddChart($oBook, $pSheet, "FruitsPivot", "ColumnClustered", "Paretto", "E2", 570) $chart.Chart.FullSeriesCollection(1).ApplyDataLabels $chart.Chart.FullSeriesCollection(2).ChartType = 4 ;Change the percentage to a line graph $chart.Chart.FullSeriesCollection(2).AxisGroup = 2 ;Move it to secondary axis $chart.Chart.Axes(2, 2).MaximumScale = 1 ;Adjust to scale to 100% max  
       
      ExcelPivot.au3 Example.zip
    • By Page2PagePro
      Excel VBA's IDE registers a Control-y as "cut this line of code".
      For those prone to Undo/Redo (Ctrl+Z/Ctrl+Y) you may find frustration when your code in the editor does not redo, but in fact clears your active line of code while killing redo history.
      Though not perfect, I keep this tool running in background on startup.
      The purpose is to allow Cltr+Y to act normally throughout Windows and Office and only interact *differently* with the "Microsoft Visual Basic for Applications" window that is active.
      If the Standard Menu bar exists, it'll try to click the ReDo (Blue Arrow to the right), else "Alt+e, r" keystrokes (less desired).
       
      Here's the code:
      Opt('MustDeclareVars', 1) Opt("WinTitleMatchMode", 1) HotKeySet("^y", "TriggerRedo") While 1 Sleep(10) WEnd Func TriggerRedo() ConsoleWrite("TriggerRedo()" & @CRLF) Local $title = "Microsoft Visual Basic for Applications - " Local $hWnd If WinExists($title) And WinActive($title) Then ;~ Parent Window Handle $hWnd = WinGetHandle($title) Local $aWindowPos = WinGetPos($hWnd) ;~ Control Bar Handle, Position and If Visible Local $sControlID = "[CLASS:MsoCommandBar; TEXT:Standard;]" Local $hStandardBar = ControlGetHandle($hWnd, "", $sControlID) Local $bIsVisible = ControlCommand($hWnd, "", $sControlID, "IsVisible") If $hStandardBar And $bIsVisible Then ConsoleWrite("Using Mouse Click." & @CRLF) ;~ Determine Redo button location on visible Control Bar Local $aBarPos = ControlGetPos($hWnd, "", $sControlID) Local $mX = $aWindowPos[0] + $aBarPos[0] + 217 + Int(23/2) Local $mY = $aWindowPos[1] + $aBarPos[1] + 27 + Int(22/2) MouseClick("Left", $mX, $mY, 1, 0) Else ConsoleWrite("Using VBA Send Keys." & @CRLF) $sControlID = "[CLASS:MsoCommandBar; TEXT:Menu Bar;]" Local $hMenuBar = ControlGetHandle($hWnd, "", $sControlID) ControlSend($hWnd, "", $hMenuBar, "!e") ;~ Send("r") $sControlID = "[CLASS:MsoCommandBarPopup; TEXT:Edit;]" Local $hPopupBar = ControlGetHandle($hWnd, "", $sControlID) ControlSend($hWnd, "", $hPopupBar, "r") EndIf Else ConsoleWrite("Using NATIVE Send Keys." & @CRLF) HotKeySet("^y") Send("^y") ;~ may cause "yyy..." when held HotKeySet("^y", "TriggerRedo") EndIf EndFunc ;==>TriggerRedo Hope this inspires someone.
       
       
    • By siawpo
      Hi,
      I'd like to change different colors for different portion of text in same cell of Excel application.
      Neither character length nor cell might not fixed.
      Here's the code I've tried to put together but not manage to pull it off.
      I'm appreciate it for any suggestion, thank you.
      $oExcel = ObjCreate("Excel.Application") With $oExcel ; open new workbook .Visible = True .WorkBooks.Add .ActiveWorkbook.Sheets(1).Select() EndWith $oExcel.Cells.Font.Color = 0x000000 $oExcel.ActiveFont.Color = -16776961 Send ("'I'd like this sentence to be red'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) $oExcel.ActiveCell.Selection.Font.Color = 0x000000 Send ("'I like this sentence to be black'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) $oExcel.ActiveFont.Color = -16776961 Send ("'I'd like this sentence to be red again'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) Send("{ENTER}")  
    • By Langmeister
      Hi, I'm sure that this is a simple question regarding webdriver udf but my search on the forum did not result in the exact same scenario that I need. I will adapt it to my corporate needs but at first I want it to work with an example everybody can reproduce if needed.
      I navigate to https://www.daysoftheyear.com/ and want to find out what special day today is. Using the following Code returns the wanted text that today is 'shark awareness day' in the console but it is not pasted in notepad as it is not copied as it probably should be.
      #include "wd_core.au3" #include "wd_helper.au3" Local $sDesiredCapabilities, $sSession, $sID _WD_Option("Driver", "C:\Program Files (x86)\AutoIt3\chromedriver.exe") _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true }}}}' _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_Navigate($sSession, "https://www.daysoftheyear.com/?timezone_offset=nan") _WD_LoadWait($sSession) Sleep(3000) Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "/html/body/div[2]/header/div/div[1]/div/div[2]/h3/a") _WD_ElementAction($sSession, $sElement, 'text') ClipGet() Run("notepad.exe") Sleep(500) Send("^v") Scite returns this, therefore the text has been found but the last mile to paste it to notepad or excel does not work for me.
      __WD_Get: URL=HTTP://127.0.0.1:9515/session/b72166b774d7fefb258b3721fc1d4306/element/657e6b5d-31a9-46a8-85c4-acb7f72bf659/text __WD_Get: StatusCode=200; $iResult = 0; $sResponseText={"value":"Shark Awareness Day"}... _WD_ElementAction: {"value":"Shark Awareness Day"}... Thanks in advance! 
       
×
×
  • Create New...