Writing in Excel file programatically

[bullpit]

Hi,
I found several examples to write into an existing excel file. I can write
in to a particular cell by using something like this:

Code Snippet
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objRange.set_Value(m_objOpt, "Last Name");

The problem is that I have a lot of cells that need to be manipulated. So I
tried to use OleDB for that and could write a query like this:

Code Snippet
objCmd.CommandText = "Insert into [Sheet1$] ([First Name], [Last Name])" +
" values ('Bill', 'Brown')";
objCmd.ExecuteNonQuery();

This works too, but only if I just have a flat table structure. The file I
have to modify has a header type thingy (which has company name that spawn
thru several cells, and some other information). How can I skip the initial
cells which comprise of the header and then start writing from the actual
table cells.

The query method shown above gives error that it cannot find the table name
Sheet$ if I include the header.

Thanks

 

[Cindy M.]

Hi =?Utf-8?B?YnVsbHBpdA==?=,

I found several examples to write into an existing excel file. I can write
in to a particular cell by using something like this:

Code Snippet
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objRange.set_Value(m_objOpt, "Last Name");

The problem is that I have a lot of cells that need to be manipulated. So I
tried to use OleDB for that and could write a query like this:

Code Snippet
objCmd.CommandText = "Insert into [Sheet1$] ([First Name], [Last Name])" +
" values ('Bill', 'Brown')";
objCmd.ExecuteNonQuery();

This works too, but only if I just have a flat table structure. The file I
have to modify has a header type thingy (which has company name that spawn
thru several cells, and some other information). How can I skip the initial
cells which comprise of the header and then start writing from the actual
table cells.

The query method shown above gives error that it cannot find the table name
Sheet$ if I include the header.

If I'm understanding you correctly, you should be able to assign a range name
to the data section, only. Then OLE DB should let you address that range name
(instead of Sheet1$).

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[bullpit]

Do you mean I can write the query like this:
Insert into ([First Name], [Last Name])" + " values ('Bill', 'Brown')";

If not, please let me know the correct way.

Thanks a lot for your reply.

 

[Cindy M.]

Hi =?Utf-8?B?YnVsbHBpdA==?=,

Do you mean I can write the query like this:
Insert into ([First Name], [Last Name])" + " values ('Bill', 'Brown')";

If not, please let me know the correct way.

Have you read this KB article?

http://support.microsoft.com/default.aspx?scid=kb;en-us;278973

This additional information may also be useful.

If you still have questions afterwards, I suggest you ask in the data.ado
newsgroup for a detailed discussion. Or possibly the excel.programming
newsgroup.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[bullpit]

Thank you Cindy. The article was very good and I have experimented with
suggestions and have them working for now. I haven't gone into full blown
production yet but soon will be. Will post more questions if need be.

Thanks again.

Hi =?Utf-8?B?YnVsbHBpdA==?=,

Do you mean I can write the query like this:
Insert into ([First Name], [Last Name])" + " values ('Bill', 'Brown')";

If not, please let me know the correct way.

Have you read this KB article?

http://support.microsoft.com/default.aspx?scid=kb;en-us;278973

This additional information may also be useful.

If you still have questions afterwards, I suggest you ask in the data.ado
newsgroup for a detailed discussion. Or possibly the excel.programming
newsgroup.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)


This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Cindy M.]

Hi =?Utf-8?B?YnVsbHBpdA==?=,

Will post more questions if need be.

I notice, on re-reading, that I forgot to paste in the link
for "additional resources". Carl Prothman's information
supplements the KB article

http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProv
iderForExcel

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update
Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail