This type of a solution is extraordinarily powerful. Being able to retrieve data directly from the database for use in Excel has been a huge help in trying to get information out to the users. Once the data is in VBA, there are lots of other nice things that can be done with the data via VBA that doesn't work quite as well with SQL (my opinion). In addition, formatting of the spreadsheet is endless with VBA. With that said, i would like to add some caveats...
PROTECT YOUR CODE. In your example, the connection string had the userid and password in the clear. One person already mentioned this. This is really, really bad. If you don't have very tight controls over who can access data in your database, you have opened yourself up to data corruption from nefarious users that have free reign in your SQL Server once they find the userid and password. Several ways to work with this - PASSWORD PROTECT your VBA code - don't let your users see this. It is more work for the developer - you need to write the code to retrieve information, but it keeps people from doing bad things. Your reports become in effect, read only. Use an ODBC connection. It would need to be set up on the users machine, and depending on how many you have, this could be problematic (or not). Create a generic userid / password that gives access to selected files as READ ONLY. You do not have to put userid / password into the connection string.