Jump to content
nooneclose

How to convert a outline.showlevels VBA code to Autoit

Recommended Posts

nooneclose

How do I properly convert this to Autoit? This is a VBA macro that I recorded in Excel.

 ActiveSheet.Outline.ShowLevels RowLevels:=2

I need this to close my subtotal once it is finished. 

any help will be greatly appreciated. 

Share this post


Link to post
Share on other sites
JLogan3o13

Try something like this (a little difficult when you don't post the rest of your code):

$oWorkbook.ActiveSheet.ShowLevels.RowLevels=2

 


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
nooneclose

@JLogan3o13 The script compiled but nothing happened. 

$OpenWorkbook.ActiveSheet.ShowLevels.RowLevels=2

How much of my code do you want? it is 600 lines almost. 

Share this post


Link to post
Share on other sites
nooneclose

@JLogan3o13 Here is a section of my code where I do the subtotal and where I need it to collapse. I want to collapse the row level 2 after the subtotal with actual code instead of mouse clicks.

 

Sleep(1000)
MsgBox($MB_ICONINFORMATION, "WO_to_FTE_Bot", "Subtotal started")
Sleep(1000)

Const $xlSum = -4157
Global $sub_Array[1] = [7]
$oRange = $OpenWorkbook.Activesheet.Range("A1:N" & $IndexRows)
$oRange.Subtotal(2, $xlSum, $sub_Array, True, False, True)

;Sleep(3000)
;MouseClick("primary", 1107, 673, 1, 10)           ;clicks on/in th excel file to ativate it just in case
;Sleep(1000)
;MouseClick("primary", 38, 213, 2, 10)             ;Collaspes the subtotal

$OpenWorkbook.ActiveSheet.ShowLevels.RowLevels=2   ;here is where i want to actually close the row level 2 by not using mouse clicks.

Sleep(1500)
MsgBox($MB_ICONINFORMATION, "WO_to_FTE_Bot", "Subtotal Finished!")

 

Share this post


Link to post
Share on other sites
water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
nooneclose

@water Thank you very much it works perfectly! My program is 99% done. Any last hints on a way to force excel to maximize once it is opened that is excel 2010 friendly? I already have code that does it and for my excel 2016 it works fine but my co-worker who has excel 2010 it does not. 

Share this post


Link to post
Share on other sites
water

$oExcel.Window.WindowState = -4137


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Juvigy

Besides the native excel way , you can also try the native windows way using WinSetState

Share this post


Link to post
Share on other sites
nooneclose

Thank you @water and @Juvigy My code is finally finished and now is unbreakable. the community has helped me so much in such a short time I wish I could thank you all.  Cheers, and may our programs always work!

Share this post


Link to post
Share on other sites
water

"unbreakable" :huh:
2 minutes after the first DAU (dumbest assumable user) has laid his hand on your script it will be broken. Or even faster ;)
That's what happens to me all the time :)

  • Like 2

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
nooneclose

I know that no code is "unbreakable" but it feels great to have finished a project like this one. thanks again! :) 

Share this post


Link to post
Share on other sites
water

Oh, I too love this moment when something works as I did design it :dance:
Unfortunately most of the time I have to lower my expectations and love what I get ;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

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

    • greichert
      By greichert
      I am trying to stop a windows service on a separate server than where my script is running. When I run the following script on the same server as the service I need to stop, it works fine. 
      RunWait(@ComSpec & " /c net stop FascetAgentfv2", @ScriptDir, @SW_HIDE);stop agent But when I change "@ScriptDir" to ""\\newserver\c$\Program Files\test\" and run it on my server nothing happens. I tried to error handle and all it was returning was "2". Any suggestions?
    • ajblandford
      By ajblandford
      I have embeded an Excel file in my autoit Script and want to use it as part of my GUI. I want to be able to select a range of cells and add the cell data to an edit box. Then dump that data to an array. I am using GUICtrlCreateObj to embed the spreadsheet. I cannot find any way to let my program see what cells are active so I can add that data to the edit box. 
       
      #include <WindowsConstants.au3> #include <GUIConstantsEx.au3> #include <excel.au3> $FileName = 'C:\VLog\book1.xlsx' If Not FileExists($FileName) Then MsgBox(0, "ERROR", "File not found") Exit EndIf ;Basic GUI $oExcelDoc = ObjGet($FileName) ; Get an excel Object from an existing filename If IsObj($oExcelDoc) Then $mainGUI = GUICreate("Production Room 2", 1800, 1200, 10, 10, $WS_MINIMIZEBOX + $WS_SYSMENU + $WS_CLIPCHILDREN) $GUI_ActiveX = GUICtrlCreateObj($oExcelDoc, 10, 70, @DesktopWidth - 250, @DesktopHeight - 260) $data = GUICtrlCreateEdit("", @DesktopWidth - 225, 75, 100) $btn = GUICtrlCreateButton( "GO", @DesktopWidth - 100, 75, 75) Else MsgBox(0, "", "failed") EndIf ;------------------ ;Turns off all command bars in excel to prevent user from making changes For $Bar In $oExcelDoc.CommandBars If $Bar.Enabled = True Then $Bar.Enabled = False If $Bar.Visible = True Then $Bar.Visible = False Next $oExcelDoc.Application.DisplayFormulaBar = False $oExcelDoc.Application.CommandBars("Shadow Settings").Visible = False $oExcelDoc.Application.DisplayScrollBars = True $oExcelDoc.Application.DisplayStatusBar = False GUISetState()  
    • Skeletor
      By Skeletor
      Hi Guys,
      Can anyone point me in the right direction. I'm trying to accomplished conditional formatting with arrows, but the code I have is wrong... 
      .Range("=$A3:$A4000").FormatConditions.Add = (xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets I also tried it like this:
      .Range("=$A3:$A4000").FormatConditions _ .Add(xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets  
    • Skeletor
      By Skeletor
      Hi Guys,
      How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
      _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
      Inventory.xlsx
    • Skeletor
      By Skeletor
      Hi All,
      Has anyone come across this before?
      Code below:
      $SheetList = _Excel_SheetList($oWorkbook) _FileWriteFromArray("C:\" & $SheetListOutput, $SheetList, 1) Result:
      ResultABC| ResultDEF| ResultGHI| ResultJKL| ResultMNO| It created these "|" vertical bars?
×