Establishing ODBC connection and refreshing data using Macro/VBA

Hi - I'm new. My team is using PowerPivot to refresh data from our ERP using ODBC. Then using the data to pivot into a report template for P&L.

My question is, is there a way to using macro/VBA to automate the refresh of data process using ODBC? I wasn't sure if this was possible given we have to use a unique user ID/password to get access via ODBC for each individual.

If there is a way, I would greatly appreciate it. I'm currently trying to reduce the number of "steps" required to refresh a P&L report every month.

J

If you try to connect to an ODBC source and don't provide the credentials then the ODBC driver usually displays a prompt for the ID/Password... I did say 'usually' so this is probably the odd time it doesn't - why don't you just try connecting in code without supplying the password and see what happens?

I guess we can give that a try. My guess is the prompt for the password will stop the macro in the middle so it won't run the rest to refresh the data but I guess we can possibly make it a two click process instead of just one.