[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1389
  • Last Modified:

reading and writing to excel from vb.net

hello experts, I want to read and write to an excel file and it seems to work except the workbook doesnt close and my changes arent always being saved can someone look at my code and see what I am doing wrong?  

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oExcel As Excel.Application
        Dim oWorkBook As Excel.Workbook
        Dim oWorkSheet As Excel.Worksheet
        Dim j As Integer
        j = 1
        oExcel = New Excel.Application
        oWorkBook = oExcel.Workbooks.Open("C:\smallcount\smallframe.xls")
        oWorkSheet = oWorkBook.Worksheets().Item(1)
        oWorkSheet.Activate()
        Dim o As Excel.Range = oWorkSheet.Cells.Item(j, 1)
        Dim s As String = o.Cells.Text
        Do While s <> ""
            j += 1
            o = o.Cells.Item(j, 1)
            s = o.Cells.Text
        Loop
        o.Cells(j, 1).Value = "Testvalue"
        oWorkBook.Application.DisplayAlerts = False
        oWorkBook.Save()
        oWorkBook.Close()
0
tentavarious
Asked:
tentavarious
  • 3
  • 2
1 Solution
 
RichWCommented:
Try killing the application object after you close Excel.

If Not oExcel Is Nothing Then
    oExcel = Nothing
End If

OR

This will kill all processes of Excel.

Try
            Dim currentProcess() As Process = Process.GetProcessesByName("excel")
            Dim proc As Process

            For Each proc In currentProcess
                proc.CloseMainWindow()
                proc.Kill()
            Next
        Catch ex As Exception
            MsgBox("Error closing Excel2: " & ex.Message)
        End Try

I don't know why the save isn't working.  I use the document object and save this way:
Doc.SaveAs("C:\smallcount\smallframe.xls")

I hope this helps.
0
 
tentavariousAuthor Commented:
Still not working when I try to open the sheet I get a message saying its already opened and reopening will cause any saved data to be lost.
0
 
RichWCommented:
Did you try the process method I entered?  That should definitely kill all instances of Excel.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
tentavariousAuthor Commented:
The problem is the save method i am getting this error       Message      "Cannot access read-only document 'smallframe.xls'."      String
0
 
JoncarlCommented:
I get an error with Excel.Application is nto defined!

when using
 Dim oExcel As Excel.Application

in Visual Studio VB 2008
0
 
tentavariousAuthor Commented:
I havent used excel objects in 2008 yet, but almost sounds like your missing a reference.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now