AutoHotkey Community

#include COM.ahk
#include functions.ahk
#include CoHelper.ahk

; INFORMATION
; Especially look at the Loop below


; Options
input_filename=in.xls
;input_title_pattern=Microsoft Excel - %input_filename%
input_title_pattern=%input_filename%
winactivate_timeout=4

; General
#SingleInstance
#Persistent
;#WinActivateForce
SetWorkingDir %A_ScriptDir%
SetTitleMatchMode, 2

; GUI
Menu, Tray, Tip, TestCase

; Menu
Menu, Tray, NoStandard
Menu, Tray, Add, Windows Spy, RunWinSpy
Menu, Tray, Add, Edit
Menu, Tray, Add, Reload
Menu, Tray, Add, Exit
Menu, Tray, Default, Reload
Return
RunWinSpy:
Run, C:\Program Files\AutoHotkey\AU3_Spy.exe
return
Edit:
Edit
Return
Reload:
Reload
Return
Exit:
ExitApp
return

; Predefined functions

ToolTipRemove:
  SetTimer, ToolTipRemove2, 3000
  return
ToolTipRemove2:
  SetTimer, ToolTipRemove2, Off
  ToolTip
  return

; The Script

#a::
Sleep 500
FileGetSize, tmp, %input_filename%
if ErrorLevel
  MsgBox, in files does not exist
else {
  ifWinExist %input_title_pattern%
  {
    WinActivate
  }
  else {
    Run, %input_filename%
  }
  WinWaitActive, %input_title_pattern%, , %winactivate_timeout%
  if ErrorLevel
    MsgBox, error2
  else {
    Sleep 100
    Send {Esc}
    Send ^{Home}
    Sleep 500
    Send {Down 2}{Right}
    Sleep 50
    Send {F2}
    Sleep 50
    Send {End}
    Send +{Home}
    Sleep 50
    Send ^c
    Sleep 50
    Send {Esc}
    Sleep 50
    output_filename=%clipboard%.xls
    output_title_pattern=%output_filename%
    ifWinExist %output_title_pattern%
      WinActivate
    else {
      FileGetSize, tmp, %output_filename%
      if ErrorLevel {
        MsgBox, no file - %output_filename%
        ExitApp
      }
      else {
        ifWinExist %output_title_pattern%
        {
          WinActivate
          Sleep 50
        }
        else {
          Run, %output_filename%
        }
      }
    }
    WinWaitActive, %output_title_pattern%, , %winactivate_timeout%
    if ErrorLevel {
      MsgBox, couldn't open window
      ExitApp
    }
    else {
      Sleep 50
      Send ^{Home}
      Sleep 50
      WinActivate, %input_title_pattern%
      Send {Esc}
      Sleep 50
      Send {Down}{Home}
      Sleep 50
      Loop, 1 {
        A:=Excel_GetCell("A", 3)
        MsgBox % A
      }
    }
  }
}
;Progress, Off
return

COM_CoInitialize()
OnExit,exits
if !oExcel := COM_GetActiveObject("Excel.Application")
{
   oExcel := COM_ActiveXObject("Excel.Application")
   COM_Invoke(oExcel, "Visible=", true)
;~    you could use either of these methods
;~    COM_Invoke(oExcel,"workbooks.open","Replace with path here")
;~    COM_Invoke(oExcel,"workbooks.add")
}
COM_Invoke(oExcel, "Visible=", true)
excelCount:=COM_Invoke(oExcel,"workbooks.count")
MsgBox   % " count of open workbooks " excelCount
If   excelCount!=2
{
   WB1:=COM_Invoke(oExcel,"workbooks.add")
;~    get the name of the worksheet
   WB1Name:=COM_Invoke(WB1,"Name")
   WB2:=COM_Invoke(oExcel,"workbooks.add")
;~    WB2:=COM_Invoke(oExcel,"workbooks.open","Replace with path here")
;~    get the name of the worksheet
   WB2Name:=COM_Invoke(WB2,"Name")
   COM_Release(WB1),WB1:=0
   COM_Release(WB2),WB2:=0
}
MsgBox % WB1Name
MsgBox % WB2Name
;~ you could also test if the required workbooks are open with something like this
If !WB:=COM_Invoke(oExcel,"workbooks.item",WB1Name) ;replace book 1 with the file name without extension
   ExitApp ; or you could use the previously described method to open the required file
;~ we didnt release excelCount because its a string not an object referenc object references need to be be released and set to nothing
COM_Release(WB),WB:=0
If !WB:=COM_Invoke(oExcel,"workbooks.item",WB2Name) ;replace book 1 with the file name without extension
   ExitApp ; or you could use the previously described method to open the required file
