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.

 

[David Jeavons]

Hi

To write data to a cell using the Excel Object Library you need to reference
the Cells collection and specify which row and column number to write too.
For example:

<code>
oSheet.Cells(1, 1).value = "Hello World"
</code>

will write Hello World to cell A1.

In order to do what you require you will need to keep a reference to the
rowNumber that data has just been wrote too and then increment this row
number. So for example:

<code>
Dim rowNumber As Int32 = 2

For i As Int32 = 1 To 10
oSheet.Cells(rowNumber, 1).Value = i.ToString
rowNumber += 1
Next
</code>

will write the values 1 to 10 to cells A2 to A11.

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.


HTH

 

[Guest]

...
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

 

[spowel4]

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