Manipulate VBA code in Excel

[AHK_user]

Example code how to manipulate VBA code in an Excel file.

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

[burque505]

@AHK_user, thanks very much for this, quite interesting.
On my Win7 64-bit system, Excel 2016, I wasn't able to get the code to run as provided, no doubt due to my Office setup. The following code works for me. I first created a guinea-pig file, 'text.xlsm' in the script directory.

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

Run excel %A_ScriptDir%\test.xlsm

sleep 5000

VBcode=
(
Sub Anything()

   MsgBox("YEAH")

End Sub
)
global oExcel

oExcel := Excel_Get()

; Connect to VBA object
oVBComponent := oExcel.ActiveWorkbook.VBProject.VBComponents.Item("ThisWorkbook")

; Add VBA code 
oVBComponent.CodeModule.InsertLines(1, VBcode)

; Get VBA Content
LinesContent := oVBComponent.CodeModule.Lines(1,oVBComponent.CodeModule.CountOfLines)

numLines := oVBComponent.CodeModule.CountOfLines
msgbox %numLines% lines of code:`n%LinesContent%

; Changes the name of the project
oExcel.ActiveWorkbook.VBProject.Name := "TestProject" 

; Run macro
oExcel.Run("ThisWorkbook.Anything")
; Clear macro
oVBComponent.CodeModule.DeleteLines(1,oVBComponent.CodeModule.CountOfLines)

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

Regards,
burque505