16 replies to this topic

[Mickers]

My issue is if I have multiple Excel windows open the script switches dynamicly to the LAST active window. I want it to set to the active window ONE time then stop looking if I change windows or now.
Trouble area:

ButtonSet: ;this is the trouble area
  Excel_Ptr := ComObjActive("Excel.Application") ;sets currently active sheet
  Row_Pointer = 2
  Gui,Submit
  GuiControl,,Subscriber_ID,% Excel_Ptr.Range("C" . Row_Pointer).Value
  GuiControl,,Customer_Name,% Excel_Ptr.Range("D" . Row_Pointer).Value
  GuiControl,,Account_Number,% Excel_Ptr.Range("F" . Row_Pointer).Value
  GuiControl,,Return_Reason,% Excel_Ptr.Range("H" . Row_Pointer).Value
  GuiControl,,Return_Amount,% Excel_Ptr.Range("I" . Row_Pointer).Value
  GuiControl,,Debit_Excp,% Excel_Ptr.Range("J" . Row_Pointer).Value
  GuiControl,,Action,% Excel_Ptr.Range("K" . Row_Pointer).Value
  Gui,Show
  Return

Full script:

Gui,+AlwaysOnTop +ToolWindow -SysMenu
Gui,Add,Button,Section,Exit
Gui,Add,Button,R1 YS,Set
Gui,Add,Button,R1 YS,Back
Gui,Add,Edit,vSubscriber_ID ReadOnly R1 W100 YS,% Excel_Ptr.Range("C" . Row_Pointer).Value
Gui,Add,Edit,vCustomer_Name ReadOnly R1 W100 YS,% Excel_Ptr.Range("D" . Row_Pointer).Value
Gui,Add,Edit,vAccount_Number ReadOnly R1 W100 YS,% Excel_Ptr.Range("F" . Row_Pointer).Value
Gui,Add,Edit,vReturn_Reason ReadOnly R1 W30 YS,% Excel_Ptr.Range("H" . Row_Pointer).Value
Gui,Add,Edit,vReturn_Amount ReadOnly R1 W50 YS,% Excel_Ptr.Range("I" . Row_Pointer).Value
Gui,Add,Edit,vDebit_Excp ReadOnly CRed Uppercase R1 W25 YS,% Excel_Ptr.Range("J" . Row_Pointer).Value
Gui,Add,Edit,vAction ReadOnly R1 W25 Center YS,% Excel_Ptr.Range("K" . Row_Pointer).Value
Gui,Add,Button,R1 YS,EC
Gui,Add,Button,R1 YS,NS
;Gui,Add,Button,YS,Continue
;Gui,Add,DDL,vOther_Action gOther W65 YS,--Other--||DE|High$|Purged
Gui,Show
Return

;-------------Buttons
ButtonExit:
  ExitApp
ButtonSet: ;this is the trouble area
  Excel_Ptr := ComObjActive("Excel.Application") ;sets currently active sheet
  Row_Pointer = 2
  Gui,Submit
  GuiControl,,Subscriber_ID,% Excel_Ptr.Range("C" . Row_Pointer).Value
  GuiControl,,Customer_Name,% Excel_Ptr.Range("D" . Row_Pointer).Value
  GuiControl,,Account_Number,% Excel_Ptr.Range("F" . Row_Pointer).Value
  GuiControl,,Return_Reason,% Excel_Ptr.Range("H" . Row_Pointer).Value
  GuiControl,,Return_Amount,% Excel_Ptr.Range("I" . Row_Pointer).Value
  GuiControl,,Debit_Excp,% Excel_Ptr.Range("J" . Row_Pointer).Value
  GuiControl,,Action,% Excel_Ptr.Range("K" . Row_Pointer).Value
  Gui,Show
  Return
