There are two steps to this: 1) setting up the ODBC connection, and 2) connecting Excel to that ODBC connection, and making it super easy and super clear how users can refresh the data.
It would be nice if this could be automated, so that end-users could have easy access to Excel's ease-of-use.
odbcconf.exe can be used within a .bat file to automate this. (though note that this tool is deprecated, in favor of PowerShell's Add-OdbcDsn)
One method of automating this is to create the ODBC connection manually, record the various bits of data that got created, and then apply those changes to other people's computers:
HKEY_CURRENT_USER\Software\Odbc\Odbc.ini\Odbc Data sources\
C:\ProgramData\IBM\DB2\<driver_name>\cfg\db2cli.ini
C:\ProgramData\IBM\DB2\<driver_name>\cfg\db2dsdriver.cfg
There seems to be conflicting information, but from what I've read, it's impossible to store the password within the ODBC connection. (Even though the DB2 UI suggests that it can? TODO
— This part is confusing.) [1]
TODO
— If the user needs to first install the DB2 ODBC drivers, there are a couple places that those downloads might be available from: [1]
Random links — this may be useful when connecting from Excel to ODBC, using a DSN password