RE: "only valid for installed components"
This suggests that somehow VBA may not be installed with Excel. It's odd, but it happens. Try repairing Office from the windows add/remove programs control panel and changing the options to include VBA support. Even if already included, repairing office may help with the various errors you've reported.
RE: If I open it from it from Excel it opens, with a message about enabling macros, but of course there's no data in the spreadsheet.
Yes, that's how it works. There will be no data until you connect to QuickBooks to pull the data, and then the code will create the W-2 worksheet and later the EFW2 file for your state (or Federal) from the worksheet. Once you enable macros, then the worksheet should create a menu and perhaps a window to connect will appear automatically. I can't quite remember.
