XLTest 1.55 released

I have updated my spreadsheet auditing addin.
Changes in version 1.55
1.    On startup, it checks and fixes autonumber seed problems in database
2.    Processing limit of 1 million cells made an option
3.    ExportActiveProject procedure runnable by VBA automation
4.    VBA Sort Procedures option
5.    Option to list all sheet statistics separately when scanning files
6.    Demo / 30-day evaluation version made available
7.    Utility option to display current autofilter criteria
8.    Numerous usability tweaks


In order to make a time limited trial available I am now protecting it using the ‘Unviewable VBA’ app from Petros Chatzipantazis:


He also markets a Ribbon Commander addin intended to make Ribbon customisation easier than XML.

Posted in Uncategorized | Tagged | Leave a comment

Excel VBA Copy Range, Paste as Table to Powerpoint 2010/2013

So, I’ve spent the last couple of weeks trying to get that one right.

Piecing together all the hints from stackoverflow and the rest, I find that I have to use the ExecuteMso method available in PP2010 and later, and use Doevents to give PP time to act in order to avoid raising errors.

In this code, mPPAppObject is the Powerpoint Application object accessed from Excel VBA eg Set mPPAppObject = GetObject(, "PowerPoint.Application")

Assuming we start with rng.Copy then if the paste is to be a table then

for i=1 to 500:Doevents :next ' 5 hundred, about 10ms timing

‘Otherwise get a Clipboard Error -2147188160 Shapes (unknown member) : Invalid request.  Clipboard is empty or contains data which may not be pasted here.

mPPAppObject.CommandBars.ExecuteMso "PasteExcelTableSourceFormatting" ' 2010+


for i=1 to 5000:Doevents :next ' 5 thousand, about 100ms
‘Otherwise calling code testing the slide.shapes collection does not see the pasted shape yet, or it does not see that the shape has a table (oShape.HasTable)

Set oShape = mPPSlide.Shapes(mPPSlide.Shapes.Count)

If we want some other paste format, then it’s a bit easier:

Set oShape = mPPSlide.Shapes.PasteSpecial(PasteDataType)

‘Where PasteDataType can be

‘ 0 ppPasteDefault ‘ truncates to correspond to visible on worksheet window
‘ 2 ppPasteEnhancedMetafile pastes correctly with full width
‘ 3 ppPasteMetafilePicture ‘ same as default, *truncated if too wide*

‘ 7 ppPasteText
‘ 8 ppPasteHTML ‘ does not work, use mPPAppObject.ActiveWindow.View.Paste

In PP 2013 I sometimes found that more than one shape would be pasted, so:

' oShape.Count property does not exist for correctly pasted shape. So use errtrapped function
If ObjectCount(oShape) > 1 Then ' handle pp 2013 bug
'Debug.Print ">1 shape pasted: "; TypeName(oShape); oShape.Count ' ShapeRange 2
Set oShape = oShape(1)
End If

Function ObjectCount(obj As Object) As Long
If obj Is Nothing Then
ObjectCount = 0
ObjectCount = 1
On Error Resume Next
ObjectCount = obj.Count
'could do Err.Clear here if you like
End If
End Function

What a kludge to get something done that should be easily accessible from the COM model.

Posted in Uncategorized | Tagged , , , , | 18 Comments

ADODB Connection string for LocalDB and SQL Server Native Client

The limit on Access MDB file size is 2GB. To get around that I wanted to try out the lightweight LocalDB server, rather than SQLExpress.
SqlLocalDB.msi is 38MB
sqlncli.msi is 3MB for win32, 5MB for win64

It took a while to get the right connection string, so to save others the wasted time, here’s what I found:

SQL Server 2014 Express LocalDB
says under “Connecting to the Automatic Instance”:

The easiest way to use LocalDB is to connect to the automatic instance owned by the current user by using the connection string “Server=(localdb)\MSSQLLocalDB;Integrated Security=true”. To connect to a specific database by using the file name, connect using a connection string similar to “Server=(LocalDB)\MSSQLLocalDB; Integrated Security=true ;AttachDbFileName=D:\Data\MyDB1.mdf”.
The first time a user on a computer tries to connect to LocalDB, the automatic instance must be both created and started. The extra time for the instance to be created can cause the connection attempt to fail with a timeout message. When this happens, wait a few seconds to let the creation process complete, and then connect again.

The string uses “Integrated Security=true;”, but as you can see below that failed for me.

You will see other results in google using
and you can check the version at a command prompt;
C:\> sqllocaldb v
Microsoft SQL Server 2014 (12.0.2000.8)

but MS have now gone to a version independent name
This is not yet mentioned on

As I am connecting from Excel using ADODB, my connection string will also need a Provider, which is not mentioned in the MSDN article above.

I use the SQL Server Native Client :
you can download SQLNCLI11 directly from

I initially used “Provider=SQLNCLI;…” but got an error 3706
Provider cannot be found. It may not be properly installed.
The windows script I posted earlier gives a list of what providers are already installed on your PC.
so I find I need to use the version number in this case:
If you want to check if a specific ADO provider is installed, check it in registry at path HKEY_CLASSES_ROOT\[Provider_Name] which gives you its CLSID.

Another potential source of confusion is the use of equivalent names in the connection string
eg Trusted_Connection=true is ODBC and Integrated Security=SSPI is OLEDB.

