Well, I'm not familiar with DataPig so I can't comment on the relative merit of it vs ADO.  One advantage of ADO is it comes with Excel.

We use VBA to handle the inputs via form controls, create the connection to SQL using ADO, execute the data extraction using ADO and a SQL procedure (returning datasets) then format data, created plots etc. in Excel using VBA again.

In the past we created some ugly 'command strings' i.e. SQL commands using VBA and then executed the strings.  Now we do the SQL part using SQL procedures; and arguments required are assembled on the VBA side.

Doing the SQL in procedures is a big improvement over the execute string approach for clarity, maintainability, etc.