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.