Skip to main content

Adjusting our Word VBA Macros for Office 2010 (64 bit)


We've had it easy these last few years. You could just upgrade Microsoft Office and not have to worry about file compatibility or about your macros not working.  I was just beginning to relax.


Unfortunately Microsoft never relaxes and the latest "innovations" extend past the annoying ribbon interface (I've been using it for over a year and I still consider it annoying) to clobber both file formats and macros.

The File Format Problem
The file format problem is easy to fix. First of all, I've changed our defaults to save in Office 97-2003 format.   There's no sense in our company posting files for public consumption in a format that they can't read. In case you're wondering where to change this, click File, then Options and click on SAVE.


If your fleet is still using Office 2003, you can download a compatibility pack which will enable it to read the new office formats.

http://www.microsoft.com/download/en/details.aspx?id=3


64 Bit API Declarations
This next bit is a little frustrating.  We rely on INI files in our Macros as they're much simpler than the registry and we can easily get our Notes/Domino apps to write updates into the files. It's all good for automation.  Unfortunately the 32 bit API calls won't work any more.

To get around the problem, replace this;


Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationname As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long

Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationname As String, ByVal lpKeyName As String, ByVal lsString As String, ByVal lplFilename As String) As Long


with this


Declare PtrSafe Function WriteProfileString Lib "kernel32" Alias "WriteProfileStringA" (ByVal lpszSection As String, ByVal lpszKeyName As String, ByVal lpszString As String) As Long

Declare PtrSafe Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long

The remainder of your INI file reading routine should run without changes.


Unavailable Objects and those Nasty String Function Errors
This one is a nasty one.  Sometimes you'll try to open a macro that contains an unusual object, such as a calendar control.  Word 2003 displays a dialog box which says;  "Could not load an object because it is not available on this machine".  This was helpful.  

Unfortunately, Word 2010 doesn't mention the offending object but instead assumes that all internal string functions, such as LCASE$, UCASE$, LEFT$, RIGHT$, MIDS$ and TRIM$ are external functions in the missing component.



It highlights the first string function and says "Can't find project or library" which promptly sets you off on a wild goose chase.

To fix this problem, 
  1. Stop your macro
  2. Click Tools, then References (if this is greyed out, you didn't stop the macro).
  3. Find the problem Reference in the list and deselect it 
  4. Click Ok.

You'll also have to replace your missing element with a similar control, such as a text box and adjust any code which refers to it.

After that though, you should have a working, if a little disabled, macro.  Now I just need to find a 64 bit calendar control.

Comments

Anonymous said…
You missed "As Long" -> "As LongPTR"

lee.strachov@cba.com.au
Anonymous said…
You for got the ptrlong part.
Lee.strachov@cba.com.au

Popular posts from this blog

How to Change Your Notification Options for New Lotus Notes Mail in version 8.x

Don't worry, I'm not patronizing you (my readers), I just decided to re-document this for one of our internal users and thought you might want to be able to use it in your own user documentation. WHAT IS THIS DOCUMENT ABOUT? Some people who don't get a lot of mail, like to be notified when such an event occurs. Notification can be; via a sound via a pop-up box via the system tray (where the computer clock is) The pop up box looks like this; Other people, who like myself, get too much mail would rather not be notified. The aim of this document is to tell you how (and where) to turn these options on and off. CHANGING YOUR SETTINGS To change your settings from the Notes 8.x client; On the Menu, click File , then Preferences... On the left hand side , click on the little plus sign to the left of Mail to expand the options. Click on the option marked Sending and Receiving . In the middle section, under receiving, you can control your notifications. If you untick the box mark

How to Create an Auto-Response Mail Message in Lotus Notes 8.5.3+

Why would you do this? Suppose that you have an externally accessible generic email address for your company; support@mycompany.com or info@mycompany.com. You might expose this to the web and allow people to send messages to you. Setting up an auto-response email will tell the senders that their message reached its destination and that it will be dealt with accordingly.  It's also good practice to include links to FAQs or other useful information. Why 8.5.3 The techniques we'll be using here work in older versions of Notes but some of the options seem to have moved around in 8.5.3.  I figured it was a good time to show you where they've moved to. The Procedure Start Domino Designer and open the Mail file to be modified.  A really quick way to do this is to right-click on the application tab and choose "Open in Designer". In the Left hand panel of designer, expand Code and then double-click Agents.  A new window should appear. Click the action

How to Do a Mail Merge to Email using Lotus Notes

Why do one? In today's "green" world, it makes much better sense to send out emails than letters but you still want to personalize them. Sadly, by itself Lotus Notes doesn't support mail merge to email. Of course, we know that outlook does (but then it lets anyone and anything send emails for you - even when you don't want them to). So, how to do it in Notes? OpenNTF The first port of call is OpenNTF ( http://www.openntf.org/ ). This place is full of great things but most of them are really badly documented. Still, these guys give things away for free and they develop in their spare time, so we should be grateful for what we get. There's a great little project there called MailMerge Excel to Notes . Go there, click on releases and download the ZIP file. Getting to the Code The installation is tricky though I've noted that since I asked the author about the install, it's been updated (so maybe these steps are less necessary). Unzip the files to somewher