VBA – How to programmatically enable access to the VBA object model using macros

    Under certain circumstances a programmer may need to access the VB object model for an Office application and interact with its VBIDE interface (clean-up missing references, back-up macro code … etc).     As described in http://support.microsoft.com/kb/282830, the code may fail with the following errors:   http://support.microsoft.com/kb/282830 Programmatic access to Office VBA project is denied ——————————————————————- From Visual Basic or…

22

VBA – Access 2013 – Programmatically assigning a Form’s (DAO RecordSet) Record Source to the result of Stored Procedure (ADO RecordSet) executed on a remote SQL Server triggers “Run time error 31 – Data provider could not be initialized” error message.

Suppose we have this scenario where a local Access database uses Forms bound to SQL serverdata sources: –  a company has developed a front-end application based on MS Access 2007 and VBA, which has been   distributed to end-users as an ADP file; –  its back-end data source runs on SQL Server; –  on the client side,…

6

VSTO & VBA – How to troubleshoot Excel memory and performance problems caused by inefficient code loops

When working with Excel, a developer sometimes has to search, read or write some cell values and these operations are usually done inside repetitive loop structures. Everything seems to work fast enough for small input files, but once we scale up the design, things can complicate and the end-user experience is severely affected. On the other hand, the developer takes…

5

Deploying a VSTO Word 2007 add-in to All Users (Visual Studio 2008 SP1)

_____________________________________________ !!!! [PERFORM THIS STEP FIRST] !!!!      To be able to deploy an Add-in to all users (without manually installing for each user account on a machine) you must download and install http://support.microsoft.com/kb/976811 (A 2007 Office system application does not load an add-in that is developed by using VSTO); To enable the hotfix package,…

3

MSI – Generate SHA2 certificate with OpenSSL and sign a MSI installer project, use ‘Application Control Policies’-‘AppLocker’ to block unsigned code

Some time ago I had to troubleshoot an issue with signed native C++ XLL add-ins for Excel, which didn’t get loaded with certain combinations of Excel and Windows. Symptom – A client has developed an unmanaged XLL add-in for Excel, which has been signed with a code signing certificate that uses SHA2 as hashing algorithm.- Everything…

3

VSTO – How to build a setup project which targets x64 bit OS, and add custom Registry Launch Condition that checks if a key is found in the native 64bit registry hive.

A developer that needs to learn all about building and deploying a VSTO 2010 installer project, should carefully review this very nice article: http://msdn.microsoft.com/en-us/library/ff937654.aspx (Deploying a Visual Studio 2010 Tools for Office Solution Using Windows Installer). Starting from the 2010 version, Office applications come in two flavors: x32 bit and x64 bit. If you need to take advantage of the x64 bit…

2

How COM works & How to build a COM visible DLL in C#.Net, call it from VBA and select the proper ClassInterface (AutoDispatch, AutoDual) [part1/2]

  This article is split across two blog posts and this is part #1.. use this link (please be patient :), I am still working on part 2) to go to part #2. When a beginner programmer needs to create a COM visible DLL which will be called from VBA, PowerShell, VBScript or .NET applications, he can find lots of tutorials on how to…

2

VBA – How to Find and Edit the Office 2003 legacy CommandBars and menus in Office 2010 (add, remove, edit controls in ‘Menu Commands’ group from Add-Ins tab)

Today I am going to show you how to work with legacy CommandBar controls in Office 2010. Let’s suppose you are in the following scenario: –  you have created a DOT template in Office 2003;-  a couple of macros have been added to the VB project; –  and you also found it convenient to insert a custom menu item to allow end-users / colleagues to easily…

2

VSTO – Deploying a VSTO Word 2003 add-in to All Users using a CustomAction Installer override project for assigning CASPOL permissions

  Deploying a VSTO 2003 add-in is a little more complicated than the 2007 and 2010 versions. This is because VSTO 2003 solutions require explicit code execution permissions to be assigned using the CASPOL tool, otherwise they will fail to load. First we need to build a very simple Word 2003 add-in project.     1.  Open…

1