Help! Need to append rows daily to an existing Excel sheet.

Question

Help! Need to append rows daily to an existing Excel sheet.

MG-148046

SSCrazy Eights

Points: 9946
More actions
February 16, 2006 at 2:32 pm

#76490

I have not idea how to go about doing this. I know next to nothing about ActiveX and I'm guessing that's what I need.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Viewing 6 posts - 1 through 5 (of 5 total)

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

db_dude Right there with Babe Points: 741 More actions · Answer 1

Fairly often, I've got to get data from excel or into excel, and I've used the following type of insert from query analyzer.

insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="S:\folder\Test.xls";Extended properties=Excel 8.0')...Sheet1$ (ColumnName1)

select col1 from dbo.tablename

It will automatically append to the end of the worksheet called Sheet1 (ie. "Sheet1$" in the t-sql statement)

If this doesn't work for you I would think dts would also.

Hope this helps,

Scott

db_dude Right there with Babe Points: 741 More actions · Answer 2

...another thing, you could get the following error if the spreadsheet is open. It needs to be closed when accessing it.

Server: Msg 7399, Level 16, State 1, Line 1

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

you can also select using the same opendatasource (or openrowset)

select * from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="S:\folder\Test.xls";Extended properties=Excel 8.0')...Sheet1$

can be a handy little thing for your scriptlet cache.

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 3

thanks - I'll give this try a little later. I'm much more comfortable with QA than ActiveX

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 4

I'm getting an error :

Invalid column name 'columnname1'

Any idea?

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Joe Williams-227299 SSC Journeyman Points: 87 More actions · Answer 5

It's pretty easy to create an Excel connection to the worksheet, and then use a Transform Data task to move data into it. By default, rows will be appended to the worksheet.