Code: Select all
SetWorkingDir %A_ScriptDir% ; Ensures a consistent starting directory.
#SingleInstance, Force
; Created by AHK_User
; Example code to read, delete, run or modify VBA code of an Excel file
VBcode=
(
Sub Anything()
MsgBox("YEAH")
End Sub
)
global oExcel
oExcel := Excel_Get()
; Connect to VBA object
oVBComponent := oExcel.ActiveWorkbook.VBProject.VBComponents.Item("Module1")
; Add VBA code
oVBComponent.CodeModule.InsertLines(1, VBcode)
; Get VBA Content
LinesContent := oVBComponent.CodeModule.Lines(1,oVBComponent.CodeModule.CountOfLines)
; Delete VBA code
;~ cmpComponent.CodeModule.DeleteLines(1,cmpComponent.CodeModule.CountOfLines) ; Clear all the lines
; Example looping over all VBA code
For cmpComponent In oExcel.ActiveWorkbook.VBProject.VBComponents
{
szFileName := cmpComponent.Name
; Import from file
;~ cmpComponent.Import(A_ScriptDir "\tempvbcode.txt")
; Read contents
LinesContent := cmpComponent.CodeModule.Lines(1,cmpComponent.CodeModule.CountOfLines)
MsgBox, % LinesContent
; Clear all the lines
cmpComponent.CodeModule.DeleteLines(1,cmpComponent.CodeModule.CountOfLines)
}
; Changes the name of the project
;~ oExcel.ActiveWorkbook.VBProject.Name := "TestProject"
; Run macro
oExcel.Run("Anything")
return
Excel_Get(WinTitle="ahk_class XLMAIN") { ; by Sean and Jethrow, minor modification by Learning one
ControlGet, hwnd, hwnd, , Excel71, %WinTitle%
if !hwnd
return
Window := Acc_ObjectFromWindow(hwnd, -16)
Loop
try
Application := Window.Application
catch
ControlSend, Excel71, {esc}, %WinTitle%
Until !!Application
return Application
} ; http://www.autohotkey.com/forum/viewtopic.php?p=492448#492448