How do I modify a SQL connection string in an xlt file from a VB6 app?

[DBatesX]

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

 

[Bob Kilmer]

Record a macro in Excel while getting external data using Data > Import
External Data, then adapt the resulting code.

 

[Jamie Collins]

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).

If your connection is to a Jet datasource (Excel .xls, Jet .mdb, etc),
you can specify all the required info in the sql text e.g. using pubs:

To create a new Excel table (and workbook/worksheet if necessary):

SELECT
emp_id AS ID, fname AS Forename, minit AS Initial,
lname AS Surname
INTO
[Excel 8.0;HDR=YES;Database=C:\MyFolder\MyNewWorkbook.xls;].MyNewTable
FROM
[ODBC;Driver={SQL Server};SERVER=MYSERVER;DATABASE=pubs;UID=MYLOGON;Pwd=XXX;].employee
;

To append data to an existing Excel table with column headers:

INSERT INTO
[Excel 8.0;HDR=YES;Database=C:\MyFolder\MyWorkbook.xls;].[MySheet$]
(ID, Forename, Initial, Surname)
SELECT
emp_id AS ID, fname AS Forename, minit AS Initial,
lname AS Surname
FROM
[ODBC;Driver={SQL Server};SERVER=MYSERVER;DATABASE=pubs;UID=MYLOGON;Pwd=XXX;].employee
;

Jamie.

--