Use AutoIT to open a file in Microsoft TEAMS

[Valnurat]

Well, now I got it to work with the change that @Danp2 then I still have a question.

Can this Excel UDF help me to get the colors for the cells?

[Nine]

Yes you can do anything.  You have a COM Excel object.  Everything that has been developed by MicroSoft is then available.  Google your request, you will get plenty of examples.

[Valnurat]

On 5/7/2020 at 8:42 PM, Nine said:

Yes you can do anything.  You have a COM Excel object.  Everything that has been developed by MicroSoft is then available.  Google your request, you will get plenty of examples.

I'm sorry, I tried but I can't find what I look for. 

I have seen how to change a color, but not how to pick it and to compare.

😟

[Nine]

https://docs.microsoft.com/en-us/office/vba/api/overview/excel/object-model

There you go.  Start reading, everything you need is truly well explained.

[water]

This should give you an idea: https://stackoverflow.com/questions/520570/return-background-color-of-selected-cell

[Valnurat]

5 hours ago, water said:

This should give you an idea: https://stackoverflow.com/questions/520570/return-background-color-of-selected-cell

Thank you @water

I know it might seems easy, but I can't figure it out. Sorry for that.

But I tried this and I just get an error in the msgbox.

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

; Create application object
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; Open an existing workbook and return its object identifier.
Local $sWorkbook = "C:\Users\DKSOKVK\OneDrive\Documents\Schedule Service Desk 2020.xlsx"
Local $oWorkbook = _Excel_BookOpenEx($oExcel, $sWorkbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Workbook '" & $sWorkbook & "' has been opened successfully." & @CRLF & @CRLF & "Creation Date: " & $oWorkbook.BuiltinDocumentProperties("Creation Date").Value)

MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen", $oExcel.ActiveWorkbook.Sheets(1).activeCell.Interior.Color)

 

[Nine]

Read this https://docs.microsoft.com/en-us/office/vba/api/excel.application.activecell

 

[Valnurat]

I don't understand it.

It seems now that I'm not going to use Excel UDF or.

🤔

[water]

Excel provides COM as an API to Excel functionality to be used by programs/scripts. The alternative is the GUI.
The Excel UDF is a wrapper around heavily used functions. It doesn't cover everything Excei provides. If you need more you can easily use COM in your scripts.

Maybe I'm going to translate the example I linked to above to AutoIt and post the script in the example scripts forum

[Valnurat]

So if I should use COM, how do I do that?

 

🙏

Edited May 9, 2020 by Valnurat

[water]

Quick and dirty:

#include <Excel.au3>

; Reference: https://www.rapidtables.com/convert/color/rgb-to-hex.html
; Cell A1 background as RGB = 64, 128, 192 returns the following results:
; 64,128,192
; 0x4080C0
; 42
; 12615744

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Local\Test.xlsx")
ConsoleWrite(_Excel_BackgroundColorGet($oExcel.ActiveSheet.Range("A1"), "RGB") & @CRLF) ; RGB
ConsoleWrite(_Excel_BackgroundColorGet($oExcel.ActiveSheet.Range("A1"), "Hex") & @CRLF) ; HEX
ConsoleWrite(_Excel_BackgroundColorGet($oExcel.ActiveSheet.Range("A1"), "IDX") & @CRLF) ; IDX
ConsoleWrite(_Excel_BackgroundColorGet($oExcel.ActiveSheet.Range("A1")) & @CRLF) ; Decimal color value

Func _Excel_BackgroundColorGet($oRange, $sFormat = Default)
    ; formatType: Hex for 0xRRGGBB, RGB for (R,G,B) and IDX for VBA Color Index, Default for the decimal value
    $iColorValue = $oRange.DisplayFormat.Interior.Color
    Local $vColor
    Switch $sFormat
        Case "HEX"
            $vColor = "0x" & StringLeft(Hex(Mod($iColorValue, 256)), 2) & _
                    Hex(Mod(Int($iColorValue / 256), 256), 2) & _
                    Hex(Int($iColorValue / 65536), 2)
        Case "RGB"
            $vColor = Mod($iColorValue, 256) & "," & _
                    Mod(Int($iColorValue / 256), 256) & "," & _
                    Int($iColorValue / 65536)
        Case "IDX"
            $vColor = $oRange.Interior.ColorIndex
        Case Else
            $vColor = $iColorValue
    EndSwitch
    Return $vColor
EndFunc   ;==>_Excel_BackgroundColorGet

 

[Nine]

Hmmm, spoon-feeding taste so good ! 

[water]

At least we come to an end
If the function works I will add it as an example for others

[Nine]

Yummy !  

[Valnurat]

56 minutes ago, water said:

Quick and dirty:

#include <Excel.au3>

; Reference: https://www.rapidtables.com/convert/color/rgb-to-hex.html
; Cell A1 background as RGB = 64, 128, 192 returns the following results:
; 64,128,192
; 0x4080C0
; 42
; 12615744

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Local\Test.xlsx")
ConsoleWrite(_Excel_BackgroundColorGet($oExcel.ActiveSheet.Range("A1"), "RGB") & @CRLF) ; RGB
ConsoleWrite(_Excel_BackgroundColorGet($oExcel.ActiveSheet.Range("A1"), "Hex") & @CRLF) ; HEX
ConsoleWrite(_Excel_BackgroundColorGet($oExcel.ActiveSheet.Range("A1"), "IDX") & @CRLF) ; IDX
ConsoleWrite(_Excel_BackgroundColorGet($oExcel.ActiveSheet.Range("A1")) & @CRLF) ; Decimal color value

Func _Excel_BackgroundColorGet($oRange, $sFormat = Default)
    ; formatType: Hex for 0xRRGGBB, RGB for (R,G,B) and IDX for VBA Color Index, Default for the decimal value
    $iColorValue = $oRange.DisplayFormat.Interior.Color
    Local $vColor
    Switch $sFormat
        Case "HEX"
            $vColor = "0x" & StringLeft(Hex(Mod($iColorValue, 256)), 2) & _
                    Hex(Mod(Int($iColorValue / 256), 256), 2) & _
                    Hex(Int($iColorValue / 65536), 2)
        Case "RGB"
            $vColor = Mod($iColorValue, 256) & "," & _
                    Mod(Int($iColorValue / 256), 256) & "," & _
                    Int($iColorValue / 65536)
        Case "IDX"
            $vColor = $oRange.Interior.ColorIndex
        Case Else
            $vColor = $iColorValue
    EndSwitch
    Return $vColor
EndFunc   ;==>_Excel_BackgroundColorGet

 

I changed the path to my file. When I try the function, I get this:

 

"C:\autoit\AnotherKalenderTest.au3" (12) : ==> Variable must be of type "Object".:
ConsoleWrite(_Excel_BackgroundColorGet($oExcel.ActiveSheet.Range("D6"), "RGB") & @CRLF)
ConsoleWrite(_Excel_BackgroundColorGet($oExcel^ ERROR

 

[Nine]

Yum Yum is coming.........

[water]

Either you dropped the _Excel_Open statement from my example or Excel isn't installed on the machien where you run the script.
Check @error and @extended after _Excel_Open and see what's going on.

[Valnurat]

11 hours ago, water said:

At least we come to an end
If the function works I will add it as an example for others

I can't thank you enough.

This I couldn't figure out.

Your function works, spot on.

[water]