ButtonBack:
  Gui,Submit
  GuiControl,,Row_Pointer,% Row_Pointer--
  GuiControl,,Subscriber_ID,% Excel_Ptr.Range("C" . Row_Pointer).Value
  GuiControl,,Customer_Name,% Excel_Ptr.Range("D" . Row_Pointer).Value
  GuiControl,,Account_Number,% Excel_Ptr.Range("F" . Row_Pointer).Value
  GuiControl,,Return_Reason,% Excel_Ptr.Range("H" . Row_Pointer).Value
  GuiControl,,Return_Amount,% Excel_Ptr.Range("I" . Row_Pointer).Value
  GuiControl,,Debit_Excp,% Excel_Ptr.Range("J" . Row_Pointer).Value
  GuiControl,,Action,% Excel_Ptr.Range("K" . Row_Pointer).Value
  Gui,Show
  Return
ButtonEC:
  Excel_Ptr.Range("K" . Row_Pointer).Value := "EC"
  Gui,Submit
  GuiControl,,Row_Pointer,% Row_Pointer++
  GuiControl,,Subscriber_ID,% Excel_Ptr.Range("C" . Row_Pointer).Value
  GuiControl,,Customer_Name,% Excel_Ptr.Range("D" . Row_Pointer).Value
  GuiControl,,Account_Number,% Excel_Ptr.Range("F" . Row_Pointer).Value
  GuiControl,,Return_Reason,% Excel_Ptr.Range("H" . Row_Pointer).Value
  GuiControl,,Return_Amount,% Excel_Ptr.Range("I" . Row_Pointer).Value
  GuiControl,,Debit_Excp,% Excel_Ptr.Range("J" . Row_Pointer).Value
  GuiControl,,Action,% Excel_Ptr.Range("K" . Row_Pointer).Value
  Gui,Show
  Return
ButtonNS:
  Excel_Ptr.Range("K" . Row_Pointer).Value := "NS"
  Gui,Submit
  GuiControl,,Row_Pointer,% Row_Pointer++
  GuiControl,,Subscriber_ID,% Excel_Ptr.Range("C" . Row_Pointer).Value
  GuiControl,,Customer_Name,% Excel_Ptr.Range("D" . Row_Pointer).Value
  GuiControl,,Account_Number,% Excel_Ptr.Range("F" . Row_Pointer).Value
  GuiControl,,Return_Reason,% Excel_Ptr.Range("H" . Row_Pointer).Value
  GuiControl,,Return_Amount,% Excel_Ptr.Range("I" . Row_Pointer).Value
  GuiControl,,Debit_Excp,% Excel_Ptr.Range("J" . Row_Pointer).Value
  GuiControl,,Action,% Excel_Ptr.Range("K" . Row_Pointer).Value
  Gui,Show
  Return
Other:
  Return

Thanks

[CodeKiller]

Just use a boolean... You put it to true the time, then you continue only if it's false (so it will execute only one time).
perhaps a key to reset the boolean to false...
Example :

If Switch
{
    Switch := False
    ; The ONCE code here
    ; This code will not be executed again since the Switch is False
}

[Mickers]

Thanks a lot!

[Mickers]

Alright I spoke too soon this method does not work at all.

If Switch
{
    Switch := False
    ; The ONCE code here
    ; This code will not be executed again since the Switch is False
}

I have tried creating a button that resets it. I have tried "If !Switch" and setting Switch :=True. I have tried everything but no matter what I do if I so much as look at another excel sheet, I don't press any Gui buttons at all, it resets everything to the other sheet. What am I missing? There seems to be no safe place to put this code that will keep it from reseting dynamicly??? :?
Next I'll try putting it in a function and calling it once with a button. Any other idea's?

EDIT:
Here's what I've tried. All other code is same as above. The Button:

