14 Replies - 316 Views - Last Post: 15 May 2019 - 03:46 PM Rate Topic: -----

#1 diablo21   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 18-June 15

Problem with code Excel

Posted 14 May 2019 - 01:51 PM

Hello i'm having a problem with my code.
App crash, but creates the file csv format, but also the process is not closed in task manager.
Can someone help me where is wrong in code or tell me how to fix it to work normally.

Imports Microsoft.Office.Interop
Imports System.IO

Public Class ConvertXLS
    Private Sub ConvertXLS_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub

    Private Sub button1_Click(sender As Object, e As EventArgs) Handles button1.Click
        Dim fso, inputFile, outputFile
        Dim str As String
        fso = CreateObject("Scripting.FileSystemObject")
        Dim sInfo As New ProcessStartInfo
        Dim excelApplication As New Microsoft.Office.Interop.Excel.Application
        Dim excelWrkBook As Microsoft.Office.Interop.Excel.Workbook
        sInfo.CreateNoWindow = False
        With excelApplication
            .Visible = False
            .DisplayAlerts = False
        End With
        Try
            excelWrkBook = excelApplication.Workbooks.Open("D:\book1.xlsx")
            excelWrkBook.SaveAs(Filename:="D:\book1.csv", FileFormat:=Microsoft.Office.Interop.Excel.XlFileFormat.xlTextMSDOS)
            excelWrkBook.Close()
            excelApplication.DisplayAlerts = True
            excelApplication.Quit()
            inputFile = fso.OpenTextFile("D:\book1.csv", 1)
            str = inputFile.ReadAll
            str = Replace(str, vbTab, ",")
            outputFile = fso.CreateTextFile("D:\book1.csv", True)
            outputFile.Write(str)

            excelWrkBook.Close()
            excelApplication.Quit()

            ReleaseObject(excelApplication)
            ReleaseObject(excelWrkBook)
        Catch ex As Exception
            MsgBox("Error: " & ex.ToString, MsgBoxStyle.Critical, "Error!")
        End Try

    End Sub

    Private Sub ReleaseObject(ByVal obj As Object)
        Try
            Dim intRel As Integer = 0
            Do
                intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            Loop While intRel > 0
            MsgBox("Final Released obj # " & intRel)
        Catch ex As Exception
            MsgBox("Error releasing object" & ex.ToString)
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class



Is This A Good Question/Topic? 0
  • +

Replies To: Problem with code Excel

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14994
  • View blog
  • Posts: 59,870
  • Joined: 12-June 08

Re: Problem with code Excel

Posted 14 May 2019 - 01:59 PM

What's the error message?

Have you considered '.dispose' or the 'using block'?
https://docs.microso...using-statement
Was This Post Helpful? 0
  • +
  • -

#3 diablo21   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 18-June 15

Re: Problem with code Excel

Posted 14 May 2019 - 02:33 PM

this is the message
Attached Image
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14994
  • View blog
  • Posts: 59,870
  • Joined: 12-June 08

Re: Problem with code Excel

Posted 14 May 2019 - 02:37 PM

I would suggest trying the 'using' or 'dispose'.
Was This Post Helpful? 0
  • +
  • -

#5 diablo21   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 18-June 15

Re: Problem with code Excel

Posted 14 May 2019 - 02:43 PM

what do you mean ? sorry i just cant catch up what you mean cause im still learning, also im trying searching for stuff with excel but no results cant know exactly how should become to solve it cause even in task manager processes are still running
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14994
  • View blog
  • Posts: 59,870
  • Joined: 12-June 08

Re: Problem with code Excel

Posted 14 May 2019 - 02:56 PM

I gave you a link on the 'using'. It's a method of disposing of objects when done.
Was This Post Helpful? 0
  • +
  • -

#7 maceysoftware   User is offline

  • Member Title
  • member icon

Reputation: 370
  • View blog
  • Posts: 1,600
  • Joined: 07-September 13

Re: Problem with code Excel

Posted 15 May 2019 - 09:29 AM

I am guessing here...

But in the code snippet you have provided, look at lines 24 and 26, now look at lines 33 and 34.

Your closing the workbook and quitting the application twice, perhaps you need to take this down to once? I could be wrong, but the exception message points to the same sort of thing.
Was This Post Helpful? 0
  • +
  • -

#8 diablo21   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 18-June 15

Re: Problem with code Excel

Posted 15 May 2019 - 10:11 AM

yes the first one is to create the file then close, then open the file and make it comma , combined then close it again.
if there is other way to make this function can someone suggest better way of doing it
Was This Post Helpful? 0
  • +
  • -

#9 maceysoftware   User is offline

  • Member Title
  • member icon

Reputation: 370
  • View blog
  • Posts: 1,600
  • Joined: 07-September 13

Re: Problem with code Excel

Posted 15 May 2019 - 10:16 AM

Please re-read your code, particularly between line 26 and 33, you never re-open your excel workbook, so again, you are calling Close on a already closed object excel workbook.

Changed Confusing statements about excel document and excel worksheet to mention workbook instead.

This post has been edited by maceysoftware: 15 May 2019 - 10:24 AM

Was This Post Helpful? 0
  • +
  • -

#10 maceysoftware   User is offline

  • Member Title
  • member icon

Reputation: 370
  • View blog
  • Posts: 1,600
  • Joined: 07-September 13

Re: Problem with code Excel

Posted 15 May 2019 - 10:30 AM

Basically, what I mean by this is:

You create your excel workbook object
You save your excel workbook object
You close your excel workbook object
You open filestream and read in the contents of a excel file
You modify and write back contents to the excel file, again using file steam.

At this point your excel objects are still closed and the application has been quitted ~At least as far as the objects are concerned, as for while the applications are staying alive in task manager that is a different issue~

You then attempt to close a excel object, as mentioned before your excel object is closed and I suspect because of that the application crashes with the error your experiencing.
Was This Post Helpful? 0
  • +
  • -

#11 diablo21   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 18-June 15

Re: Problem with code Excel

Posted 15 May 2019 - 10:52 AM

so basicly the code will not work, then some other solution how to make it ?
Was This Post Helpful? 0
  • +
  • -

#12 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14994
  • View blog
  • Posts: 59,870
  • Joined: 12-June 08

Re: Problem with code Excel

Posted 15 May 2019 - 11:06 AM

Be careful about asking people to give you the code, and doing your work for you.
Was This Post Helpful? 0
  • +
  • -

#13 maceysoftware   User is offline

  • Member Title
  • member icon

Reputation: 370
  • View blog
  • Posts: 1,600
  • Joined: 07-September 13

Re: Problem with code Excel

Posted 15 May 2019 - 11:08 AM

Well to stop it crashing then remove the duplication of the closing of the workbook and quiting of the excel application.
Was This Post Helpful? 0
  • +
  • -

#14 diablo21   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 18-June 15

Re: Problem with code Excel

Posted 15 May 2019 - 12:46 PM

already try this still the same, also i had removed the try option and still the same, also i try with remove the end releaseobject and use other way but still not good.
Was This Post Helpful? 0
  • +
  • -

#15 Sheepings   User is offline

  • Senior Programmer
  • member icon

Reputation: 223
  • View blog
  • Posts: 1,260
  • Joined: 05-December 13

Re: Problem with code Excel

Posted 15 May 2019 - 03:46 PM

Instead of opening and closing the workbook, you can try utilizing using blocks (using statement) on your workbook and whatever you need to do with it while opened with a using statement can be done in that statement. When using completes, the blocks close and dispose that code and any objects within. Try it.

Find a link for using blocks under my VB.NET signature.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1