Load data into multiple excel sheets in the same Excel workbook
-
Hi,
I have to load data from SQL server database into the excel workbook.
But I have to create the excel sheets dynamically based on the number of weeks available in the current month.
Based on the week i have to choose the created excel sheet as my destination and load the data.
Also while creating the sheet, i have to create the header also.
Once data is loaded into my excel sheets I have to send an email with details like week name, how many records are loaded ....etc. this will be generated dynamically and send it to respective users.
Please can you help me on the same?
Regards,
Chinni.
-
what you are asking for in your first post here is a number of complex things on some very wide subjects, much too wide to provide any meaningful examples without more data from you. there are more than one way to do everything you asked about, and it depends on what you are familiar with.
for SQL data to excel, this is a good starting point:
http://www.mssqltips.com/tip.asp?tip=1202
I've only done it using the OPENROWSET method, which requires a number of things:
1. the .xls document must already exist.
2. all the worksheets must also already exist, and the first row must contain the header/column names.
3. you can't chang ethe worksheet names with openrowset, so if you want them named anything else, they have to be done before you try to touch them via TSQL. it's basically a template that you can populate...you can't do it dynamically i think.
once the worksheet exists, you can do something like this:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT dbname,Name, Date FROM [Sheet1$]')
SELECT db_name(),[Name], GETDATE() FROM sys.tables where is_ms_shipped = 0
GO
USE SandBox
GO
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT dbname,Name, Date FROM [Sheet2$]')
SELECT db_name(),[Name], GETDATE() FROM sys.tables where is_ms_shipped = 0
the rest of what you are asking, where to put your data and sending an email with msdb.dbo.sp_send_dbmail you can ask again once you are at that point and have specific questions.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply