Renaming Excel worksheet from TSQL

Question

Post reply

Renaming Excel worksheet from TSQL

Roust_m

SSCoach

Points: 17410
More actions
August 31, 2007 at 12:44 am

#118097

Hi,
I got the below code from one of the posts in this forum, which allows some inserts/updates done on an Excel worksheet from TSQL. This does not even require installing Excel on the server.  Does anyone know, if you can rename and insert worksheets on existing workbook similar way?
Thanks.
=============================================================================

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$]')

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 738663 More actions · Answer 1

Have you tried it with replacing SHEET$1 with something else?

Roust_m SSCoach Points: 17410 More actions · Answer 2

Sorry, did not quite understand... Do you mean replacing this:

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

with this:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 5.0;Database=C:\Temp\NorthwindTest.xls;',

'SELECT EmployeeID,LastName,FirstName FROM [NewName]')

SELECT EmployeeID,LastName,FirstName FROM Northwind.dbo.Employees

?