Summary
Using VBA code in a 64bit version of office where the OTA or SA api is used results in the error: ActiveX component can't create object
Error
ActiveX component can't create object
Cause
An attempt to instantiate a 32bit COM object in a 64bit version of MS Office was attempted.
Fix
Use a 32bit version of MS Office.
This because it is impossible to instantiate 32bit COM objects in 64bit Office.
Microsoft documents the limitation here...
Choose between the 64-bit or 32-bit version of Office
Specifically from the link above...
You have 32-bit COM Add-ins with no 64-bit alternative. You can continue to run 32-bit COM add-ins in 32-bit Office on 64-bit Windows. You can also try contacting the COM Add-in vendor and requesting a 64-bit version.
Note: there are no plans to compile the ALM Client or ALM SA Client as 64bit