ButtonSet:
  Switch := Set_Boolian(Switch)
  If Switch = 2
  {
    Switch := 3

    Excel_Ptr := ComObjActive("Excel.Application")
    Row_Pointer = 2
    Gui,Submit
    GuiControl,,Subscriber_ID,% Excel_Ptr.Range("C" . Row_Pointer).Value
    GuiControl,,Customer_Name,% Excel_Ptr.Range("D" . Row_Pointer).Value
    GuiControl,,Account_Number,% Excel_Ptr.Range("F" . Row_Pointer).Value
    GuiControl,,Return_Reason,% Excel_Ptr.Range("H" . Row_Pointer).Value
    GuiControl,,Return_Amount,% Excel_Ptr.Range("I" . Row_Pointer).Value
    GuiControl,,Debit_Excp,% Excel_Ptr.Range("J" . Row_Pointer).Value
    GuiControl,,Action,% Excel_Ptr.Range("K" . Row_Pointer).Value
    Gui,Show
  }
  Return

The function:

Set_Boolian(Boolian)
{
  If Boolian =
    Boolian := 2
  Else Boolian := 3
  Return Boolian
}

As far as I can see there is no way to stop it from pointing to the active sheet. I think the problem lies within "ComObjActive("Excel.Application")" it seems to be able to permanently set the pointer to whaterver excel sheet is active at any time only being called once. Of course what do I know Lol.

[Mickers]

@a4u
I had to go to a team meeting while the post before yours was in limbo. Then I sumbitted it before seeing your reply.
I added your 2 functions and replaced my Excel_Pointer code with your example BUT it still does the same thing... If I look at any other sheet, that I don't want my script to point to, it reverts to the newest sheet I've looked at... I really don't know how to get around this.
My script used to have the user choose a file and set the pointer to that and it worked just fine, no issues. But for what I use it for it takes far to long to do that everytime I need to work a new sheet.
Thanks again. :cry:

Changed code:

ButtonSet:
    ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN
    if Not Excel_Ptr := Acc_ObjectFromWindow(hwnd, -16).Application
      ExitApp

    Row_Pointer = 2
    Gui,Submit
    GuiControl,,Subscriber_ID,% Excel_Ptr.Range("C" . Row_Pointer).Value
    GuiControl,,Customer_Name,% Excel_Ptr.Range("D" . Row_Pointer).Value
    GuiControl,,Account_Number,% Excel_Ptr.Range("F" . Row_Pointer).Value
    GuiControl,,Return_Reason,% Excel_Ptr.Range("H" . Row_Pointer).Value
    GuiControl,,Return_Amount,% Excel_Ptr.Range("I" . Row_Pointer).Value
    GuiControl,,Debit_Excp,% Excel_Ptr.Range("J" . Row_Pointer).Value
    GuiControl,,Action,% Excel_Ptr.Range("K" . Row_Pointer).Value
    Gui,Show
  Return

And your functions as they are within my script:

; Written by Sean
Acc_Init()
{
   Static   h
   If Not   h
      h:=DllCall("LoadLibrary","Str","oleacc","Ptr")
}
Acc_ObjectFromWindow(hWnd, idObject = -4)
{
   Acc_Init()
   If   DllCall("oleacc\AccessibleObjectFromWindow", "Ptr", hWnd, "UInt", idObject&=0xFFFFFFFF, "Ptr", -VarSetCapacity(IID,16)+NumPut(idObject==0xFFFFFFF0?0x46000000000000C0:0x719B3800AA000C81,NumPut(idObject==0xFFFFFFF0?0x0000000000020400:0x11CF3C3D618736E0,IID,"Int64"),"Int64"), "Ptr*", pacc)=0
   Return   ComObjEnwrap(9,pacc)
}

[tank]

PEBKAC

:twisted: :roll: :lol:

[Mickers]

I'll add that snippet Lol. I guess that would make sense since I only set my pointer to the active worksheet not the individual sheet itself. PEBKAC? FYI it works now thanks for your help.

[Mickers]

I don't fully understand how to use the MSDN with Ahk just by reading it and trying to apply it to my scripts. However it is a skill I want and need to create the kind of scripts that will make my life/job a lot easier. The most difficult part is taking their examples in VBA and trasnlating them in a way Ahk will understand.
I modified my code without those functions and instead only use:

Excel_Ptr := ComObjActive("Excel.Application")
  Sheet_Ptr := Excel_Ptr.ActiveSheet

