Is there a way to open a excel file from sql server (query window or DTS). I
want to run a macro on a excel file before I load the data from the file to
SQL server table using dts. If someone can post the code to that would be
very helpful

Thanks,
MG

Hi MG,

There may be a better way, but you can use certain OLE-related Extended
Stored Procedures to automate other applications. For example, the T-SQL
code below may work for you. You may want to ask you question in a SQL
newsgroup, as there may be better ways of doing something like this.

Also, keep in mind that this merely opens the workbook - from there, your
workbook will have to take over. You can put some code in the Workbook_Open
event routine that calls another subroutine. In that subroutine, you can do
your processing, set ThisWorkbook.Saved=True, then invoke Application.Quit.
If you want to call a specific VBA routine from SQL after opening the
workbook, you would have to execute the "Run" method of the Application
object (in this case @object), passing in the appropriate parameters and
getting the return value, if any. But then you would have to set the Saved
property and invoke the Quit method as well, so it would make this code
substantially longer and slower.

declare @hr integer,
@object integer,
@wbs integer,
@src varchar(255),
@error varchar(255),
@test integer

EXEC @hr = sp_OACreate 'Excel.Application', @object OUTPUT
if (@hr < 0)
begin
EXEC sp_OAGetErrorInfo @object, @src OUTPUT, @error OUTPUT
PRINT 'Error: ' + @src + ', ' + @error
end
else -- successful creation of Excel
begin
EXEC @hr = sp_OAGetProperty @object, 'Workbooks', @wbs OUTPUT
if (@hr < 0)
begin
EXEC sp_OAGetErrorInfo @object, @src OUTPUT, @error OUTPUT
PRINT 'Error: ' + @src + ', ' + @error
end
else -- successful retrieval of wb collection
begin
EXEC @hr = sp_OAMethod @wbs, 'Open', @test OUTPUT, 'C:\test.xls'
if (@hr < 0)
begin
EXEC sp_OAGetErrorInfo @wbs, @src OUTPUT, @error OUTPUT
PRINT 'Error: ' + @src + ', ' + @error
end
else -- successful opening of wb
begin
EXEC sp_OADestroy @wbs
EXEC sp_OADestroy @object
PRINT 'Success!'
end
end
end

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


MD wrote:
Is there a way to open a excel file from sql server (query window or
DTS). I want to run a macro on a excel file before I load the data
from the file to SQL server table using dts. If someone can post the
code to that would be very helpful

Thanks,
MG