Excel QueryTable Refresh Causes "Choose Profile" Dialog to Appear
 

Hi

I'm trying to Import my firm's Global Address List in Outlook to a SQL
Server 2005 Database that's updated daily.

I know this probably appears to be a far fetched way to pull it off,
but I couldn't find an easier way to do this, so here's the way I'm
able to accomplish this on a Windows Server 2003 box.

-Created a linked table in Access Database to Exchange Global Address
List
-Setup a User DSN via Control Panel-Administrative Tools-Data Sources
(ODBC) to the Access DB
-Setup an Excel Spreadsheet that pulls from that Data Source.
-Included some VBA code to the 'Workbook_Open' event which refreshes
the query to pull the most recent data if any data has changed in the
data source (which ultimately means the Global Address List data was
modified in Exchange)
-Setup a SSIS package that opens the Excel Workbook (thereby launching
the VBA code in the step above), then that pulling the data from the
Excel source and plugging it into a SQL Server 2005 DB.

Process is working fine except for one (seemingly) small step--in the
Excel VBA code the 'Refresh' portion provides an Outlook-style 'Choose
Profile' prompt (Note that there's only 1 profile on this machine).
If I'm babysitting the process I can just hit enter and that's all
that's needed, but this needs to be automated completely so I need to
do one of two things with this, those being:

-grab it (via an HWND/GetWindow-type routine) and programmatically hit
enter
-suppress it

To get the second option to work I've tried just about everything--
programatically opening an Outlook session beforehand, using the
Application.DisplayAlerts method, playing around with the
Excel.MailLogon methods, etc etc. And obviously i've changed the 'Set
as Default Profile' option both in the popup itself, and via Control
Panel.

The bulk of the web research i've done applies more to SendMail-type
events rather than a QueryTable like this, and the solutions to those
don't apply to my scenario....and i've done a TON of research on this.

How can i get rid of this thing? I've come pretty far up this path so
would prefer to just find a way to get rid of it, but if looking at
the process of what i'm trying to do (migrate from Exchange to SQL
Server 2005) then if you have a method of doing that that isn't
terribly involved i'd be willing to hear that out.

Thanks in advance

Jeremy