Switching between Excel and another Application

[Mhj96813]

I have a script that finds and open application and then collects some info.  It then opens excel and does a bunch of _Excel_RangeWrite's  to a blank worksheet.  Then it sends some keys to the active application and then does some more _Excel_RangeWrite's.  It does this until conditions are met on the application.   When I'm all done I would like to do some formatting in the excel spreadsheet.  I'm sure its easy but I can't figure out how to make the Excel worksheet active so I can send key strokes to it.  

I start off:

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)
Local $aArray1D[1][30] = [["item","Operation","Resource Group","Resource","description","demand QTY","Delay (days)","Order Minimum","Order Maximum","Order Multiple","Fixed Lead Time (days)","Var. Lead Time (hrs.)","    Expedited Fixed Lead Time (days)","Expedited Var. Lead Time (hrs.)","Start Date    End Date","Work Center","Duration","Number of Members","Delay (days)","Request Date","Usage Date","Supply ID","Inventory Date","On hand Qty","Usage Qty","Shift ID#1","Shift ID#2","Shift ID#3","Shift ID#4"]]

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray1D, "A1")

Then I 

WinActivate("Infor ERP SL (cda) - Demand Detail Chart APS ", "")

I grab some values from the app and send write them to excel

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $val1, "A"& $intRow)

Do I make the excel object active? or the workbook object active?  and if so how?

 

[Skeletor]

Basically, after you insert data into your sheet, you then format it. 

Like this:
 

#include <EXCEL.AU3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)
Local $aArray1D[1][30] = [["item", "Operation", "Resource Group", "Resource", "description", "demand QTY", "Delay (days)", "Order Minimum", "Order Maximum", "Order Multiple", "Fixed Lead Time (days)", "Var. Lead Time (hrs.)", "    Expedited Fixed Lead Time (days)", "Expedited Var. Lead Time (hrs.)", "Start Date    End Date", "Work Center", "Duration", "Number of Members", "Delay (days)", "Request Date", "Usage Date", "Supply ID", "Inventory Date", "On hand Qty", "Usage Qty", "Shift ID#1", "Shift ID#2", "Shift ID#3", "Shift ID#4"]]
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray1D, "A1")

; Use the For Loop to write to all your rows and columns.
For $i = 1 To 30 Step 1
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $val1, "A" & $intRow)
Next


;WinActivate("Infor ERP SL (cda) - Demand Detail Chart APS ", "")

$oWorkbook.Sheets(1).Name = "Infor ERP SL (cda) - Demand Detail Chart APS " ;Rename your worksheet
; Note if you have more than one worksheet on an Excel work book, then name it according.
;=================================================
;Example: Sheets(1), Sheets(2), Sheets(3), etc...
;=================================================
; $oWorkbook.Sheets(1).Name = ""
; $oWorkbook.Sheets(2).Name = ""
; $oWorkbook.Sheets(3).Name = ""

$oWorkbook.ActiveSheet.Columns("A:AC").AutoFit ;Auto fits all the colomns... can remove this if its not required.
With $oExcel.ActiveWorkbook.Sheets(1)
    .Range("A1:AC1").Font.Bold = True ;Makes the font bold
    .Range("A1:AC1").Interior.Color = 0xABCDEF ; Adds color
    .Range("A1:AC1").Borders.LineStyle = 1 ;Adds a border to the row and column
EndWith
; Then Save the file
_Excel_BookSaveAs($oWorkbook, @TempDir, $xlExcel8, True) ;Excel 97-2003 Workbook

By the way, all thanks to @water for this Excel UDF. This UDF saves me a ton of time everyday at work.

Edited February 22, 2019 by Skeletor

[water]

2 hours ago, Skeletor said:

By the way, all thanks to @water for this Excel UDF. This UDF saves me a ton of time everyday at work.

Glad you like the UDF

[LandonEvans]

I use ZetExcel and it does everything I need with regards to parsing MS Excel files. https://zetexcel.com/

[Skeletor]

1 hour ago, LandonEvans said:

I use ZetExcel and it does everything I need with regards to parsing MS Excel files. https://zetexcel.com/

Cool, but I wont be rushing anytime soon to place a $99 Dev license... 
How bout showing some examples of what you mean?

[water]

The post count of LandonEvans is 2 and both posts were to advertise this/his tool.
Seems he just came here to sell the product

[Nine]

2 hours ago, water said:

The post count of LandonEvans is 2 and both posts were to advertise this/his tool.
Seems he just came here to sell the product