1,105,286 Community Members

looping and changing cells value in excel using VB.NET

Member Avatar
cascade001
Newbie Poster
5 posts since Jun 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

what i am trying to do is make a program to read an excel file that i already have , choose certin columns depending on its header title then paste these columns into sheet2 .
the problem is when i check for the columns i want to extract it gives me this error "Object reference not set to an instance of an object".

this is the code i am using to extract the value of a certin cell while looping throw a row and if the value of the cell = "Sample" it changes the value of another cell with "Pass"..

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.IO

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles StartTesting.Click

    Dim oXL As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oSheet As Excel.Worksheet
   
    
   
' start excel application 
     oXL = New Excel.Application
     oXL.Visible = True

        ' Get a new workbook.
        oWB = oXL.Workbooks.Open(C:\filepath.xls)
        oSheet = oWB.ActiveSheet
       
' just to show me the value of the cell
        MsgBox(oSheet.Cells(1, 1).Value.ToString, MsgBoxStyle.OkOnly)

' starting the loop
        Dim i As Integer = 1
        For i = 1 To 128
            If oSheet.Cells(1, i).Value.ToString = "Sample" Then
                oSheet.Cells(2, 1) = "Pass"
            End If
        Next i

    End Sub

but it does not work :(

thank you for helping

Member Avatar
rapture
Posting Whiz in Training
294 posts since Jul 2007
Reputation Points: 134 [?]
Q&As Helped to Solve: 41 [?]
Skill Endorsements: 0 [?]
 
0
 

I'm not sure myself, but maybe this helps

http://www.thescarms.com/dotnet/ExcelObject.aspx

Member Avatar
cascade001
Newbie Poster
5 posts since Jun 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

I'm not sure myself, but maybe this helps

http://www.thescarms.com/dotnet/ExcelObject.aspx

thank you for your help Rapture that really helped me :)

and yesterday i solved my problem with removing the ".tostring"
for ex

' as you can see you dont state the ".value "nor ".tostring"  after" 
' then" 
if osheet.cell(1,i).value = "Sample" then 
osheet.cell(2,i)= "Pass"
end if 
next i

thanks again

Question Answered as of 5 Years Ago by rapture
Member Avatar
julio.azevedo.562
Newbie Poster
2 posts since Jan 2014
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

I have a graph in my excel file. There is two values that changes this graph appearance, so I want to change these cell values in a FOR loop, in order to make a "animation". Is it work? Every step of the loop will change the graph, but how can I be abke to see it changing? Anyone can help mein this doubt?

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article