Dynamic export
-
-
I don't remember where I got these from... they seem to work... of course, the spreadsheet must already exist but you can make a master template, copy it to a new file, and update the new file.
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=C:\Temp\NorthwindTest.xls;',
'SELECT EmployeeID,LastName,FirstName FROM [Sheet1$]')
SELECT EmployeeID,LastName,FirstName FROM Northwind.dbo.Employees
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=C:\Temp\NorthwindTest.xls;',
'SELECT EmployeeID,LastName,FirstName FROM [Sheet1$] WHERE EmployeeID = ''''')
SELECT EmployeeID,LastName,FirstName FROM Northwind.dbo.Employees
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Temp\NorthwindTest.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Temp\NorthwindTest.xls;HDR=no',
'SELECT * FROM [Sheet1$a5:a9]')
UPDATE OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=C:\Temp\NorthwindTest.xls;',
'SELECT EmployeeID,LastName,FirstName FROM [Sheet1$]')
SET LastName = NULL
WHERE EmployeeID = 5
UPDATE OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=C:\Temp\NorthwindTest.xls;',
'SELECT EmployeeID,LastName,FirstName FROM [Sheet1$]')
SET EmployeeID = '',
LastName = '',
FirstName = ''
WHERE EmployeeID IS NOT NULL
DELETE OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Temp\NorthwindTest.xls;HDR=Yes',
'SELECT EmployeeID FROM [Sheet1$]')
WHERE EmployeeID = 5
SELECT * FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\Temp\NorthwindTest.xls',
'SELECT * FROM [Sheet1$]')
DELETE OPENROWSET('MSDASQL',
'Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\Temp\NorthwindTest.xls',
'SELECT * FROM [Sheet1$]')
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Problem with this you MUST know the name of the sheet inside of Excel Workbook.
And users use to rename/delete those sheets without asking permissions from those bloody developers.
_____________
Code for TallyGenerator -
It looks like a perfect solution for me. Thanks heaps.
-
No problem at all. I will create a blank workbook having N worksheets with headers. I will name the worksheets sequentially: Sheet1, Sheet2, ... , SheetN (This can be automated, I just created a workbook with over 1500 worksheets. Excel 2003 help tells that the number of worksheets is "Limited by available memory"!). Those worksheets can be renamed to the category name later, when user opens the workbook with "AutoOpen" procedure.
After the report is finished I will copy the result into user accessible directory and replace the one on the server with blank file again.
-
> I just created a workbook with over 1500 worksheets.
Good revenge!
_____________
Code for TallyGenerator
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply