December 30, 2008 at 6:29 am
Hi,
I'm developing a stored procedure to update data from an excel sheet (which contains a pivot table) without opening the file itself.
I can not find the right method to execute the command Update/Refresh on the Excel sheet.
Do you have any suggestions ?
Thanks
The following stored procedure:
[font="Courier New"]/* Create stored procedure */
CREATE PROCEDURE [dbo].[CS_SP_DMO_RefreshExcel]
AS
BEGIN
declare @hr int,
@objExcel int,
@objWorkBooks int,
@objWorkSheet int,
@WorksheetIndex int,
@filename varchar(512),
@strErrorMessage varchar(255),
@FindFile int, /* 0=False, -1=True */
@T int
set @filename = 'C:\Temp\Refresh.xls'
set @WorksheetIndex = 1
exec @hr = sp_OACreate 'Excel.Application', @objExcel out
if (@hr = 0)
select @strErrorMessage = 'Returning WorkBooks object '
IF (@hr = 0)
EXEC @hr = sp_OAMethod @objExcel,
'WorkBooks.Open',
@objWorkBooks OUT,
@filename
IF (@hr = 0)
BEGIN
Set @FindFile = -1
EXEC @hr = sp_OAMethod @objWorkBooks,
'Worksheets.Item',
@objWorkSheet OUT,
@WorksheetIndex
IF (@hr = 0)
PRINT ('Selection Worksheet successful')
ELSE
PRINT ('Selection Worksheet ended with errors')
IF (@hr = 0)
EXEC @hr = sp_OAMethod @objWorkSheet, 'Activate'
IF (@hr = 0)
PRINT ('Worksheet successful activation')
ELSE
PRINT ('Activation Worksheet ended with errors')
-- Refresh WorkSheet
IF (@hr = 0)
--EXEC @hr = sp_OAMethod @objWorkSheet, 'Refresh' -- ??
EXEC @hr = sp_OAMethod @objWorkSheet, 'Update' -- ??
IF (@hr = 0)
PRINT ('Refresh Worksheet successful')
ELSE
PRINT ('Refresh Worksheet ended with errors: ' + str(@hr))
EXEC @hr = sp_OAMethod @objExcel, 'Workbooks.Close'
EXEC sp_OAMethod @objExcel, 'Close'
END
EXEC sp_OADestroy @objExcel
EXEC sp_OADestroy @objWorkSheet
EXEC sp_OADestroy @objWorkBooks
END[/font]
December 30, 2008 at 12:32 pm
When I googled "sp_OAMethod Excel Refresh", I saw that you had this same question posted on at least one other SQL forum. The problem is that it's not really a T-SQL question. What you are really wanting to know is the name of a method from the Excel object model, which will NOT be documented as a part of T-SQL. You should probably also post this question to an Excel forum, as the object model for Excel is not the primary focus here.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 30, 2008 at 1:56 pm
It's "Application.CalculateFull".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 8:44 pm
Let's have an instant replay on ol' helmut head spiking THAT ball... yep, there it is! A perfect 6 points, spiking the ball, and the ref didn't even call him on being in the wrong forum! What a player he is!...
... now if we could just get him to remove the helmet for the locker room interviews... 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
December 31, 2008 at 8:11 am
Fifteen yard penalty for illegal use of The Force... will be assessed from the line of scrimmage.... third down...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 31, 2008 at 9:25 am
Barry, Bob, and sgovoni... you guys have a great new year!
--Jeff Moden
Change is inevitable... Change for the better is not.
December 31, 2008 at 9:47 am
Not that I don't enjoy automation and all, but I just prefer to set the "auto-refresh on opening" option in the Excel data range......
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 31, 2008 at 10:26 am
Jeff: You have a great New Year's too! I'm off to Dallas to watch my Rebels play Texas Tech in the Cotton Bowl.
Matt: Quit spoiling everybody's fun! :hehe:
Seriously, hope y'all all have a safe and happy holiday and may 2009 be a better year than 2008.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 31, 2008 at 10:29 am
Heh. Believe it or not, I still do Excel.VBA projects from time to time. Just finished one in November. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 31, 2008 at 10:30 am
RBarryYoung (12/31/2008)
Heh. Believe it or not, I still do Excel.VBA projects from time to time. Just finished one in November. 🙂
I completely believe it. I still get to support a MS Access application that's been in Production for 14 years straight... Amazing the mileage some of those things get.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 31, 2008 at 10:32 am
Matt Miller (12/31/2008)
Not that I don't enjoy automation and all, but I just prefer to set the "auto-refresh on opening" option in the Excel data range......
I usually turn it off because the links tend to get broken when we move the files around or email them to & from the clients.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 31, 2008 at 10:55 am
Who was it that defined legacy code as the stuff that has been around so long that you finally got most of the bugs out of it?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
January 1, 2009 at 12:06 pm
Bob Hovious (12/31/2008)
Who was it that defined legacy code as the stuff that has been around so long that you finally got most of the bugs out of it?
I don't know, but whoever it was hasn't seen the crap code left to our group. It got my hourly "WTF rate" up over 100... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2009 at 2:57 pm
Thanks for your suggestions.
After some tests, I found the following solution:
[font="Courier New"]/* Create stored procedure */
create procedure [dbo].[USP_DMO_Refresh_Excel_Pivot_Table]
(@FileName varchar(512),
@WorksheetIndex int,
@PivotTableName varchar(100),
@Debug tinyint)
as
begin
declare @hr int,
@objExcel int,
@objWorkBooks int,
@objWorkSheet int,
@strErrorMessage varchar(255),
@FindFile int,
@objErrorObject int,
@cmd varchar(128)
set nocount on
exec @hr = sp_OACreate 'Excel.Application', @objExcel output
if (@hr = 0)
select @strErrorMessage = 'Returning WorkBooks object '
if (@hr = 0)
exec @hr = sp_OAMethod @objExcel, 'WorkBooks.Open', @objWorkBooks output, @FileName
if (@hr = 0)
begin
set @FindFile = -1
exec @hr = sp_OAMethod @objWorkBooks, 'Worksheets.Item', @objWorkSheet output, @WorksheetIndex
if (@debug = 1)
if (@hr = 0)
print('Selection Worksheet completed successful')
else
print('Selection Worksheet ended with errors')
if (@hr = 0)
exec @hr = sp_OAMethod @objWorkSheet, 'Activate'
if (@debug = 1)
if (@hr = 0)
print('Worksheet successful activation')
else
print('Activation Worksheet ended with errors')
if (@debug = 1)
if (@hr = 0)
print('Refreshing PivotTables = ' + @PivotTableName)
if (@hr = 0)
begin
set @cmd = 'PivotTables("' + @PivotTableName + '").RefreshTable'
exec @hr = sp_OAMethod @objWorkSheet, @cmd
end
if (@hr = 0)
begin
set @cmd = 'PivotTables("' + @PivotTableName + '").SaveData'
exec @hr = sp_OAMethod @objWorkSheet, @cmd
end
if (@debug = 1)
if (@hr = 0)
print('Refresh PivotTables("' + @PivotTableName + '") completed successful')
else
print('Refresh PivotTables("' + @PivotTableName + '") ended with errors: ' + str(@hr))
end
else
set @FindFile = 0
if (@FindFile = -1)
begin
exec sp_OAMethod @objExcel, 'ActiveWorkbook.Save'
exec sp_OAMethod @objExcel, 'Workbooks.Close'
end
exec sp_OAMethod @objExcel, 'Close'
if (@hr <> 0)
begin
declare @Source varchar(255),
@Description varchar(255),
@Helpfile varchar(255),
@HelpID int
execute sp_OAGetErrorInfo @objErrorObject,
@source output,
@Description output,
@Helpfile output,
@HelpID output
select @hr, @source, @Description,@Helpfile,@HelpID output
select @strErrorMessage = 'Error whilst: '
+ COALESCE(@strErrorMessage, 'doing something')
+ ', ' + COALESCE(@Description, '')
raiserror (@strErrorMessage, 16, 1)
end
exec sp_OADestroy @objExcel
exec sp_OADestroy @objWorkSheet
exec sp_OADestroy @objWorkBooks
exec sp_OADestroy @objErrorObject
set nocount off
return @hr
end[/font]
Example:
[font="Courier New"]exec dbo.USP_DMO_Refresh_Excel_Pivot_Table
@FileName = 'c:\temp\Refresh.xls',
@WorksheetIndex = 1,
@PivotTableName = 'PT1',
@Debug = 1[/font]
Excuse me if I wrong forum to post my question.
Spare the best wishes of good year 2009!
Sergio
January 4, 2009 at 1:32 pm
Nothing to excuse 🙂
I was just trying to direct you to another forum where you might get a faster answer. But Mr. Young nailed it. Thanks for putting your final solution up. I'm filing it away for future reference.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply