How to control Excel with AHK

[Siopaulo]

I know how to create Excel object to handle it: oW := ComObjActive("Excel.Application")
I know how to give value to cells on the sheet: oW.ActiveSheet.Cells(1,1).Value := Pole[1]
It took me a lot of time to find just a few simple things like above.

1. Is there some list of all controllable parameters with the right syntax. The syntax for VBA not always work even it is here recommended.

2. Can you write me correct AHK syntax for "Protect workbook structure". (You can find it under File/Info tab) VBA syntax is following: ActiveSheet.Protect "password", "structure", "windows"

3. Can you write me correct AHK syntax for "Encrypt with Password". (You can find it under File/Info tab) VBA syntax is not known for me, sorry.

Thanks a lot.
Sio

[Siopaulo]

Please help

[Thoughtfu1Tux]

Have you looked at the Excel COM tutorial in the old forum? I used the guide for all of my Excel automation. Read through all the replies to it for even further guides posted by different members of the community.

https://autohotkey.com/board/topic/6903 ... for-excel/

[Siopaulo]

Yes, I looked and used some of the codes but what I am looking for is a complete list of excel parameters I can automate with correct syntax. This is something I am not able to find anywhere.

[FanaticGuru]

Yes, I looked and used some of the codes but what I am looking for is a complete list of excel parameters I can automate with correct syntax. This is something I am not able to find anywhere.

The Microsoft MSDN for Excel contains all the properites, methods, and events of the Excel COM object.
https://docs.microsoft.com/en-us/office ... on(object)
It is a giant branching tree with many layers of properties and methods.

FG

[Siopaulo]

Thanks a lot, Guru. This is something I had found. The problem I have is then the syntax. Usually, the code is for VB and not working in AHK ... still giving some errors or unknown.

For example this: "Protect workbook structure". (You can find it under File/Info tab) VBA syntax is following: ActiveSheet.Protect "password", "structure", "windows" ... in VBA it works, in AHK at all.

[FanaticGuru]

Thanks a lot, Guru. This is something I had found. The problem I have is then the syntax. Usually, the code is for VB and not working in AHK ... still giving some errors or unknown.

For example this: "Protect workbook structure". (You can find it under File/Info tab) VBA syntax is following: ActiveSheet.Protect "password", "structure", "windows" ... in VBA it works, in AHK at all.

You just look the "protect" method up in the MSDN:
https://docs.microsoft.com/en-us/office ... et.protect

It shows the syntax as this:

Code: Select all

expression.Protect( _Password_ , _DrawingObjects_ , _Contents_ , _Scenarios_ , _UserInterfaceOnly_ , _AllowFormattingCells_ , _AllowFormattingColumns_ , _AllowFormattingRows_ , _AllowInsertingColumns_ , _AllowInsertingRows_ , _AllowInsertingHyperlinks_ , _AllowDeletingColumns_ , _AllowDeletingRows_ , _AllowSorting_ , _AllowFiltering_ , _AllowUsingPivotTables_ )
expression A variable that represents a Worksheet object.

I don't know what the "structure", "windows" is about but to put a password on the active sheet you do this:

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlApp.ActiveSheet.Protect("password")

It is important to understand that AHK does not allow named parameters. This is not about COM this is just the difference between how VBA and AHK handle function parameters. In AHK, you have to put the parameters in the correct slot as shown in the syntax above. VBA also does not require that you use () with functions. Again a VBA thing, not a COM thing. In fact the VBA is not following the recommended syntax from the MSDN reference material.

So if you wanted to set _AllowUsingPivotTables_ in AHK you have to put in a bunch of blank parameters: xlApp.ActiveSheet.Protect("password",,,,,,,,,,,,,,,true)

VBA can be useful for examples of how to do things with COM but the MSDN is what is important. VBA and AHK are both accessing the object described in the MSDN.

Edit: I notice now you are mixing the protecting of a workbook and worksheet together. In your example you used ActiveSheet.Protect "password", "structure", "windows". That probably does not work because that is the syntax for a workbook and you used ActiveSheet.

If you want to protect a Workbook this is the link: https://docs.microsoft.com/en-us/office ... ok.protect
And this is the syntax:

Code: Select all

expression.Protect( _Password_ , _Structure_ , _Windows_ )
expression A variable that represents a Workbook object.

FG

[Siopaulo]

Thank you, trying to more understand. I did following syntax and it does not work. What could be wrong? No errors from AHK. Object oW is created but then nothing will happen.

oW := ComObjActive("Excel.Application")
Sleep, 500
oW.Protect("password",true,true)

[awel20]

Code: Select all

expression A variable that represents a Worksheet object.

Code: Select all

xlApp.ActiveSheet.Protect("password")

Code: Select all

oW := ComObjActive("Excel.Application")
oW.Protect("password",true,true)

Siopaulo, you're trying to use Application.Protect, which does not exist.
Workbook.Protect and Worksheet.Protect on the other hand...

[FanaticGuru]

Thank you, trying to more understand. I did following syntax and it does not work. What could be wrong? No errors from AHK. Object oW is created but then nothing will happen.

oW := ComObjActive("Excel.Application")
Sleep, 500
oW.Protect("password",true,true)

Like has been stated you are trying to protect the Excel application object when the syntax requires that you protect a workbook object.

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlApp.ActiveWorkbook.Protect("password", ComObject(0xB, -1), ComObject(0xB, -1))

This will protect the structure and windows of the current active workbook.

The true and false can sometimes be tricky when working with COM object.

Short Answer:
For true use: ComObject(0xB, -1)
For false use: ComObject(0xB, 0)

Long Answer:
Some COM methods require that you use a COM object true or false. This has to do with data types which in AHK you normally do not have to worry about but many other languages require data to be of a certain stated data type. On the MSDN page it list that it is expecting the two last parameters to be Data Type, Variant and Boolean. 0xB is the code for VT_BOOL data type. In Variant Boolean data types -1 is true and 0 is false.

FG

[Siopaulo]

Thank you so much. Without you, I won´t be able to figure it out. I will test it and make my notes. Anyway as you said something is tricky but I believe I get better in such things through the time.