EXcel 2007 Refreshall using VBA and connection string
Posted on 2010-11-07
Hi, I have a few Excel spreadsheets that are connected with the 2007 Data Connection using Sql Authentication (SQL 2005). I automated the refreshall with no problem, but the connection form pops up to insert a UID and password. This process will be kicked off using SSIS so I need to automaticly pass in the correct connection string. I can't change to Windows authetication. The excel spreadseeht is saved as a .xlsx which removes the VBA so that our users will not have access. I have been trying to figure out how to do prevent the connection pop up form from opening and pass in the VBA connection string but have not been able to come up with the correct VBA code or find what I need to perform a Refresh All. The Refresh ALL runs a proc in SQL that updates a few worksheets in the workbook. In most instances they are Pivot tables, but in some, it is straight data - columns and rows. Any help would be appreciated.