Using Interop for excel not working and causing errors

  • (2 Pages)
  • +
  • 1
  • 2

20 Replies - 1049 Views - Last Post: 06 July 2012 - 11:23 AM Rate Topic: -----

#1 habydlg  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 25
  • Joined: 25-June 12

Using Interop for excel not working and causing errors

Posted 28 June 2012 - 11:06 AM

This is quite a pickle... I am using interop to read and write in excel but the project is not reading the excel correctly, Here I leave the code for you guys.. hope it helps!

This is in a public Module
    Public name, accessories, comments as string
    Public edit As Object
    Public xlApp As New excel.Application
    Public xlWorkBook As excel.Workbook
    Public xlWorkSheet As excel.Worksheet
    Public Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Module



and this is when the form is activated

        xlWorkBook = xlApp.Workbooks.Open("C:\Users\DF\Desktop\exceltest.xls")
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")
        xlApp.Visible = False
        name = xlWorkSheet.Cells(Val(product), 2)
        accessories = xlWorkSheet.Cells(Val(product), 5)
        comments = xlWorkSheet.Cells(Val(product), 15)
        nametxtbx.Text = name
        accessoriestxtbx.Text = accessories
        commentstxtbx.Text = comments
        previewpicbx.Load("C:\Users\DF\Desktop\pictures\" + ((product).Trim) + ".jpg")
        xlWorkBook.Close()



None of the textboxes show text and the preview picturebox does not work... Do I need any resources besides the excel object library 14.0 and the visual basic external applications 5.3?

By the way, product is a number in a textbox

Is This A Good Question/Topic? 0
  • +

Replies To: Using Interop for excel not working and causing errors

#2 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5316
  • View blog
  • Posts: 11,355
  • Joined: 02-June 10

Re: Using Interop for excel not working and causing errors

Posted 28 June 2012 - 12:46 PM

Quote

By the way, product is a number in a textbox

That could be your problem right there. TextBoxes don't contain numbers. They contain text. "5" is not a number. If you aren't converting that text into a number before using it for your array index it's pointless.


Q: I do x and y happens which I didn't expect but I don't know how to figure out why. How do I debug and find my problem?
A:

This post has been edited by tlhIn`toq: 28 June 2012 - 12:47 PM

Was This Post Helpful? 1
  • +
  • -

#3 habydlg  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 25
  • Joined: 25-June 12

Re: Using Interop for excel not working and causing errors

Posted 28 June 2012 - 02:10 PM

Thanks! it was a .Value that was missing!
Was This Post Helpful? 0
  • +
  • -

#4 habydlg  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 25
  • Joined: 25-June 12

Re: Using Interop for excel not working and causing errors

Posted 29 June 2012 - 07:27 AM

Found the answer. After xlWorkSheet.Cells(Val(product), 5) I was missing a .value
Was This Post Helpful? 0
  • +
  • -

#5 m_wylie85  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 96
  • View blog
  • Posts: 899
  • Joined: 15-October 10

Re: Using Interop for excel not working and causing errors

Posted 29 June 2012 - 07:39 AM

Once you run you program, open up task manager and check that the excel app has been closed correctly as this is an issue most people face. As if you don't release the com object correctly it stays open in the background. Just check to be sure

That com object in task manager will most likly be called : Excel.exe

This post has been edited by m_wylie85: 29 June 2012 - 07:40 AM

Was This Post Helpful? 1
  • +
  • -

#6 habydlg  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 25
  • Joined: 25-June 12

Re: Using Interop for excel not working and causing errors

Posted 02 July 2012 - 08:35 AM

View Postm_wylie85, on 29 June 2012 - 07:39 AM, said:

Once you run you program, open up task manager and check that the excel app has been closed correctly as this is an issue most people face. As if you don't release the com object correctly it stays open in the background. Just check to be sure

That com object in task manager will most likly be called : Excel.exe


You are right! I haven't released the com objects correctly... I used this and it does not work...

    Public Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub



This is in a module
Was This Post Helpful? 0
  • +
  • -

#7 habydlg  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 25
  • Joined: 25-June 12

Re: Using Interop for excel not working and causing errors

Posted 02 July 2012 - 09:58 AM

Greetings fellow users and moderators.

I am using interop to write to excel but it seems that my project isn't writing that well into the excel file. I want to change a cell with "N" to "D" but the coding isn't right.

                ElseIf check = "N" Then
                    xlWorkSheet.Cells(Int(Listnum.Items(numrent2)), 6) = "D"
                End If



The listbox contains the row number in a string and changes it to integer.

Afterwards, I am not releasing my COM the right way... I've been informed that my release object is wrong.

        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try


Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8366
  • View blog
  • Posts: 31,088
  • Joined: 12-June 08

Re: Using Interop for excel not working and causing errors

Posted 02 July 2012 - 10:00 AM

Please do not open duplicate or triplicate topics.
Was This Post Helpful? 0
  • +
  • -

#9 habydlg  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 25
  • Joined: 25-June 12

Re: Using Interop for excel not working and causing errors

Posted 02 July 2012 - 10:05 AM

whoa my bad, thought this was a new topic. Never gonna happen again

[edit]
Sorry! I'm kinda new here...

This post has been edited by habydlg: 02 July 2012 - 10:13 AM

Was This Post Helpful? 0
  • +
  • -

#10 habydlg  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 25
  • Joined: 25-June 12

Re: Using Interop for excel not working and causing errors

Posted 02 July 2012 - 10:06 AM

Greetings fellow users and moderators.

I am using interop to write to excel but it seems that my project isn't writing that well into the excel file. I want to change a cell with "N" to "D" but the coding isn't right.

ElseIf check = "N" Then

    xlWorkSheet.Cells(Int(Listnum.Items(numrent2)), 6) = "D"

End If



The listbox contains the row number in a string and changes it to integer.

Afterwards, I am not releasing my COM the right way... I've been informed that my release object is wrong.

Try

    System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)

    obj = Nothing

Catch ex As Exception

    obj = Nothing

Finally

    GC.Collect()

End Try


Was This Post Helpful? 0
  • +
  • -

#11 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8366
  • View blog
  • Posts: 31,088
  • Joined: 12-June 08

Re: Using Interop for excel not working and causing errors

Posted 02 July 2012 - 10:08 AM

No.. I mean do *NOT* open up duplicate topics. As in you already have a thread for your interop issues - keep it together.
Was This Post Helpful? 0
  • +
  • -

#12 habydlg  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 25
  • Joined: 25-June 12

Re: Using Interop for excel not working and causing errors

Posted 02 July 2012 - 10:09 AM

Oh, my bad, well If you wish you can delete this topic.
Was This Post Helpful? 0
  • +
  • -

#13 habydlg  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 25
  • Joined: 25-June 12

Re: Using Interop for excel not working and causing errors

Posted 02 July 2012 - 10:12 AM

Now I understand how this works ;) Never going to make this same mistake! sorry!

Now, does anyone know the right way to release the COM objects and why interop isn't writing into the excel file correctly? It seems to identify when its "N" and "D" but when it tries to write, it does not do anything, maybe its the COM objects not being released on previous debugging?
Was This Post Helpful? 0
  • +
  • -

#14 habydlg  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 25
  • Joined: 25-June 12

Re: Using Interop for excel not working and causing errors

Posted 02 July 2012 - 10:42 AM

I've found the answer! it was the wrong order of my object releasing!
            releaseObject(xlWorkSheet)
            releaseObject(xlWorkBook)
            releaseObject(xlApp)


instead of

            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)

Was This Post Helpful? 0
  • +
  • -

#15 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8366
  • View blog
  • Posts: 31,088
  • Joined: 12-June 08

Re: Using Interop for excel not working and causing errors

Posted 02 July 2012 - 11:14 AM

Kudos on the find!
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2