Help to Build Excel Function through COM

Get help with using AutoHotkey and its commands and hotkeys
wilcoxaj
Posts: 4
Joined: 15 Apr 2020, 10:31

Help to Build Excel Function through COM

15 Apr 2020, 10:57

Curious if anyone with COM/VBA skills would be available to help build an AHK script for me?

I have the VBA code for it already written out, so it would essentially need to be translated to COM. It's about 300 lines of VBA.

It's essentially a bulk creator of calculated columns for Excel pivot tables.

If you can do it, or know of someone who can, I'd love to chat over email. Let me know your hourly rates and all that.

Thanks!
AJ
debug_print
Posts: 4
Joined: 12 Apr 2020, 22:17

Re: Help to Build Excel Function through COM

16 Apr 2020, 01:03

I'm not much of a programmer, more of a cutter, paster, meddler but I have been calling Excel VBA subs for years with code that some kind soul posted on the forums Jethrow I think.

example vba code I use to backup

Code: Select all

Private Sub BackMeUp()

'ctrl + z
    Dim backupfolder As String
    Dim sDate As String
    On Error Resume Next
    mdlTestPrivate.GetEnv
    'sDate = Format(Now(), "dd-MM-yyyy-hh:mm:ss")
    
    sDate = Format(DateTime.Now, "dd-MM-yyyy-hh-mm-ss-")

    backupfolder = mMyEnv & "Excel STUFF\Backup\"
'    backupfolder = "F:\ExcelSTUFF\backup\"
'    backupfolder = "E:\ExcelSTUFF\backup\"
    ThisWorkbook.SaveCopyAs Filename:=backupfolder & sDate & ThisWorkbook.Name
      If Err.Number = 1004 Then
        MsgBox "Backup folder " & backupfolder & "not found"
        Application.ScreenUpdating = True
        Exit Sub
    End If

End Sub

then somewhere in your ahk script put this

Code: Select all

; Excel_Get by jethrow (modified)
; Forum:    https://autohotkey.com/boards/viewtopic.php?f=6&t=31840
; Github:   https://github.com/ahkon/MS-Office-COM-Basics/blob/master/Examples/Excel/Excel_Get.ahk
Excel_Get(WinTitle:="ahk_class XLMAIN", Excel7#:=1) {
    static h := DllCall("LoadLibrary", "Str", "oleacc", "Ptr")
    WinGetClass, WinClass, %WinTitle%
    if !(WinClass == "XLMAIN")
        return "Window class mismatch."
    ControlGet, hwnd, hwnd,, Excel7%Excel7#%, %WinTitle%
    if (ErrorLevel)
        return "Error accessing the control hWnd."
    VarSetCapacity(IID_IDispatch, 16)
    NumPut(0x46000000000000C0, NumPut(0x0000000000020400, IID_IDispatch, "Int64"), "Int64")
    if DllCall("oleacc\AccessibleObjectFromWindow", "Ptr", hWnd, "UInt", -16, "Ptr", &IID_IDispatch, "Ptr*", pacc) != 0
        return "Error calling AccessibleObjectFromWindow."
    window := ComObject(9, pacc, 1)
    if ComObjType(window) != 9
        return "Error wrapping the window object."
    Loop
        try return window.Application
        catch e
            if SubStr(e.message, 1, 10) = "0x80010001"
                ControlSend, Excel7%Excel7#%, {Esc}, %WinTitle%
            else
                return "Error accessing the application object."
}

; References
;   https://autohotkey.com/board/topic/88337-ahk-failure-with-excel-get/?p=560328
;   https://autohotkey.com/board/topic/76162-excel-com-errors/?p=484371
;   https://autohotkey.com/boards/viewtopic.php?p=134048#p134048


then call the function something like this my spreadsheet is macro enabled and excel 2007 but have had it working on later versions at my previous job

Code: Select all

