Tools: SQL Server 2K, Excel 2000

Hi,

I have an Excel report worksheet with formatted headings. What I want
to do is to export data from the SQL server into a specific cell of the
excel file. Is this doable? Can somebody give me some direction on how
to accomplish this?

I appreciate any suggestions.

Edgar J.

To export data from SQL Server table to Excel file, create an Excel
file named testing having the headers same as that of table columns and
use this query

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

To export data from Excel to new SQL Server table,

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

To export data from Excel to existing SQL Server table,

Insert into SQLServerTable Select * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')

Bala

Thanks Bala for your suggestion. What I really need is a direction on
how to update a specific cell, ex: C10. I have a scalar value coming
from the SQL Server not rows of data.

Edgar

Does the data need to be fetched each time the spreadsheet is
recalcuated?

I have done this a couple of different ways depending on the situation.
Here are a few ideas that might be helpful:

If there are not very many cells that would get updated then I would
create a custom excel function within the workbook that connects to SQL
Server, retrieves the value and then puts it in the cell. Using this
approach, the cell can move around the spreadsheet and you don't have
to change any of the code. This can also get more flexible as you
could pass in variables to the function. Make sure to set the function
to "volatile" or it will not recalc each time the spreadsheet is
recalc'd. Also note that this is solution can get annoying to the user
as it slows the worksheet calculation since each time it is
recalculated, a connection and query has to be made to SQL Server.
This can be aided by creating a connection to SQL server when the
spreadsheet is opened. If that is the case then I would make sure to
ask the user if they want to connect, store the response, and build it
into an if statement so that the function does time out a call to the
database server each time.

If there are a lot of cells to update, then I would suggest adding a
custom dropdown menu to the standard excel menu that has a button to
recalculate the "special" cells that get data from SQL Server. This is
more efficient in that you don't need to create, query, and then close
a connection for each cell - this requires a little more work to set up
the menu. Another issue is finding the cells that need to be updated.
You could do this with your custom menu too and store the result which
works well. I have also used cell comments to flag cells to receive
data since the comments are stored in a list that is easy to iterate
through.

Perhaps another solution would be to use the msft wizard under the data
menu and create a new database query - this has many limitations but is
very easy to set up.

A few to think about anyway, hopefully one of those is useful...

The data is updated on a weekly basis. I mentioned I have a scalar
value. But I think I can fetch one row with 3 columns. The cells I
want to update on the Excel are: C4, C7 and C10.
The integer values are used in the excel formulas.

So far, I've tried using a DTS with activex to test just one particular
cell. But the code was updating the cell below C4 which I can't figure
out why. Here is the ActiveX code I'm using:

Function Main()

Dim appExcel
Dim newBook
Dim oSheet

Dim oPackage
Dim oConn

Set appExcel = CreateObject("Excel.Application")
Set newBook = appExcel.Workbooks.add
Set oSheet = newBook.Worksheets(1)

'Specify column names.
oSheet.Range("C4").Value = "D"


DTSGlobalVariables ("FileName").Value = "C:\MyExcel.xls"

With newBook
.SaveAs DTSGlobalVariables("FileName").Value
.Save
End With

appExcel.quit

'dynamically specify the destination Excel file

set oPackage = DTSGlobalVariables.parent

' connection 2 is to the Excel file

set oConn = oPackage.connections(2)
oConn.datasource = DTSGlobalVariables("FileName").Value

set oPackage = nothing
set oConn = nothing

Main = DTSTaskExecResult_Success

End Function

Again, thank you for all your input and/or directions.

Edgar J.