And it works the same. Thanks again and I'll be trying to understand the MSDN better.

[sinkfaze]

Don't let objects confuse you too well, it's simply a matter of doing one thing in order to do another. Oversimplified example: Suppose you want to go visit your friend at their office, which is in a high rise downtown. To visit them first you need to get to where the buildings are (Application), then you need to get to the right building (Workbook), then you need to go to the right floor in that building (Worksheet), then you need to go down the right hall on that floor (Range), to finally get to their office (Cell).

Excel makes the trip easier since it can assume that you already know which building (ActiveWorkbook) and which floor (ActiveSheet) you're going to. So when you write this:

Excel_Ptr.Range("C" . Row_Pointer).Value

What's really happening in effect is this:

Excel_Ptr[color=red].ActiveWorkbook.ActiveSheet[/color].Range("C" . Row_Pointer).Value

As far as MSDN is concerned, when you're looking to access a method/property you'll often find that it applies to several objects, just look only at the objects that appear should apply to you. If you have trouble translating code, post it here, no one's afraid to help. :wink:

[Mickers]

I'm starting to have another issue where my scrip refuses to use my active sheet and will only point to a sheet I opened an hour ago and haven't even looked at recently. What causes this?
EDIT:
Another issue I have encounted is if I try to pass my script as a exe file on to a coworker it doesn't work at all. The gui pops up and causes the excel sheet to flash if I click the set button but no information is displayed.

#NoTrayIcon

Gui,+AlwaysOnTop +ToolWindow -SysMenu
Gui,Add,Button,Section,Exit
Gui,Add,Button,R1 YS,Set
Gui,Add,Button,R1 YS,Back
Gui,Add,Edit,vSubscriber_ID ReadOnly R1 W100 YS,% Sheet_Ptr.Range("C" . Row_Pointer).Value
Gui,Add,Edit,vCustomer_Name ReadOnly R1 W100 YS,% Sheet_Ptr.Range("D" . Row_Pointer).Value
Gui,Add,Edit,vAccount_Number ReadOnly R1 W100 YS,% Sheet_Ptr.Range("F" . Row_Pointer).Value
Gui,Add,Edit,vReturn_Reason ReadOnly R1 W30 YS,% Sheet_Ptr.Range("H" . Row_Pointer).Value
Gui,Add,Edit,vReturn_Amount ReadOnly R1 W50 YS,% Sheet_Ptr.Range("I" . Row_Pointer).Value
Gui,Add,Edit,vDebit_Excp ReadOnly CRed Uppercase R1 W25 YS,% Sheet_Ptr.Range("J" . Row_Pointer).Value
Gui,Add,Edit,vAction ReadOnly R1 W25 Center YS,% Sheet_Ptr.Range("K" . Row_Pointer).Value
Gui,Add,Button,R1 YS,EC
Gui,Add,Button,R1 YS,NS
Gui,Add,Button,R1 YS,Next
Gui,Show
Return

;-------------Buttons
ButtonExit:
  ExitApp
ButtonSet: ;this is the trouble area 
  Excel_Ptr := ComObjActive("Excel.Application") ;sets active book 
  Sheet_Ptr := Excel_Ptr.ActiveSheet ;sets active sheet
  Row_Pointer = 2 
  Gui,Submit 
  GuiControl,,Subscriber_ID,% Sheet_Ptr.Range("C" . Row_Pointer).Value 
  GuiControl,,Customer_Name,% Sheet_Ptr.Range("D" . Row_Pointer).Value 
  GuiControl,,Account_Number,% Sheet_Ptr.Range("F" . Row_Pointer).Value 
  GuiControl,,Return_Reason,% Sheet_Ptr.Range("H" . Row_Pointer).Value 
  GuiControl,,Return_Amount,% Sheet_Ptr.Range("I" . Row_Pointer).Value 
  GuiControl,,Debit_Excp,% Sheet_Ptr.Range("J" . Row_Pointer).Value 
  GuiControl,,Action,% Sheet_Ptr.Range("K" . Row_Pointer).Value 
  Gui,Show 
  Return