CBBackup()
{
xlApp := Excel_Get()
Needle := "CB-Plant-Equipment-Inventory.xlsm"
If InStr(xlApp.Caption, Needle)
	{
	WinActivate, % xlApp.Caption
	WinWait, % xlApp.Caption
	; msgbox, % xlApp.Caption
	oExcel := Excel_Get()
	try oExcel.Run("BackMeUp")

	catch e  ; Handles the first error/exception raised by the block above.
	{
		MsgBox, An exception was thrown!`nSpecifically: %e%
		oExcel.ScreenUpdating :=   True
		oExcel := ""
		Exit
	}

	return
	}
Else
	{
    MsgBox, %Needle% not running.
	}
return	
}
and the hotkey combination to call it

Code: Select all

<^#b::
CBBackup()
return
wilcoxaj
Posts: 4
Joined: 15 Apr 2020, 10:31

Re: Help to Build Excel Function through COM

17 Apr 2020, 09:45

Thanks Debug! That's super helpful. I'm with you - that would definitely be the easiest way to pull it off. That being said, this requires that the vba be stored on the user's machine already, and the upside of sending the code through COM is that I can hand this to coworkers without any previous setup necessary, and then worksheets don't give the macro warnings on save and open.

That being said, I REALLY appreciate you sharing this because you made it so easy that even stupid me could pull it off like that. THANKS!
debug_print wrote:
16 Apr 2020, 01:03
I'm not much of a programmer, more of a cutter, paster, meddler but I have been calling Excel VBA subs for years with code that some kind soul posted on the forums Jethrow I think.

example vba code I use to backup
debug_print
Posts: 4
Joined: 12 Apr 2020, 22:17

Re: Help to Build Excel Function through COM

18 Apr 2020, 00:13

No problem, its not perfect , but has worked for me for sometime....I notice that the Subs in VBA can be "Private" and it will still work...so you can hide all the macros from the end user to avoid accidental usage within Excel
User avatar
Jovannb
Posts: 256
Joined: 17 Jun 2014, 02:44
Location: Austria

Re: Help to Build Excel Function through COM

18 Apr 2020, 01:07

Hi,

it is possible to "inject" VBA-Code dynamically into Excel as a marco and afterwards to call that macro.

You
  • have your vba-code as textfile
  • import that vba-code-textfile into excel

    Code: Select all

    excel.activeworkbook.vbproject.vbcomponents.import(vba-code-textfile)
    is what you need from COM) by ahk
  • call that macro by

    Code: Select all

    send !{F8}
    by ahk .. there is a COM-Method for calling vba-macros too, but thats not reliable
  • can then delete that vba-macro by COM too

    Code: Select all

    excel.activeworkbook.vbproject.vbcomponents.remove(.. your macro..)
regards

J.B.
AHK: 1.1.33.01 Ansi, 32-Bit; Win10 (2004, 19041.685) 64 bit, german
wilcoxaj
Posts: 4
Joined: 15 Apr 2020, 10:31

Re: Help to Build Excel Function through COM

19 Apr 2020, 10:32

Great point, debug! Thanks!
debug_print wrote:
18 Apr 2020, 00:13
No problem, its not perfect , but has worked for me for sometime....I notice that the Subs in VBA can be "Private" and it will still work...so you can hide all the macros from the end user to avoid accidental usage within Excel
wilcoxaj
Posts: 4
Joined: 15 Apr 2020, 10:31

Re: Help to Build Excel Function through COM

19 Apr 2020, 10:35

Thanks Jovannb! I think that's a great potential solution - thanks for offering it up! The downside here is that I'd have to have a coworker enable some settings ahead of time before they could use it, but ultimately a pretty solid solution.

I do really like the idea of translating the VBA directly to COM just so it would work regardless of settings, but I think this is brilliant nonetheless. Thanks for sharing!
Jovannb wrote:
18 Apr 2020, 01:07
Hi,

it is possible to "inject" VBA-Code dynamically into Excel as a marco and afterwards to call that macro.

You
  • have your vba-code as textfile
  • import that vba-code-textfile into excel

    Code: Select all

    excel.activeworkbook.vbproject.vbcomponents.import(vba-code-textfile)
    is what you need from COM) by ahk
  • call that macro by

    Code: Select all

    send !{F8}
    by ahk .. there is a COM-Method for calling vba-macros too, but thats not reliable
  • can then delete that vba-macro by COM too

    Code: Select all

    excel.activeworkbook.vbproject.vbcomponents.remove(.. your macro..)
regards

J.B.

Return to “Ask For Help”

Who is online

Users browsing this forum: Bing [Bot], mikeyww and 39 guests