![]() |
How do I modify a SQL connection string in an xlt file from a VB6 app?
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 |
How do I modify a SQL connection string in an xlt file from a VB6 app?
Record a macro in Excel while getting external data using Data Import
External Data, then adapt the resulting code. "DBatesX" wrote in message ... 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 |
How do I modify a SQL connection string in an xlt file from a VB6 app?
"DBatesX" wrote ...
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. -- |
All times are GMT +1. The time now is 01:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com