ButtonBack:
  Gui,Submit
  GuiControl,,Row_Pointer,% Row_Pointer--
  GuiControl,,Subscriber_ID,% Sheet_Ptr.Range("C" . Row_Pointer).Value
  GuiControl,,Customer_Name,% Sheet_Ptr.Range("D" . Row_Pointer).Value
  GuiControl,,Account_Number,% Sheet_Ptr.Range("F" . Row_Pointer).Value
  GuiControl,,Return_Reason,% Sheet_Ptr.Range("H" . Row_Pointer).Value
  GuiControl,,Return_Amount,% Sheet_Ptr.Range("I" . Row_Pointer).Value
  GuiControl,,Debit_Excp,% Sheet_Ptr.Range("J" . Row_Pointer).Value
  GuiControl,,Action,% Sheet_Ptr.Range("K" . Row_Pointer).Value
  Gui,Show
  Return
ButtonEC:
  Sheet_Ptr.Range("K" . Row_Pointer).Value := "EC"
  Gui,Submit
  GuiControl,,Row_Pointer,% Row_Pointer++
  GuiControl,,Subscriber_ID,% Sheet_Ptr.Range("C" . Row_Pointer).Value
  GuiControl,,Customer_Name,% Sheet_Ptr.Range("D" . Row_Pointer).Value
  GuiControl,,Account_Number,% Sheet_Ptr.Range("F" . Row_Pointer).Value
  GuiControl,,Return_Reason,% Sheet_Ptr.Range("H" . Row_Pointer).Value
  GuiControl,,Return_Amount,% Sheet_Ptr.Range("I" . Row_Pointer).Value
  GuiControl,,Debit_Excp,% Sheet_Ptr.Range("J" . Row_Pointer).Value
  GuiControl,,Action,% Sheet_Ptr.Range("K" . Row_Pointer).Value
  Gui,Show
  Return
ButtonNS:
  Sheet_Ptr.Range("K" . Row_Pointer).Value := "NS"
  Gui,Submit
  GuiControl,,Row_Pointer,% Row_Pointer++
  GuiControl,,Subscriber_ID,% Sheet_Ptr.Range("C" . Row_Pointer).Value
  GuiControl,,Customer_Name,% Sheet_Ptr.Range("D" . Row_Pointer).Value
  GuiControl,,Account_Number,% Sheet_Ptr.Range("F" . Row_Pointer).Value
  GuiControl,,Return_Reason,% Sheet_Ptr.Range("H" . Row_Pointer).Value
  GuiControl,,Return_Amount,% Sheet_Ptr.Range("I" . Row_Pointer).Value
  GuiControl,,Debit_Excp,% Sheet_Ptr.Range("J" . Row_Pointer).Value
  GuiControl,,Action,% Sheet_Ptr.Range("K" . Row_Pointer).Value
  Gui,Show
  Return
ButtonNext:
  Gui,Submit
  GuiControl,,Row_Pointer,% Row_Pointer++
  GuiControl,,Subscriber_ID,% Sheet_Ptr.Range("C" . Row_Pointer).Value
  GuiControl,,Customer_Name,% Sheet_Ptr.Range("D" . Row_Pointer).Value
  GuiControl,,Account_Number,% Sheet_Ptr.Range("F" . Row_Pointer).Value
  GuiControl,,Return_Reason,% Sheet_Ptr.Range("H" . Row_Pointer).Value
  GuiControl,,Return_Amount,% Sheet_Ptr.Range("I" . Row_Pointer).Value
  GuiControl,,Debit_Excp,% Sheet_Ptr.Range("J" . Row_Pointer).Value
  GuiControl,,Action,% Sheet_Ptr.Range("K" . Row_Pointer).Value
  Gui,Show
  Return

[Mickers]

Yes that does solve the issue but i'm against using anything that I don't understand. I use a few functions that I know work but couldn't write myself but I try to limit those if possible. Thanks for your help.