I have a VB6 app that queries and displays data from a
SQL Server database. I want to be able to open that data
in an Excel spreadsheet, for different users using
different connection strings (logins). I have been
creating dqy files on the fly and opening them with
Excel, but that precludes me from using any formatting or
using Pivot tables/charts. I would like to be able to
create an xlt template file with the formating I want,
but haven't been able to figure out how to modify the
data connection. This is what I've got so far:

Dim xlsApp As Excel.Application

If xlsApp Is Nothing Then
' Set xlsApp = New Excel.Application
Set xlsApp = CreateObject("Excel.Application")
End If
xlsApp.Workbooks.Open App.Path & "\DataReport.xlt"

' update SQL connection login
???

xlsApp.Visible = True

Any ideas?

Thanks