This is an older article I wrote. The underlying code relies on SQL-DMO which is installed with SQL Server 2000 Client Tools. I suspect you may not have SQL-DMO installed on the machine from which you are running. You can test by attempting to create a DMO object. Save the following text as a vbs file:

Set oServer = CreateObject("SQLDMO.SQLServer")

Next execute cscript.exe c:\pathtoyourfile.vbs

If no errors you have DMO installed. One other thought -- Excel macro behavior has changed in Excel 2007, be sure you're able to execute macros.