COM_Release(WB),WB:=0
;~ write a value to a cell
COM_Invoke(oExcel,"workbooks.item[" WB1Name "].worksheets.item[1].cells.item[2].item[1].value","first workbook b1")
;~ Read and save value to an ahk variable
readValue:=COM_Invoke(oExcel,"workbooks.item[" WB1Name "].worksheets.item[1].cells.item[2].item[1].value")
;~ Write that value to another workbook
COM_Invoke(oExcel,"workbooks.item[" WB2Name "].worksheets.item[1].cells.item[2].item[1].value",readValue)
;~ you can optionaly use the index of the workbook they will be in the order they were opened
COM_Invoke(oExcel,"workbooks.item[2].worksheets.item[1].cells.item[2].item[2].value","This is book 2 tho")
MsgBox click ok to close excel
exits:
COM_Error(0)
COM_Invoke(oExcel,"quit")
COM_CoUninitialize()
ExitApp

COM_CoInitialize()
OnExit,exits
if !oExcel := COM_GetActiveObject("Excel.Application")
{
   oExcel := COM_ActiveXObject("Excel.Application")
   COM_Invoke(oExcel, "Visible=", true)
;~    you could use either of these methods
;~    COM_Invoke(oExcel,"workbooks.open","Replace with path here")
;~    COM_Invoke(oExcel,"workbooks.add")
}
COM_Invoke(oExcel, "Visible=", true)
excelCount:=COM_Invoke(oExcel,"workbooks.count")
MsgBox   % " count of open workbooks " excelCount
If   excelCount!=2
{
   WB1:=COM_Invoke(oExcel,"workbooks.add")
;~    get the name of the worksheet
   WB1Name:=COM_Invoke(WB1,"Name")
   WB2:=COM_Invoke(oExcel,"workbooks.add")
;~    WB2:=COM_Invoke(oExcel,"workbooks.open","Replace with path here")
;~    get the name of the worksheet
   WB2Name:=COM_Invoke(WB2,"Name")
   COM_Release(WB1),WB1:=0
   COM_Release(WB2),WB2:=0
}
MsgBox % WB1Name
MsgBox % WB2Name
;~ you could also test if the required workbooks are open with something like this
If !WB:=COM_Invoke(oExcel,"workbooks.item",WB1Name) ;replace book 1 with the file name without extension
   ExitApp ; or you could use the previously described method to open the required file
;~ we didnt release excelCount because its a string not an object referenc object references need to be be released and set to nothing
COM_Release(WB),WB:=0
If !WB:=COM_Invoke(oExcel,"workbooks.item",WB2Name) ;replace book 1 with the file name without extension
   ExitApp ; or you could use the previously described method to open the required file
COM_Release(WB),WB:=0
;~ write a value to a cell
COM_Invoke(oExcel,"workbooks.item[" WB1Name "].worksheets.item[1].cells.item[2].item[1].value","first workbook b1")
;~ Read and save value to an ahk variable
readValue:=COM_Invoke(oExcel,"workbooks.item[" WB1Name "].worksheets.item[1].cells.item[2].item[1].value")
;~ Write that value to another workbook
COM_Invoke(oExcel,"workbooks.item[" WB2Name "].worksheets.item[1].cells.item[2].item[1].value",readValue)
;~ you can optionaly use the index of the workbook they will be in the order they were opened
COM_Invoke(oExcel,"workbooks.item[2].worksheets.item[1].cells.item[2].item[2].value","This is book 2 tho")
MsgBox click ok to close excel
exits:
COM_Error(0)
COM_Invoke(oExcel,"quit")
COM_CoUninitialize()
ExitApp

   file:=A_ScriptDir . "\in" ; or \in.xls
   WB2:=COM_Invoke(oExcel,"workbooks.open",file)

COM_Invoke(oExcel,"workbooks.item[" WB2Name "].worksheets.item[1].cells.item[2].item[1].value",readValue)

 file:=A_ScriptDir . "\in" ; or \in.xls

file:=A_ScriptDir . "\in.xls"

COM_Invoke(oExcel,"workbooks.item[" WB2Name "].worksheets.item[1].cells.item[2].item[1].value",readValue)

COM_Invoke(oExcel,"workbooks.item[" WB2Name "].worksheets.item[1].cells.item[2].item[1].value",readValue)

COM_Invoke(WB2,"worksheets.item[1].cells.item[2].item[1].value",readValue)