Jump to content

Help with my Script

gassis

By gassis,
in AutoIt General Help and Support

 Share

Recommended Posts

gassis Seeker

gassis

Posted
Posted 

#AutoIt3Wrapper_AU3Check_Stop_OnWarning=Y
#AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6

#include <Excel Rewrite.au3>
#include <Constants.au3>

; Create application object , they must all have different numbers. Each workbook and function should have one.
Global $oAppl = _Excel_Open()
Global $oApp2 = _Excel_Open()
Global $oApp3 = _Excel_Open()
Global $oApp4 = _Excel_Open()
Global $oApp5 = _Excel_Open()
Global $oApp6 = _Excel_Open()
Global $oApp7 = _Excel_Open()
Global $oApp8 = _Excel_Open()
Global $oApp9 = _Excel_Open()
Global $oApp10 = _Excel_Open()
Global $oApp11 = _Excel_Open()
Global $oApp12 = _Excel_Open()
Global $0App13 = _Excel_Open()


Validator($oApp2)
DashGen($oAppl)
SSSG($oApp4)
GCA($oApp5)
SystemSales($oApp6)
RegionGCA($oApp7)
RegionSSSG($oApp8)
RegionSystem($oApp9)
UnitSSSG($oApp10)
UnitSystem($oApp11)
Market($oApp12)
StoreCount($oApp3)




;put your function code below the validator, it must go first to push all other changes.


Func Validator($oApp2)

   ; Open the Main File / validaot that will push the updates to all other files
    Local $sWorkbook = "S:\Finance\Automated-Reports\Source Files\KFC Report for TB\Validator.xlsm"
    Local $oWorkbook = _Excel_BookOpen($oApp2, $sWorkbook)
    ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)

   ;Force Excel Window to be active


WinActivate("Microsoft Excel - Validator.xlsm")