gives a handy table showing that for example, these are the same:

    | Value                | Synonym                 |
    | extended properties  | attachdbfilename        |
    | timeout              | connect timeout         |
    | server               | data source             |
    | database             | initial catalog         |
    | trusted_connection   | integrated security     |

Error messages

Here I used the wrong version, 11 instead of 12:
“Provider=SQLNCLI11;Server=(localdb)\v11.0;Integrated Security=SSPI;”
-2147467259 SQL Server Network Interfaces: Cannot create an automatic instance.

This is correct; if you get a timeout error, simply retry:
“Provider=SQLNCLI11;Server=(localdb)\v12.0;Integrated Security=SSPI;”
-2147467259 Unable to complete login process due to delay in opening server connection
I got a timeout error on my PC after 20 seconds so now add “timeout=30;” to the string
I imagine that time might improve with Windows prefetch.

This is the version-independent string:
“Provider=SQLNCLI11;Server=(localdb)\MSSQLLocalDB;Integrated Security=SSPI;”

The MS article uses “Integrated Security=true;”, but that failed for me:
“Provider=SQLNCLI11;Server=(localdb)\MSSQLLocalDB;Integrated Security=true;”
-2147217887 Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

confirms that it should be “Trusted_Connection=yes;”

This is what I finally used:
I am posting this here to provide a google hit for that string, in the hope this helps someone.


Posted in Excel/VBA | Tagged , , , , , , , | Leave a comment

VBScript to list installed OLEDB Providers

I found that the script I got by googling for that title failed on the line:
Error: ActiveX component can’t create object: ‘RegEdit.Server’

So here’s my own VBScript :

'List of installed OLEDB providers on local computer
Option Explicit
Const HKEY_CLASSES_ROOT = &H80000000

Dim OutText, Key, strComputer, objRegistry, arrKeys
Dim strKeyPath, strValueName, strValue, uValue

strComputer = "."

Set objRegistry = GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv")

objRegistry.enumKey HKEY_CLASSES_ROOT, "CLSID", arrKeys
Wscript.Echo "Number of Keys to be searched: " & 1+ UBound(arrKeys) & vbCrLf & "Click OK and wait a minute" ' 7645

for each key in arrKeys
strKeyPath = "CLSID\" & key
strValueName = "OLEDB_SERVICES"
if objRegistry.GetDWordValue (HKEY_CLASSES_ROOT,strKeyPath,strValueName,uValue)=0 then ' that value name exists
'get the (Default) value which is the name of the provider
objRegistry.GetStringValue HKEY_CLASSES_ROOT,strKeyPath,"",strValue
OutText=OutText & strValue & vbcrlf
' and the expanded description
objRegistry.GetStringValue HKEY_CLASSES_ROOT,strKeyPath & "\OLE DB Provider","",strValue
OutText=OutText & " " & strValue & vbcrlf
end if
Wscript.Echo OutText

'Windows Script Host
' Microsoft OLE DB Provider for SQL Server
' Microsoft Office 12.0 Access Database Engine OLE DB Provider
' Microsoft Office 15.0 Access Database Engine OLE DB Provider
' SQL Server Native Client 10.0

Posted in Productivity | Tagged , , , , , | 2 Comments

Dumb email bounces

A spammer is sending mail to Polish addresses faking an address from one of the domains I administer.
Nothing unusual in that. What’s more annoying is all the recipient postmasters replying to the APPARENT from-address, ie my address, saying the mail is spam.
At least one of them is smart enough to acknowledge :

The message carried your return address, so it was either a genuine mail
from you, or a sender address was faked and your e-mail address abused
by third party, in which case we apologize for undesired notification.

What’s more, some will even provide a trace to the sender IP address, often in .cn, .kz, .ir, etc.
But if they know that why spam me with bounces? Really, guys, think a bit further.

Posted in Uncategorized | Tagged , , , , , | Leave a comment

Irish Computer Society evening talk on Spreadsheet Controls

My talk on “Spreadsheet Management and Controls” is next month at the ICS.
Monday 18:00 – 19:30 9th Jun

This session outlines current concerns about risks from the uncontrolled use of spreadsheet-based information systems.
The session will cover how to assess risk in the context of spreadsheet criticality, and a process of high level and detailed reviews. Good practices are presented for error detection, correction, and prevention.

ICS further info and booking

Free to ICS members, 25 euro otherwise.

Posted in Excel/VBA, Risk, Software Quality, Software Testing | Tagged , , , | Leave a comment

The Art of the Cell

I liked this post on the Excel-L list today.

On 07/05/2014 10:21, Laurent Wright wrote:
Reminds me of a question, many years ago, that I got asked.
In Layman’s terms, what is the difference between Access and Excel?
My reply was Access is Meccano and Excel is Lego
With Meccano you are stuck into building rigid and 'structured Models', 
with Lego, you can pretty much build what you like.

On 07/05/2014 10:34, John Nurick wrote:

The other relevant difference is that once you’ve built Meccano it stays built until someone comes along with the skill to use a screwdriver and spanner and, hopefully, the insight to put it back together better. But a Lego model can be spoilt by any passing toddler or puppy.

Posted in Uncategorized | Tagged , , | Leave a comment