If there was a vba function to replace
any spaces found in a string with underscores,

VBA6 (as from Office 2000) has a Replace function built in.

And with VBA5, you can use Application.Substitute()

This seems to be working:

strSheetName = Replace(strSheetName, " ", "_")