October 20, 2011 at 3:29 am
Hi everyone,
I'm trying to find a way of getting the sheet names and, more importantly the sheet IDs from an Excel 2007 (xlsx) Workbook into Sql Server 2008. I do have some code (not written by me) that will work for Excel 2003 but not for Excel 2007. Can anyone offer any suggestions as to how I could accomplish this please . It's worth pointing out that, being something of a newbie to this aspect of SQL, I don't understand how the existing code works.
If anyone could help I'd be very grateful.
Thanks Very Much
David.
Here's the code as it stands:
ALTER PROCEDURE [dbo].[spSelUploadXlsWorkSheets](
@xlsWorkbook VARCHAR(255)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @hr int
DECLARE @adoCatalogue int
DECLARE @adoConn int
DECLARE @numTables int -- Number of tables/sheets found in workbook
DECLARE @curTable int -- current table/sheet processed
DECLARE @tableName VARCHAR(200)
SET @curTable = 0
-- Table variable to hold all sheetnames in workbook
DECLARE @result TABLE (sheetNo SMALLINT, sheetname varchar(255))
-- Create an ADOX-Catalog object
EXEC @hr = master.dbo.sp_OACreate 'ADOX.Catalog', @adoCatalogue OUTPUT
PRINT 'Create Catalog : ' + CAST (@hr AS VARCHAR)
-- Create an ADODB.Connection Object
EXEC @hr = master.dbo.sp_OACreate 'ADODB.Connection', @adoConn OUTPUT
PRINT 'Create Connection : ' + CAST (@hr AS VARCHAR)
-- Set Properties to open an Excel Workbook
EXEC @hr = master.dbo.sp_OASetProperty @adoConn, 'Provider', 'Microsoft.Jet.OLEDB.4.0'
PRINT 'Add Provider : ' + CAST (@hr AS VARCHAR)
EXEC @hr = master.dbo.sp_OASetProperty @adoConn, 'Properties("Extended Properties").Value', 'Excel 8.0'
PRINT 'Add extended properties : ' + CAST (@hr AS VARCHAR)
PRINT @xlsWorkbook
EXEC @hr = master.dbo.sp_OASetProperty @adoConn, 'Properties("Data Source").Value', @xlsWorkbook
PRINT 'Create Data Source : ' + CAST (@hr AS VARCHAR)
-- Open Connection to Workbook
EXEC @hr = master.dbo.sp_OAMethod @adoConn, 'Open'
PRINT 'Open Connection : ' + CAST (@hr AS VARCHAR)
-- Return OLE error
DECLARE @src VARCHAR(200)
DECLARE @desc VARCHAR(200)
EXEC sp_OAGetErrorInfo @adoConn, @src OUT, @desc OUT
PRINT convert(varbinary(4),@hr)
PRINT @src
PRINT @desc
-- Get ActiveConnection Reference
EXEC @hr = master.dbo.sp_OASetProperty @adoCatalogue, 'ActiveConnection', @adoConn
PRINT 'Retrieve Active Connection: ' + CAST (@hr AS VARCHAR)
-- Get total count of worksheets and named ranges in workbook
EXEC @hr = master.dbo.sp_OAGetProperty @adoCatalogue, 'Tables.count', @numTables OUTPUT
PRINT 'Table Count : ' + CAST (@hr AS VARCHAR)
-- Loop through all the worksheets to retrieve the name
WHILE @curTable <= (@numTables - 1)
BEGIN
DECLARE @command VARCHAR(200)
SET @command = 'Tables(' + CAST(@curTable AS VARCHAR) + ').name'
EXEC @hr = master.dbo.sp_OAGetProperty @adoCatalogue, @command, @tableName OUTPUT
PRINT 'Table Names : ' + CAST (@hr AS VARCHAR)
PRINT 'Table Names : ' + CAST (@hr AS VARCHAR)
INSERT INTO @result SELECT @curTable, @tableName
SET @curTable = @curTable + 1
END
EXEC @hr = master.dbo.sp_OAMethod @adoConn, 'Close'
EXEC @hr = master.dbo.sp_OADestroy @adoConn
SELECT sheetNo, sheetname FROM @result
END
October 20, 2011 at 3:39 am
Forgot to mention - when I execute the procedure and pass an appropriate parameter to it, the query executes successfully but with no results and I get the following messages displayed:
<<\\ACTUAL SERVER NAME, PATH & FILE NAME WITH XLSX EXTENSION IS DISPLAYED HERE>>>
Create Catalog : 0
ADO Catalogue : 16711422
Create Connection : 0
ADODB.Connection : 33488638
Add Provider : 0
Add extended properties : 0
<<\\ACTUAL SERVER NAME, PATH & FILE NAME WITH XLSX EXTENSION IS DISPLAYED HERE>>>
Create Data Source : 0
Open Connection : -2147467259
0x80004005
Microsoft JET Database Engine
External table is not in the expected format.
Retrieve Active Connection: -2146824579
Table Count : 0
(1 row(s) affected)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply