Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

Basic Ahk_L COM Tutorial for Excel


  • Please log in to reply
227 replies to this topic
plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011
Sweet, so this is what I had in mind.

Right now, I have a hotkey where it copies highlighted text into the clipboard and pastes it into an excel sheet.

What I want to do is copy Spanish and English contextual definitions from Word Reference using a hotkey, which will paste them appropriately into the Excel sheet.

Since Word Reference's only reliable delimiter is bold and non-bold text, I use a VBA code to split into two cells the content from column A based on bold and non-bold text.

This is the code to highlight the text and place the clipboard items into an excel sheet:

^s::
Clipboard =
Sleep 200
Send ^c 
Sleep 200
Clipwait = 3
Sleep 200
IfWinNotExist Microsoft Excel - Unknown4
{
Run "C:\Documents and Settings\censored\My Documents\Dropbox\Spanish\Spreadsheets\Unknown4.xlsx"
Sleep 500
ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN
window := Acc_ObjectFromWindow(hwnd, -16)
xlBook := window.parent
xlApp := window.application
xlSheet := window.activesheet
}
Sleep 200
IfWinNotActive Microsoft Excel - Unknown4
    WinActivate Microsoft Excel - Unknown4
Loop
{
    if (xlSheet.Range("A" . A_Index).Value != "")
        continue
    else
        Send %Clipboard%{ENTER}
    Sleep 200
        xlBook.Save()
        break
    }
Return

This is the code to split based on bold and non-bold text:

Option Explicit
Sub SplitBold()
' stanleydgromjr, 11/21/2010, EF754022
Dim c As Range, a As Long, BS As String, NS As String
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  BS = "": NS = ""
  For a = 1 To Len(Trim(c)) Step 1
    If c.Characters(a, 1).Text = " " Then
      BS = BS & c.Characters(a, 1).Text
      NS = NS & c.Characters(a, 1).Text
    ElseIf c.Characters(a, 1).Font.FontStyle = "Bold" Then
      BS = BS & c.Characters(a, 1).Text
    ElseIf c.Characters(a, 1).Font.FontStyle <> "Bold" Then
      NS = NS & c.Characters(a, 1).Text
    End If
  Next a
  c.Offset(, 1) = Trim(BS)
  NS = Trim(NS)
  If Left(NS, 1) = Chr(150) Then NS = Right(NS, Len(NS) - 1)
  If Left(NS, 1) = Chr(45) Then NS = Right(NS, Len(NS) - 1)
  c.Offset(, 2) = Trim(NS)
Next c
Application.ScreenUpdating = True
End Sub

How could we reuse that VBA code within our AutoHotkey code, I was a little confused about that.[/code]

tuna
  • Members
  • 158 posts
  • Last active: Dec 28 2012 04:50 PM
  • Joined: 03 Oct 2007
Awesome stuff, Mickers. Any way to hook onto events of a specific instance of a COM object? For example, I might want to respond to a Worksheet_Change event.

jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009

#Persistent
XL := ComObjCreate("Excel.Application")
XL.WorkBooks.Add
XL.Visible := true
Sheet := XL.ActiveSheet
ComObjConnect(Sheet, Worksheet_Events)


class Worksheet_Events {
   Change(cell, sheet) {
      MsgBox, % "Changed cell " RegExReplace(cell.address,"\$") " to: " cell.value
   }
}


tuna
  • Members
  • 158 posts
  • Last active: Dec 28 2012 04:50 PM
  • Joined: 03 Oct 2007
Excellent. Thanks.

Alpha Bravo
  • Members
  • 1687 posts
  • Last active: Nov 07 2015 03:06 PM
  • Joined: 01 Sep 2011
I can't seem to get "saveas" to save as CSV, any help?
I tried:
Xl.ActiveWorkbook.Saveas(filename, xlcsv)
Workbook.SaveAs Method

Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
Xl.ActiveWorkbook.Saveas(filename, [color=red]"xlcsv"[/color])
Or
Xl.ActiveWorkbook.Saveas(filename, 6)
:?:

jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009
xlCSV := 6


Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010

xlCSV := 6

Derp beat me to it. :p

Alpha Bravo
  • Members
  • 1687 posts
  • Last active: Nov 07 2015 03:06 PM
  • Joined: 01 Sep 2011

Xl.ActiveWorkbook.Saveas(filename, [color=red]"xlcsv"[/color])
Or
Xl.ActiveWorkbook.Saveas(filename, 6)
:?:


Xl.ActiveWorkbook.Saveas(filename, "xlcsv") ; did not work

Xl.ActiveWorkbook.Saveas(filename, 6) ; works like a charm
You guys rock, thanks
Learn something everyday (use VALUE not NAME)

j--hn
  • Members
  • 176 posts
  • Last active: Oct 26 2011 02:42 PM
  • Joined: 16 Apr 2011
Hi guys,

Any hint on how to copy/move worksheet with additional(before/after) parameter? I've tried with this simple code:

Xl := ComObjActive("Excel.Application")

; run fine, no parameter
Xl.ActiveSheet.Copy ; copy active sheet to new workbook (automatically created by excel)
Xl.ActiveSheet.Move ; move active sheet to new workbook (automatically created by excel)

/*
[color=red]; failed to run, ERROR: Call to nonexistent function.[/color]
[color=red]; Spesifically: Sheets(1)[/color]
Xl.ActiveSheet.Move Before:=Sheets(1) ; move active sheet to front
Xl.ActiveSheet.Copy Before:=Sheets(1) ; make a copy of active sheet and move it to the front
*/


Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
Sheets
Move has two parameters: Before and After.
Xl.ActiveSheet.Move("Sheet1", "Sheet3")
:?:

