how to insert data into an excel spreadsheet cell

[spowel4]

I need some help with this please: I'm looping through some data to
total some figures. Each time the total is calculated I need to
insert that total into an excel spreadsheet cell, A2 for instance.
The next time the total would be written to cell A3 and so on. Here
is how I create the spreadsheet:

oExcel = New Microsoft.Office.Interop.Excel.Application
oBook = oExcel.Workbooks.Add
oSheet = CType(oBook.Worksheets(1),
Microsoft.Office.Interop.Excel.Worksheet)

I can't quite figure out how to write each total to a different cell.

[=?Utf-8?B?T2xpdmVyIFRlbGw=?=]

"David Jeavons" wrote:

>...
One other thing to note is that you cannot use Option Strict On with this
type of coding as you are using COM objects and unless I am mistaken there
is no way to utilise the Value property of the cells collection whilst
Option Strict is on. Obviously this could cause some problems as you should
always have Option Strict On so I would suggest that you move this type of
code to a module where it can be called without hindering the rest of your
code.
...

I dont agree with that point ("Option Strict On is not possible"). :-)
Instead of osheet.cells(1,1).value = "Hello World" you can use
osheet.range(osheet.cells(1,1).item).value = "Hello World"

As an alternative, you can use a string to specify a range:
osheet.range("A1").value = "Hello World"

To change the value of cells A1, A2, B1 and B2 in one go you can use another
range like osheet.range("A1", "B2").value = "Hello World"

I totally agree to your point ("Always have Option Strict On !"). :-)

[David Jeavons]

Hi Oliver

Thanks for pointing that out, it didn't occur to me to use the Range method.
To be honest, it has been years since I did any automation with Excel using
the Object Library but when I did I remember not being able to find the
appropriate method that I could use whilst still keeping Option Strict On.

Learn something new everyday :)

--
MVP (Visual Developer - Visual Basic)
"Oliver Tell" <Ol********@discussions.microsoft.comwrote in message
news:6A**********************************@microsof t.com...

"David Jeavons" wrote:

>>...
One other thing to note is that you cannot use Option Strict On with this
type of coding as you are using COM objects and unless I am mistaken
there
is no way to utilise the Value property of the cells collection whilst
Option Strict is on. Obviously this could cause some problems as you
should
always have Option Strict On so I would suggest that you move this type
of
code to a module where it can be called without hindering the rest of
your
code.
...

I dont agree with that point ("Option Strict On is not possible"). :-)
Instead of osheet.cells(1,1).value = "Hello World" you can use
osheet.range(osheet.cells(1,1).item).value = "Hello World"

As an alternative, you can use a string to specify a range:
osheet.range("A1").value = "Hello World"

To change the value of cells A1, A2, B1 and B2 in one go you can use
another
range like osheet.range("A1", "B2").value = "Hello World"

I totally agree to your point ("Always have Option Strict On !"). :-)

[spowel4]

David, thank you for your example, that worked for me.