I use Excel 2003 with stored procedures without involving VBA. The trick is to manually edit the .dqy file that MSQuery generates, or more accurately, just manually create the file and leave out MSQuery altogether. Its just plain text so Notepad works nicely. I personally use UltraEdit.
The format you want is:
EXEC dbo.myStoredProc ?, ?
The first 2
lines are standard.
Line 3 is basic connection string info.
Line 4 is your EXEC statement with parameters listed as '?'s
Line 5 is a tab-separated list of parameter names, or prompts for Excel
Save the file as myStoredProc.dqy. In Excel, use Import External Data > Import Data, and browse for your new file. Then hit the Parameters button and you'll see your parameter prompts. I usually set them to update on change of a cell value.
I was always frustrated with MSQuery limitations, now I have no need for it. This made things much easier for me and I have built some very nice user interfaces with zero VBA.