April 2, 2007 at 12:09 am
Hello all,
I've been having a look on Google and of course this wonderful site regarding my following issue, but can't seem to find concise answers. I've found a number of issues similar to this, but nothing exactly the same.
I have an existing Excel Spreadsheet with Excel calculations, different coloured font and text depending on context etc. for a handful of these cells, in no particular order, I need to populate them with the results of an SQL Query (from a SQL Server 2000/5 database), but leave the rest of the cells untouched. However, everything I've read so far regarding DTS and Excel does not give you the ability to target a specific cell (i.e. I only want to update, say, cell G8 with value X, and not the rest of the worksheet). So my first question is, is there any way of using DTS packages to export data from SQL Server in to Excel, but only target certain cells, and not overwrite the entire worksheet?
Is there anyway to do this? I.e. target specific cells?
Thanks for your time and apologies if this is a really simple question.
Thanks.
Dan
April 2, 2007 at 12:59 am
After doing a bit more research, I see that SSIS is the replacement for DTS packages. Does anyone know how hard it is to use SSIS to update specific cells in an Excel sheet?
Dan
April 2, 2007 at 1:15 pm
I don't know about SSIS, but in DTS you could store the results of your query in Global Variables, then use an Active x script task to open the .xls file and populate specific cells with the values of the Global Variables.
Dim xlApp
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Workbooks.OpenText "c:\Test\MySpreadsheet.xls"
.Range("A3").FormulaR1C1 = DTSGlobalVariables("Var1").Value
.Range("D9").FormulaR1C1 = DTSGlobalVariables("Var2").Value
.ActiveWorkbook.Save
.Application.Quit
End With
Set xlApp = Nothing
April 2, 2007 at 5:34 pm
Erik,
thanks for the reply.
Your above suggestion sounds very good. I'll have to confirm that there are no security issues surrounding the Active X scripts, but it sounds the most appealing approach I've seen so far.
Now to brush off my VB skills (if I ever had them!).
D
Dan
April 3, 2007 at 11:17 pm
I'm probably being stupid, but what is the best way to populate this list of Global Variables? The data will be coming out of a database.
The current flow of data I've got is Database to Global Variables to Excel.
Is this sensible? Or would it make more sense to go Database straight to Excel? I guess it would but then I'd have to do a local database connection from an ActiveX object.
Any advice welcome.
Dan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply