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.
Quote: Originally Posted by Edgar 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. |
Have you experimented with the MS Query option in Excel? Underneath "Data" + "Import External Data" + "New Database Query" you can connect to your SQL Server datasource and query for the values you are looking for. There are some limitations however (i.e. can't run stored procedures that take parameters directly) but seems to cover a lot of the things people want to do in Excel.
|||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?|||
Quote: Originally Posted by Edgar 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 |
If you use the External Data option I described, then you can exactly what you are looking for.
In your example, you want to put a value into c10. You use the "Import External Data" feature to connect / build / write your SQL Query. As part of this process you'll tell Excel what cell you want the data populated into, so select c10 (note: a column header is returned so you either need to put the results into c9 so your data is in c10, or put the results on a different sheet or far removed cell of the same sheet and then make c10 = the cell that the data is actually returned to.
|||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.|||Hi Edgar,
You haven't mentioned why this approach won't work or isn't appropriate, so I'll elaborate.
First, add a new worksheet to your workbook. Call it whatever you want...for now I'll refer to it as "scaler_sheet". Also, as an example, I'll pretend your current worksheet you are referring to (the one you want the data in) is called "live_sheet". Make sure "scaler_sheet" is the active sheet...
From the "Data" Menu, choose "Import External Data" + "New DB Query". This will open MS Query. Choose "New Data Source" and then "Ok". Give your datasource a name and choose the "SQL Server" driver. Choose "Connect". This should open the SQL Server login box. Enter your server name or IP, login id, password. Click on the "Option" button and set your default db. Click "Ok". You should be back at the "Create New Data Source" dialogue. Leave "Choose Default Table" blank. Choose "Ok" and you can now tell Excel to use your new datasource (highlight it and click "Ok"). This will open MS Query...from here you can choose tables (if you want) or write your SQL directly ("Cancel" out of the table selection box and then click on the "SQL" button in the tool bar).
Depending on how complex your SQL is, MS Query may complain that it can represent your query graphically (no big deal). When your query results are displayed, choose "File" + "Return Data to Microsoft Excel". You'll be prompted to choose the cell where you want the data returned (i.e. scaler_sheet!A1). The data will be displayed with the column headers in A1, B1, and C1 and the data in A2, B2, and C2 respectively. Now, from your description above, you said you need your SQL data to appear in C4, C7, and C10. So, make this sheet the active sheet, and then reference the data from the appropriate cells from scaler_sheet (i.e. live_sheet!C4 = scaler_sheet!A2...live_sheet!C7 = scaler_sheet!B2...and live_sheet!C10 = scaler_sheet!C2).
I believe you will find this will work very well for you, and the data need only be refreshed when you want it to be.
Keith