j--hn
  • Members
  • 176 posts
  • Last active: Oct 26 2011 02:42 PM
  • Joined: 16 Apr 2011
Hi Mickers, I got another error:
- on move method : Unable to get the Move property of the Worksheet Class
- on copy method : Unable to get the Copy property of the Worksheet Class
XL := ComObjActive("Excel.Application")
^1::XL.ActiveSheet.Move("Sheet1", "Sheet3")
^2::XL.ActiveSheet.Copy("Sheet1", "Sheet3")
Do we have to specify before & after altogether? I try this on Excel VBA and it work as it should:
ActiveSheet.Move Before:=Sheets(1) ; move active sheet to front
ActiveSheet.Copy Before:=Sheets(1) ; make a copy of active sheet and move it to the front

AHK_Lw 1.1.05, OS: XP SP3, Firefox 3

jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009

expression.Move(Before, After)
Before Optional Variant. The sheet before which the moved sheet will be placed. You cannot specify Before if you specify After.

After Optional Variant. The sheet after which the moved sheet will be placed. You cannot specify After if you specify Before.


Xl.ActiveSheet.Move(Xl.Sheets(1)) ;# move active sheet to front


Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
Yes I realized mine was incorrect. I needed to use a worksheets object where you had a sheet object so the syntax was incompatible.
Btw you don't need the copy method at all for this action.

j--hn
  • Members
  • 176 posts
  • Last active: Oct 26 2011 02:42 PM
  • Joined: 16 Apr 2011
Thanks guys, actually I was trying to sort sheets using VBA but excel was refuse to save the macro on personal macro workbook, so i use AHK instead.

;Sorting all worksheets on active workbook, using Natural Sort
#SingleInstance force
SetBatchLines, -1

Gui, Add, Button,gSortA w180 h50 Center, Ascending`nA -- Z
Gui, Add, Button,gSortD w180 h50 Center, Descending`nZ -- A
Gui, Add, Text, Center, ------------------------------------------------------------
Gui, Add, Button,gCancel w180 h30 Center, Cancel
Gui, -SysMenu +AlwaysOnTop
Gui, Show, Center AutoSize, Sort Sheet
Return

SortA:
Gui, Destroy
Ascending  := TRUE
Goto Begin

SortD:
Gui, Destroy
Goto Begin

Begin:
Progress,, Indexing,, Sort Sheet
Xl := ComObjActive("Excel.Application")

Opt1:=Xl.ScreenUpdating, Opt2:=Xl.EnableEvents, Opt3:=Xl.Calculation, Opt4:=Xl.Cursor ; store default setting
Xl.ScreenUpdating:=False, Xl.EnableEvents:=False, Xl.Calculation:=-4135, Xl.Cursor:=1 ; performance setting

sheetCount := Xl.Sheets.Count, sheetIndex := "", sheetName := ""

   Progress,, Indexing,, Sort Sheet
   Loop, %sheetCount%
   {
   sheetName := Xl.Sheets(A_Index).Name, sheetIndex .= sheetName
   Indexing := (A_Index/sheetCount)*100
   Progress, %Indexing%, Indexing. %A_Index%/%sheetCount%,, Sort Sheet %updateInterval%
   If A_Index = %sheetCount%
      Break
   sheetIndex  .= "/"
   }

   Progress, 100, Checking,, Sort Sheet
      If Ascending
         Sort sheetIndex, F StrCmpLogicalA D/
      Else
         Sort sheetIndex, F StrCmpLogicalD D/
   Sleep, 100
   
   Loop, parse, sheetIndex, /
   {
      targetIndex := Xl.Sheets(A_LoopField).Index
      If targetIndex*1 <> A_Index
      {
      Xl.Sheets(targetIndex).Move(Xl.Sheets(A_Index))
      Sorting := (A_Index/sheetCount)*100
      Progress, %Sorting%, Sorting. %A_Index%/%sheetCount%,, Sort Sheet
      }
   }
   Progress,100, Done. %sheetCount% sheets Sorted,, Sort Sheet

Xl.Cursor:=Opt4, Xl.Calculation:=Opt3, Xl.EnableEvents:=Opt2, Xl.ScreenUpdating:=Opt1 ; restore default setting
Progress, Off

Cancel:
Gui,Destroy
ExitApp

; --------------------------------------------------------------------------------------------------------------------------
; Natural Sort Function by temp01
; http://www.autohotkey.com/forum/topic35835-15.html

StrCmpLogicalA(str1, str2){
   Loop, 2
      MultiByteToWideChar(str%A_Index%)
   return DllCall("ShlWapi\StrCmpLogicalW", "UInt", &str1, "UInt", &str2)
}
StrCmpLogicalD(str2, str1){
   Loop, 2
      MultiByteToWideChar(str%A_Index%)
   return DllCall("ShlWapi\StrCmpLogicalW", "UInt", &str1, "UInt", &str2)
}
MultiByteToWideChar(ByRef str){
   ostr := str, size = 0
   Loop, 2 {
      VarSetCapacity(str, size*2)
      size := DllCall("MultiByteToWideChar", "UInt", 65001, "UInt", 0, "UInt", &ostr, "int", -1, "UInt", &str, "int", size)
   }
}

Anyway, i still can't figure, how to specify optional parameter. Copy, move, and add method of Sheets object has 'before' and 'after' can you give example on how to use 'after' ? Also another example on method with optional parameter would be great. :wink:
AHK_Lw 1.1.05, OS: XP SP3, Firefox 3