Excel 2003 connects to SQL2000 using DSN - Changing server

We have a number of Microsoft Excel 2003 workbooks on Windows XP that connect to a Windows 2000 server running SQL 2000 which use a system DSN to connect. We are upgrading to a new server (Windows 2008 Server running SQL2005)

What is the best way to get the Excel workbooks to connect to the new server? I have read quite a bit about this on the net and it seems that just changing the DSN information will not achieve this as connection information is imbedded in the Excel workbooks. I have found this:

Steve,
There should be no problem with you changing the DSN and effecting the update across the board provided the following points are adhered to:
The new source should have :
- the same DSN
- the same Description
- the same UID & PW i.e. whatever is valid on the old source should be valid on the new
The old source should be deleted (or renamed until all is confirmed as working) from the System DSN lists
This is the standard method I use when various clients have changed servers. Except once, where the table structure was changed by their IT team without telling anyone, I have never had any issues this way.
Assuming that the queries are linked by DSN name, whether as a workbook query or via VBA macros, all should be good.
Have you already tried this method?
Regards

I had read that this info was 'hard coded' into Excel workbooks and found the following code:

This seems to work but do I really have to run this on every workbook?

It appears that once an Excel workbook is created with an ODBC link, the ODBC link is no longer needed and the workbook can be passed around to whomever. However, if there is a system DSN, the method described above will work as well