Month: December 2013

Share the Clipboard with VBA and the Windows API

Accessing the clipboard in Windows becomes quite tricky in VBA, particularly if you have to stay 32 and 64 bits compatible.

Here’s my compilation of portable routines, inspired by MSDN, Internet scraping and good sense.

First the Declares section (I know, it pours out of the frame on the right, just select and copy the text):

Then the VBA code:

Update: just a quick sample (Access VBA):

There’s an Access 2016 demo database (on devinfo.net), open it, go to the VBA editor (ALT+F11), go to the debug Windows (CTRL+G), type “Test” (without the quotes of course) and hit ENTER; any text in the clipboard will be displayed in the debug window.

Enjoy.

Microsoft Access Databases ADO connection strings for 32/64 bits

If you try to open a connection to an Access database via ADO, you’ll have to use a different provider, whether you’re coding for 32 or 64 bits environments.

Here’s a utility function I use for that (updated 25.02.2014):

Of course you have to have the correct jet engine files installed on the target computer, but just a warning here: you’ll find quite a few links to download the Access 2010 database engine redistributable package, but if you’re using Office 2013 and you install this redistributable package, as soon as you’ll start an Office application, the repair feature of the Office installer will trigger.

You can find the GetFileExt() function in this post.

Compiling the zlib compression library with Visual Studio 2013

I you need zip compression in your development project, the zlib library is a good and free solution.
There’s however a bit of googling necessary to achieve a successful compilation with VS2013, especially for generating the 32 bits version of the library.
As I just needed it for some VB/VBA integration, I had to sort out a lot of outdated and not really useful information, for a simple solution, after all.
So here’s my receipe which will help you save some time, I hope, if you’re on the same track.

Update #2, 11/12/2013, here are my compiled binaries, zlibwapi32.dll and zlib64.dll in this zip archive, MD5: eb8932beb0360b42c6cdd1ccd79745ad

Download the library source code here: http://www.zlib.net, then:

  1. Uncompress the archive (in my case zlib-1.2.8.zip), you’ll find a zlibvc.sln file in the contribvstudiovc11 subfolder, that you can open with vs2013 (let it do its conversion stuff);
  2. The annoying part comes if you’re compiling against a Win32 target, because you then have to specify an additional linker command line switch /SAFESEH:NO. Right-click the zlibvc project and open its property pages:
  3. vs2013compile
  4. Do the same for the testzlib project
  5. Give it a go.

For the Win64 version, the linker switch does not apply, just go directly to the build process.

Note that you’ll end up with a zlibwapi.dll file that you can use with your vb/vba projects.
I did compile 32 and 64 bits versions of the library, and I renamed them zlibwapi32.dll and zlibwapi64.dll to avoid confusion; here are the declares for the 32 bits version:
(Updated 11/12/2013)

It’s then quite easy to figure out how to use the functions in your C/C++/VB/Whatever projects, samples are included with the library, but for VB/VBA, here is an excellent introduction from Mike D Sutton: http://edais.mvps.org/Tutorials/ZLib/index.html .
Happy zipping!