WinWaitActive("Microsoft Excel - Validator.xlsm")

   Sleep(4000); Runs the macro to update the Pivot  , looks to see if the new period is in the cube. if not nothing will happen .
   ControlSend($sWorkbook,"","","{ALT}LPM")
   ControlSend($sWorkbook,"","","UpdateCurrent")
   ControlSend($sWorkbook,"","","!R")
   sleep(10000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
   Sleep(4000); Runs the macro to update the Pivot  , looks to see if the new period is in the cube. if not nothing will happen .
   ControlSend("!LPM")
   ControlSend("RemoveBrand")
   ControlSend("!R")
   sleep(10000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
    Local $sResult = _Excel_RangeRead($oWorkbook, Default, "A1")
    ;Excel_RangeWrite($oWorkbook, Default, $sResult, "A3")
    IF $sResult = "Don't Update" Then 
       Global $Var_1 =_Excel_RangeRead($oWorkbook, Default, "A2")
       
       WinActive("Microsoft Excel - Validator.")
; Runs the macro to update the Pivot  so that validator is now both sheets on same period 
   ControlSend("!LPM")
   ControlSend("UpdateAll")
   ControlSend("!R")
   sleep(10000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
    ;the Main  , this will save the file to the new period so that both of them now show the same period
   WinActive("Microsoft Excel - Validator.xlsm")
   ControlSend("^s")
   Sleep(5000)
       
       
     Else
       _Excel_BookClose($oWorkbook, True )
         Sleep(3000)
         ControlSend("!fx")
         Exit
     EndIf
    
    ; MAJOR KEY , you must make your variable equal to to the global varaible . it is the one that will tell your models which period to use.

EndFunc
Func DashGen($oAppl)

   ; Open the Main File/ generator for dashboard
    Local $sWorkbook = "S:\Finance\Automated-Reports\Source Files\KFC Report for TB\PH-KFC_Dashboard_Generator.xlsm"
    Local $oWorkbook1 = _Excel_BookOpen($oAppl, $sWorkbook)
    ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)

   ; Force Excel Window to be active
   ;WinActivate ( "Microsoft Excel - PH-KFC_Dashboard_Generator.xlsm" )

   WinWaitActive("Microsoft Excel - PH-KFC_Dashboard_Generator.xlsm")
   

   
  sleep(5000)
   ; Copy current Period # and increases it in 1 , this will make the value of the period to match the validator. If you use another function you need to make the $SResult a different number
    ;Local $sResult2 = _Excel_RangeRead($oWorkbook, Default, "B1")
    Local $sResult2 = $Var_1
    _Excel_RangeWrite($oWorkbook1, Default, $sResult2, "B1")
    

   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("UpdatePivot")
   ControlSend("!R")
   sleep(3000000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
   ; Runs the macro to update the Final Sheet
   ControlSend("!LPM")
   ControlSend("FinalStep")
   ControlSend("!R")
   sleep(10000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   


   ; Saves the Main File
   WinWaitActive("Microsoft Excel - PH-KFC_Dashboard_Generator.xlsm")
   ControlSend("^s")
   Sleep(5000)

   ;Generates the file to ControlSend to TB
   ControlSend("!FA")
   WinWaitActive("Save As")
   ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult2 & "\P" & $sResult2 & " - Dashboard.xlsm"  )
   ControlSend("!S")
   Sleep(5000)
   ControlSend("{TAB}")
   ControlSend("{ENTER}")
  

   ;Close excel???
    _Excel_BookClose($oWorkbook1, True )
      Sleep(3000)
    ;ControlSend("!fx")

EndFunc 

Func SSSG($oApp4)
   
    Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Per Brand_Same Store Sales & Transactions.xlsm"
    Local $oWorkbook4 = _Excel_BookOpen($oApp4, $sWorkbook)
    ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)

   ; Force Excel Window to be active
   WinActivate ( "Microsoft Excel - Per Brand_Same Store Sales & Transactions.xlsm" )
   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Unhide")
   ControlSend("!R")
   sleep(20000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
    Local $sResult4 = _Excel_RangeRead($oWorkbook4, Default, "F1")
    $sResult4 =  $Var_1
    _Excel_RangeWrite($oWorkbook4, Default, $sResult4, "F1")
    
    ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("UpdatePivot")
   ControlSend("!R")
   sleep(20000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Hide")
   ControlSend("!R")
   sleep(10000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
    ; Saves the Main File
   WinWaitActive("Microsoft Excel - Per Brand_Same Store Sales & Transactions.xlsm")
   ControlSend("^s")
   Sleep(5000)
   
   ;Generates the file to ControlSend to TB
   ControlSend("!FA")
   WinWaitActive("Save As")
   ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult4 & "\P" & $sResult4 & " - Per Brand_Same Store Sales & Transactions.xlsm"  )
   ControlSend("!S")
   Sleep(5000)
   ControlSend("{TAB}")
   ControlSend("{ENTER}")
   
    _Excel_BookClose($oWorkbook4, True )
      Sleep(3000)
   
EndFunc

Func GCA($oApp5)
   
    Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Per Brand_Same Store GCA & WPSA.xlsm"
    Local $oWorkbook5 = _Excel_BookOpen($oApp5, $sWorkbook)
    ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)

   ; Force Excel Window to be active
   WinActivate ( "Microsoft Excel - Per Brand_Same Store GCA & WPSA.xlsm" )
   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Unhide")
   ControlSend("!R")
   sleep(20000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
   Local $sResult5 = _Excel_RangeRead($oWorkbook5, Default, "F1")
    $sResult5 =  $Var_1
    _Excel_RangeWrite($oWorkbook5, Default, $sResult5, "F1")
    
    ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("UpdatePivot")
   ControlSend("!R")
   sleep(20000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Hide")
   ControlSend("!R")
   sleep(10000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
   ; Saves the Main File
   WinWaitActive("Microsoft Excel - Per Brand_Same Store GCA & WPSA.xlsm")
   ControlSend("^s")
   Sleep(5000)
   
   ;Generates the file to ControlSend to TB
   ControlSend("!FA")
   WinWaitActive("Save As")
   ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult5 & "\P" & $sResult5 & " - Per Brand_Same Store GCA & WPSA.xlsm"  )
   ControlSend("!S")
   Sleep(5000)
   ControlSend("{TAB}")
   ControlSend("{ENTER}")
   
   _Excel_BookClose($oWorkbook5, True )
      Sleep(3000)
   
EndFunc
   
Func SystemSales($oApp6)
   
    Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Per Brand_System Sales & Transactions.xlsm"
    Local $oWorkbook6 = _Excel_BookOpen($oApp6, $sWorkbook)
    ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)

   ; Force Excel Window to be active
   WinActivate ( "Microsoft Excel - Per Brand_System Sales & Transactions.xlsm" )
   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Unhide")
   ControlSend("!R")
   sleep(20000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
   Local $sResult6 = _Excel_RangeRead($oWorkbook6, Default, "F1")
    $sResult6 =  $Var_1
    _Excel_RangeWrite($oWorkbook6, Default, $sResult6, "F1")
    
    ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("UpdatePivot")
   ControlSend("!R")
   sleep(20000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Hide")
   ControlSend("!R")
   sleep(10000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
   ; Saves the Main File
   WinWaitActive("Microsoft Excel - Per Brand_System Sales & Transactions.xlsm")
   ControlSend("^s")
   Sleep(5000)
   
   ;Generates the file to ControlSend to TB
   ControlSend("!FA")
   WinWaitActive("Save As")
   ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult6 & "\P" & $sResult6 & " - Per Brand_System Sales & Transactions.xlsm"  )
   ControlSend("!S")
   Sleep(5000)
   ControlSend("{TAB}")
   ControlSend("{ENTER}")
   
    _Excel_BookClose($oWorkbook6, True )
      Sleep(3000)
   
EndFunc

Func RegionGCA($oApp7)
    
    Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Per Region_Same Store GCA & WPSA.xlsm"
    Local $oWorkbook7 = _Excel_BookOpen($oApp7, $sWorkbook)
    ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)

   ; Force Excel Window to be active
   WinActivate ( "Microsoft Excel - Per Region_Same Store GCA & WPSA.xlsm" )
   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Unhide")
   ControlSend("!R")
   sleep(20000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
   Local $sResult7 = _Excel_RangeRead($oWorkbook7, Default, "F1")
    $sResult7 =  $Var_1
    _Excel_RangeWrite($oWorkbook7, Default, $sResult7, "F1")
    
    ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("UpdatePivot")
   ControlSend("!R")
   sleep(20000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Hide")
   ControlSend("!R")
   sleep(10000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
   ; Saves the Main File
   WinWaitActive("Microsoft Excel - Per Region_Same Store GCA & WPSA.xlsm")
   ControlSend("^s")
   Sleep(5000)
   
   ;Generates the file to ControlSend to TB
   ControlSend("!FA")
   WinWaitActive("Save As")
   ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult7 & "\P" & $sResult7 & " - Per Region_Same Store GCA & WPSA.xlsm"  )
   ControlSend("!S")
   Sleep(5000)
   ControlSend("{TAB}")
   ControlSend("{ENTER}")
   
   _Excel_BookClose($oWorkbook7, True )
      Sleep(3000)


EndFunc

Func RegionSSSG($oApp8)
   
    Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Per Region_Same Store Sales  Transactions.xlsm"
    Local $oWorkbook8 = _Excel_BookOpen($oApp8, $sWorkbook)
    ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)

   ; Force Excel Window to be active
   WinActivate ( "Microsoft Excel - Per Region_Same Store Sales  Transactions.xlsm" )
   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Unhide")
   ControlSend("!R")
   sleep(20000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
    Local $sResult8 = _Excel_RangeRead($oWorkbook8, Default, "F1")
    $sResult8 =  $Var_1
    _Excel_RangeWrite($oWorkbook8, Default, $sResult8, "F1")
    
    ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("UpdatePivot")
   ControlSend("!R")
   sleep(20000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Hide")
   ControlSend("!R")
   sleep(10000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
    ; Saves the Main File
   WinWaitActive("Microsoft Excel - Per Region_Same Store Sales  Transactions.xlsm")
   ControlSend("^s")
   Sleep(5000)
   
   ;Generates the file to ControlSend to TB
   ControlSend("!FA")
   WinWaitActive("Save As")
   ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult8 & "\P" & $sResult8 & " - Per Region_Same Store Sales  Transactions.xlsm"  )
   ControlSend("!S")
   Sleep(5000)
   ControlSend("{TAB}")
   ControlSend("{ENTER}")
   
    _Excel_BookClose($oWorkbook8, True )
      Sleep(3000)

EndFunc

Func RegionSystem($oApp9)
    
    Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Per Region_System Sales & Transactions.xlsm"
    Local $oWorkbook9 = _Excel_BookOpen($oApp9, $sWorkbook)
    ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)

   ; Force Excel Window to be active
   WinActivate ( "Microsoft Excel - Per Region_System Sales & Transactions.xlsm" )
   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Unhide")
   ControlSend("!R")
   sleep(20000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
   Local $sResult9 = _Excel_RangeRead($oWorkbook9, Default, "F1")
    $sResult9 =  $Var_1
    _Excel_RangeWrite($oWorkbook9, Default, $sResult9, "F1")
    
    ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("UpdatePivot")
   ControlSend("!R")
   sleep(20000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Hide")
   ControlSend("!R")
   sleep(10000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
   ; Saves the Main File
   WinWaitActive("Microsoft Excel - Per Region_System Sales & Transactions.xlsm")
   ControlSend("^s")
   Sleep(5000)
   
   ;Generates the file to ControlSend to TB
   ControlSend("!FA")
   WinWaitActive("Save As")
   ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult9 & "\P" & $sResult9 & " - Per Region_System Sales & Transactions.xlsm"  )
   ControlSend("!S")
   Sleep(5000)
   ControlSend("{TAB}")
   ControlSend("{ENTER}")
   
   _Excel_BookClose($oWorkbook9, True )
      Sleep(3000)

EndFunc

Func UnitSSSG($oApp10)
   
    Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Per Unit_SSS and Transactions.xlsm"
    Local $oWorkbook10 = _Excel_BookOpen($oApp10, $sWorkbook)
    ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)

   ; Force Excel Window to be active
   WinActivate ( "Microsoft Excel - Per Unit_SSS and Transactions.xlsm" )
   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Unhide")
   ControlSend("!R")
   sleep(20000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
    Local $sResult10 = _Excel_RangeRead($oWorkbook10, Default, "F1")
    $sResult10 =  $Var_1
    _Excel_RangeWrite($oWorkbook10, Default, $sResult10, "F1")
    
    ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("UpdatePivot")
   ControlSend("!R")
   sleep(20000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Hide")
   ControlSend("!R")
   sleep(80000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
    ; Saves the Main File
   WinWaitActive("Microsoft Excel - Per Unit_SSS and Transactions.xlsm")
   ControlSend("^s")
   Sleep(5000)
   
   ;Generates the file to ControlSend to TB
   ControlSend("!FA")
   WinWaitActive("Save As")
   ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult10 & "\P" & $sResult10 & " - Per Unit_SSS and Transactions.xlsm"  )
   ControlSend("!S")
   Sleep(5000)
   ControlSend("{TAB}")
   ControlSend("{ENTER}")
   
    _Excel_BookClose($oWorkbook10, True )
      Sleep(3000)
EndFunc

Func UnitSystem($oApp11)
   
    Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Per Unit_System Sales and Transactions.xlsm"
    Local $oWorkbook11 = _Excel_BookOpen($oApp11, $sWorkbook)
    ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)

   ; Force Excel Window to be active
   WinActivate ( "Microsoft Excel - Per Unit_System Sales and Transactions.xlsm" )
   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Unhide")
   ControlSend("!R")
   sleep(20000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
    Local $sResult11 = _Excel_RangeRead($oWorkbook11, Default, "F1")
    $sResult11 =  $Var_1
    _Excel_RangeWrite($oWorkbook11, Default, $sResult11, "F1")
    
    ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("UpdatePivot")
   ControlSend("!R")
   sleep(20000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Hide")
   ControlSend("!R")
   sleep(80000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
    ; Saves the Main File
   WinWaitActive("Microsoft Excel - Per Unit_System Sales and Transactions.xlsm")
   ControlSend("^s")
   Sleep(5000)
   
   ;Generates the file to ControlSend to TB
   ControlSend("!FA")
   WinWaitActive("Save As")
   ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult11 & "\P" & $sResult11 & " - Per Unit_System Sales and Transactions.xlsm"  )
   ControlSend("!S")
   Sleep(5000)
   ControlSend("{TAB}")
   ControlSend("{ENTER}")
   
    _Excel_BookClose($oWorkbook11, True )
      Sleep(3000)
   
EndFunc

Func Market($oApp12)
   
    Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Sales_Final_Report_Monthly-Q0F.xlsm"
    Local $oWorkbook12 = _Excel_BookOpen($oApp12, $sWorkbook)
    ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)

   ; Force Excel Window to be active
   WinActivate ( "Microsoft Excel - Sales_Final_Report_Monthly-Q0F.xlsm" )
   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Unhide")
   ControlSend("!R")
   sleep(20000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
    Local $sResult12 = _Excel_RangeRead($oWorkbook12, Default, "F1")
    $sResult12 =  $Var_1
    _Excel_RangeWrite($oWorkbook12, Default, $sResult12, "F1")
    
    Sleep(10000)
    
    ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Period_changer")
   ControlSend("!R")
   sleep(3000000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Hide")
   ControlSend("!R")
   sleep(10000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
    ; Saves the Main File
   WinWaitActive("Microsoft Excel - Sales_Final_Report_Monthly-Q0F.xlsm")
   ControlSend("^s")
   Sleep(5000)
   
   ;Generates the file to ControlSend to TB
   ControlSend("!FA")
   WinWaitActive("Save As")
   ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult12 & "\P" & $sResult12 & " - Sales_Final_Report_Monthly-Q0F.xlsm"  )
   ControlSend("!S")
   Sleep(5000)
   ControlSend("{TAB}")
   ControlSend("{ENTER}")
   
    _Excel_BookClose($oWorkbook12, True )
      Sleep(3000)
   
EndFunc


Func StoreCount($oApp3)
   Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Per Brand_Per Region Store Count.xlsm"
   Local $oWorkbook2 = _Excel_BookOpen($oApp3, $sWorkbook)
    ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)

   ; Force Excel Window to be active
   WinActivate ( "Microsoft Excel - Per Brand_Per Region Store Count.xlsm" )
   
   ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Unhide")
   ControlSend("!R")
   sleep(20000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
    Local $sResult3 = _Excel_RangeRead($oWorkbook2, Default, "F1")
    $sResult3 =  $Var_1
    _Excel_RangeWrite($oWorkbook2, Default, $sResult3, "F1")
   
    ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Period_changer")
   ControlSend("!R")
   sleep(20000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")

 ; Runs the macro to update the Pivot table
   ControlSend("!LPM")
   ControlSend("Hide")
   ControlSend("!R")
   sleep(10000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")
   
      ; Saves the Main File
   WinWaitActive("Microsoft Excel - Per Brand_Per Region Store Count.xlsm")
   ControlSend("^s")
   Sleep(5000)

   ;Generates the file to ControlSend to TB
   ControlSend("!FA")
   WinWaitActive("Save As")
   ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult3 & "\P" & $sResult3 & " - Per Brand_Per Region Store Count.xlsm"  )
   ControlSend("!S")
   Sleep(5000)
   ControlSend("{TAB}")
   ControlSend("{ENTER}")
   
   
   
  
   
   ;_Excel_Close($oExcel,true,true)
   
    ;Close excel???
    ;_Excel_BookClose($oWorkbook2, True )
      ;Sleep(3000)
    ControlSend("!fx")
EndFunc
; End of Function

Im trying to get this script to work on a locked computer. it works fine without the computer being locked. Ive trying the controlsend as you see but i couldnt get it to work like with the normal send. please help

Link to comment
Share on other sites
jdelaney Universalist

jdelaney

Posted
Posted (edited)

This is why:

http://www.autoitscript.com/wiki/FAQ#Why_doesn.27t_my_script_work_on_a_locked_workstation.3F

Use the excel functions, not the control|Win functions...the API will get around such issues.

Edited by jdelaney
IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.
Link to comment
Share on other sites
water Universalist

water

Posted
Posted

The Excel functions he is talking about are those listed at the tope of the "Excel Rewrite.au3" include file. If you need more functions you have to write them yourself (I will help if possible).

BTW: Calling _Excel_Open 13 times doesn't help because you get 13 references to the same Excel instance. What do you want to achieve?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites
water Universalist

water

Posted
Posted

BTW:

Could you please give meaningful titles to your threads? Everyone on this forum is searching for help ;)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites
gassis Seeker

gassis

Posted
  • Author
Posted

What we are trying to do is to have a bunch of excel pivottables update to the right period every month without us having to do so . so we have a excel file that will create the vairable number of that period and then that vairable will carry over to each file an update the pivot tables to that period .

Link to comment
Share on other sites
water Universalist

water

Posted
Posted (edited)

Let's do it step by step.

What does the collowing code do?

ControlSend($sWorkbook,"","","{ALT}LPM")
   ControlSend($sWorkbook,"","","UpdateCurrent")
   ControlSend($sWorkbook,"","","!R")
   sleep(10000)
   WinWaitActive( "Microsoft Excel" )
   ControlSend("{ENTER}")

Edit: I have installed a german Excel so the shortcuts don't work for me.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites
jdelaney Universalist

jdelaney

Posted
Posted

Alt LPM opens the macros window.

http://stackoverflow.com/questions/10232150/run-excel-macro-from-outside-excel-using-vbscript-from-command-line

I'm certain there is an excel rewrite function for that though...but the above shows the VBA way, which can be easily converted to autoit.

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.
Link to comment
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
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...