Help to Build Excel Function through COM

[wilcoxaj]

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]

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]

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!

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]

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

[Jovannb]

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.

[wilcoxaj]

Great point, debug! Thanks!

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]

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!

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.