Results 1 to 29 of 29

Thread: [RESOLVED] System,Runtime.InterloopServices.COMException when writing to excel

  1. Jul 8th, 2016, 08:23 PM #1
    toecutter
    toecutter is offline

    Thread Starter
    Frenzied Member toecutter's Avatar
    Join Date
    Apr 2006
    Location
    Brisbane, Australia
    Posts
    1,160

    Resolved [RESOLVED] System,Runtime.InterloopServices.COMException when writing to excel

    I have been using below code for 10 years with no issues.

    One user is getting error in below image when trying to export list items to excel, he was using a student/home license so we download a 2016 trial and same issue.



    Although if i delete the items in the list it will write the hard coded text below this comment without any issues "'add accessories"

    Code:
    Option Explicit On
Option Strict Off

Imports System.Xml
Imports System
Imports System.Drawing
Imports System.Drawing.Drawing2D
Imports System.Collections
Imports System.ComponentModel
Imports System.Windows.Forms
Imports System.Data
Imports System.IO
Imports System.Text.RegularExpressions
Imports System.Windows.Forms.ListViewItem
    Code:
        Inherits System.Windows.Forms.Form
    Private moApp As Object
    Dim moSheet1 As Object
    Dim moSheet2 As Object
    Code:
        Public Sub ExportToExcel()
        Try
            Dim i As Integer
            Dim i2 As Integer
            Dim row As Integer, col As Integer
            moApp = CreateObject("Excel.Application")
            moApp.ScreenUpdating = False
            moApp.EnableEvents = False
            moApp.DisplayAlerts = False
            moApp = moApp.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\xxx\Template.xlt", True) ', OverWriteFiles) ', as excel.workbook
            moSheet1 = moApp.worksheets(1)
            moSheet2 = moApp.worksheets(2)
            row = 1
            col = 1
            moSheet2.Cells(row, col) = Console.lblJobNo.Text
            col = 1
            row = row + 1
            Dim dDircetory As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\xxx\"
            '************************************************************************************************************************
            row = 3
            col = 1
            moSheet1.Cells(row, col) = Console.lblJobNo.Text
            col = 1
            row = row + 1
            With Me.lstTakeoffList
                Dim qQty As Integer = 4
                For i = 0 To .Items.Count - 1
                    col = 1
                    moSheet1.Cells(row, col) = .Items(i).Text
                    col = col + 1
                    moSheet1.Cells(row, col) = .Items(i).SubItems(1).Text
                    col = col + 1
                    moSheet1.Cells(row, col) = .Items(i).SubItems(2).Text
                    col = col + 1
                    moSheet1.Cells(row, col) = .Items(i).SubItems(3).Text
                    col = col + 1
                    moSheet1.Cells(row, col) = .Items(i).SubItems(4).Text
                    col = col + 1
                    moSheet1.Cells(row, col) = .Items(i).SubItems(5).Text
                    col = col + 1
                    moSheet1.Cells(row, col) = .Items(i).SubItems(6).Text
                    col = col + 1
                    '******************************************************************
                    '23/02/2016   remove $ sign
                    'H
                    Dim lLabourCostFormulaToEnter As String = Nothing
                    lLabourCostFormulaToEnter = "=IF(ISNA(VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,2,FALSE)),"""",VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,2,FALSE))" '.ToString
                    moSheet1.Cells(row, col).Formula = lLabourCostFormulaToEnter
                    col = col + 1
                    'I
                    Dim mMaterialCostFormulaToEnter As String = Nothing
                    mMaterialCostFormulaToEnter = "=IF(ISNA(VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,3,FALSE)),"""",VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,3,FALSE))" '.ToString
                    moSheet1.Cells(row, col).Formula = mMaterialCostFormulaToEnter
                    col = col + 1
                    'J
                    Dim lLabourPriceFormulaToEnter As String = Nothing
                    lLabourPriceFormulaToEnter = "=IF(F" & qQty & "="""", """",F" & qQty & "*H" & qQty & ")"
                    moSheet1.Cells(row, col).Formula = lLabourPriceFormulaToEnter
                    col = col + 1
                    'K
                    Dim mMaterialPriceFormulaToEnter As String = Nothing
                    mMaterialPriceFormulaToEnter = "=IF(F" & qQty & "="""", """",F" & qQty & "*I" & qQty & ")"
                    moSheet1.Cells(row, col).Formula = mMaterialPriceFormulaToEnter
                    col = col + 1
                    'L
                    Dim tTotalCostFormulaToEnter As String = Nothing
                    tTotalCostFormulaToEnter = "=IF(F" & qQty & "="""", """",J" & qQty & "+K" & qQty & ")"
                    moSheet1.Cells(row, col).Formula = tTotalCostFormulaToEnter
                    col = col + 1
                    row = row + 1
                    qQty = qQty + 1
                Next
                'add assessoreies
                If chkAss.Checked Then
                    col = 1
                    row = row + 1
                    moSheet1.Cells(row, col) = "Assesories [rivert/silicon]"
                    col = col + 6
                    moSheet1.Cells(row, col) = "no"
                    col = 1
                    row = row + 1
                    moSheet1.Cells(row, col) = "Deliveries"
                    col = col + 6
                    moSheet1.Cells(row, col) = "no"
                    col = 1
                    row = row + 1
                    moSheet1.Cells(row, col) = "Supervision"
                    col = col + 6
                    moSheet1.Cells(row, col) = "no"
                    col = 1
                    row = row + 1
                    moSheet1.Cells(row, col) = "Waste"
                    col = col + 6
                    moSheet1.Cells(row, col) = "no"
                    col = 1
                    row = row + 1
                    moSheet1.Cells(row, col) = "Height Allowance"
                    col = col + 6
                    moSheet1.Cells(row, col) = "m2"
                End If
            End With
  End Sub
    I checked if the 2.0 and 3.5 frameworks were enabled and they are.

    It appears to the way the formulas are being written to excel?

    Any ideas what is causing this error
    Reply With Quote Reply With Quote
  2. Jul 9th, 2016, 12:48 PM #2
    KGComputers
    KGComputers is offline
    Frenzied Member KGComputers's Avatar
    Join Date
    Dec 2005
    Location
    Cebu, PH
    Posts
    2,024

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    You have Option Strict Off set in your project, change it to Option Strict On since the issue provided describes LateBinding. Then check which line(s) causes the issue.
    CodeBank: VB.NET & C#.NET | ASP.NET
    Programming: C# | VB.NET
    Blogs: Personal | Programming
    Projects: GitHub | jsFiddle
    ___________________________________________________________________________________

    Rating someone's post is a way of saying Thanks...
    Reply With Quote Reply With Quote
  3. Jul 9th, 2016, 01:10 PM #3
    wes4dbt
    wes4dbt is offline
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,518

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    Quote Originally Posted by KGComputers View Post
    You have Option Strict Off set in your project, change it to Option Strict On since the issue provided describes LateBinding. Then check which line(s) causes the issue.
    He's doing late binding, you can't do that with Option Strict On.
    Reply With Quote Reply With Quote
  4. Jul 9th, 2016, 01:15 PM #4
    wes4dbt
    wes4dbt is offline
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,518

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    Wait a second,
    Code:
        Public Sub ExportToExcel()
        Try
            Dim i As Integer
            Dim i2 As Integer
            Dim row As Integer, col As Integer
            moApp = CreateObject("Excel.Application")
            moApp.ScreenUpdating = False
            moApp.EnableEvents = False
            moApp.DisplayAlerts = False
            moApp = moApp.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\xxx\Template.xlt", True) ', OverWriteFiles) ', as excel.workbook
            moSheet1 = moApp.worksheets(1)
            moSheet2 = moApp.worksheets(2)
            row = 1
            col = 1
            moSheet2.Cells(row, col) = Console.lblJobNo.Text
            col = 1
            row = row + 1
            Dim dDircetory As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\xxx\"
            '************************************************************************************************************************
            row = 3
            col = 1
            moSheet1.Cells(row, col) = Console.lblJobNo.Text
            col = 1
            row = row + 1
            With Me.lstTakeoffList
                Dim qQty As Integer = 4
                For i = 0 To .Items.Count - 1
                    col = 1
                    moSheet1.Cells(row, col) = .Items(i).Text
                    col = col + 1
                    moSheet1.Cells(row, col) = .Items(i).SubItems(1).Text
                    col = col + 1
                    moSheet1.Cells(row, col) = .Items(i).SubItems(2).Text
                    col = col + 1
                    moSheet1.Cells(row, col) = .Items(i).SubItems(3).Text
                    col = col + 1
                    moSheet1.Cells(row, col) = .Items(i).SubItems(4).Text
                    col = col + 1
                    moSheet1.Cells(row, col) = .Items(i).SubItems(5).Text
                    col = col + 1
                    moSheet1.Cells(row, col) = .Items(i).SubItems(6).Text
                    col = col + 1
                    '******************************************************************
                    '23/02/2016   remove $ sign
                    'H
                    Dim lLabourCostFormulaToEnter As String = Nothing
                    lLabourCostFormulaToEnter = "=IF(ISNA(VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,2,FALSE)),"""",VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,2,FALSE))" '.ToString
                    moSheet1.Cells(row, col).Formula = lLabourCostFormulaToEnter
                    col = col + 1
                    'I
                    Dim mMaterialCostFormulaToEnter As String = Nothing
                    mMaterialCostFormulaToEnter = "=IF(ISNA(VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,3,FALSE)),"""",VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,3,FALSE))" '.ToString
                    moSheet1.Cells(row, col).Formula = mMaterialCostFormulaToEnter
                    col = col + 1
                    'J
                    Dim lLabourPriceFormulaToEnter As String = Nothing
                    lLabourPriceFormulaToEnter = "=IF(F" & qQty & "="""", """",F" & qQty & "*H" & qQty & ")"
                    moSheet1.Cells(row, col).Formula = lLabourPriceFormulaToEnter
                    col = col + 1
                    'K
                    Dim mMaterialPriceFormulaToEnter As String = Nothing
                    mMaterialPriceFormulaToEnter = "=IF(F" & qQty & "="""", """",F" & qQty & "*I" & qQty & ")"
                    moSheet1.Cells(row, col).Formula = mMaterialPriceFormulaToEnter
                    col = col + 1
                    'L
                    Dim tTotalCostFormulaToEnter As String = Nothing
                    tTotalCostFormulaToEnter = "=IF(F" & qQty & "="""", """",J" & qQty & "+K" & qQty & ")"
                    moSheet1.Cells(row, col).Formula = tTotalCostFormulaToEnter
                    col = col + 1
                    row = row + 1
                    qQty = qQty + 1
                Next
                'add assessoreies
                If chkAss.Checked Then
                    col = 1
                    row = row + 1
                    moSheet1.Cells(row, col) = "Assesories [rivert/silicon]"
                    col = col + 6
                    moSheet1.Cells(row, col) = "no"
                    col = 1
                    row = row + 1
                    moSheet1.Cells(row, col) = "Deliveries"
                    col = col + 6
                    moSheet1.Cells(row, col) = "no"
                    col = 1
                    row = row + 1
                    moSheet1.Cells(row, col) = "Supervision"
                    col = col + 6
                    moSheet1.Cells(row, col) = "no"
                    col = 1
                    row = row + 1
                    moSheet1.Cells(row, col) = "Waste"
                    col = col + 6
                    moSheet1.Cells(row, col) = "no"
                    col = 1
                    row = row + 1
                    moSheet1.Cells(row, col) = "Height Allowance"
                    col = col + 6
                    moSheet1.Cells(row, col) = "m2"
                End If
            End With
  End Sub
    You have a "Try", but no Catch or End Try. This can't be your real code.
    Reply With Quote Reply With Quote
  6. Jul 9th, 2016, 02:03 PM #5
    toecutter
    toecutter is offline

    Thread Starter
    Frenzied Member toecutter's Avatar
    Join Date
    Apr 2006
    Location
    Brisbane, Australia
    Posts
    1,160

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    Hi wes4dbt,

    Yes sorry about that, there is another 60 lines of code i didn't need to post, i just moved the end sub without checking....

    Can you see why the first part of the code would only fail on that one W10 pc?

    Code:
           moApp.Application.Visible = True
            moApp.Application.ScreenUpdating = True
        Catch ex As Exception
            MsgBox(ex.ToString)
            MessageBox.Show("If you are certain you have excel installed" & Environment.NewLine & "there is a issue, contact Mick." & Environment.NewLine & "If you dont have excel installed you will need to install it", "Software cannot be started")
        End Try
    Reply With Quote Reply With Quote
  7. Jul 9th, 2016, 03:52 PM #6
    wes4dbt
    wes4dbt is offline
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,518

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    Which line is throwing the error? What is you "Target CPU", x86, x64, Any Cpu?

    If the error doesn't show which line of code is causing the error, then comment out the Try/Catch and run it. that should cause the problem line to be highlighted. If it's only one machine then most likely it's the data or the Excel but no way to no without knowing which line causes the error.

    After looking at your code, you do have some hard wired locations that could cause errors on only one machine if they don't exist.
    My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\xxx\Template.xlt
    My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\xxx\
    Last edited by wes4dbt; Jul 9th, 2016 at 04:36 PM.
    Reply With Quote Reply With Quote
  8. Jul 9th, 2016, 04:50 PM #7
    toecutter
    toecutter is offline

    Thread Starter
    Frenzied Member toecutter's Avatar
    Join Date
    Apr 2006
    Location
    Brisbane, Australia
    Posts
    1,160

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    Target CPU = x86,

    Prerequisites = windows installer 3.1
    3.5
    3.5 SP1

    I have compiled with numbered messagesbox's and sent to user, i will TeamVeiwer in and check it out when he comes on line...

    thx


    Code:
    Public Sub ExportToExcel()

        MessageBox.Show("1")
        'Try
        Dim i As Integer
        Dim i2 As Integer
        Dim row As Integer, col As Integer
        moApp = CreateObject("Excel.Application")
        MessageBox.Show("2")
        moApp.ScreenUpdating = False
        moApp.EnableEvents = False
        moApp.DisplayAlerts = False
        moApp = moApp.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\TakeoffExpress\Template.xlt", True) ', OverWriteFiles) ', as excel.workbook
        moSheet1 = moApp.worksheets(1)
        moSheet2 = moApp.worksheets(2)
        MessageBox.Show("3")
        row = 1
        col = 1
        moSheet2.Cells(row, col) = Console.lblJobNo.Text
        col = 1
        row = row + 1
        Dim dDircetory As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\TakeoffExpress\"
        '************************************************************************************************************************
        MessageBox.Show("4")
        row = 3
        col = 1
        moSheet1.Cells(row, col) = Console.lblJobNo.Text
        col = 1
        row = row + 1
        MessageBox.Show("5")
        With Me.lstTakeoffList
            Dim qQty As Integer = 4
            For i = 0 To .Items.Count - 1
                col = 1
                moSheet1.Cells(row, col) = .Items(i).Text
                col = col + 1
                moSheet1.Cells(row, col) = .Items(i).SubItems(1).Text
                col = col + 1
                moSheet1.Cells(row, col) = .Items(i).SubItems(2).Text
                col = col + 1
                moSheet1.Cells(row, col) = .Items(i).SubItems(3).Text
                col = col + 1
                moSheet1.Cells(row, col) = .Items(i).SubItems(4).Text
                col = col + 1
                moSheet1.Cells(row, col) = .Items(i).SubItems(5).Text
                col = col + 1
                moSheet1.Cells(row, col) = .Items(i).SubItems(6).Text
                col = col + 1
                '******************************************************************
                '23/02/2016   remove $ sign
                'H
                Dim lLabourCostFormulaToEnter As String = Nothing
                lLabourCostFormulaToEnter = "=IF(ISNA(VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,2,FALSE)),"""",VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,2,FALSE))" '.ToString
                moSheet1.Cells(row, col).Formula = lLabourCostFormulaToEnter
                col = col + 1
                'I
                Dim mMaterialCostFormulaToEnter As String = Nothing
                mMaterialCostFormulaToEnter = "=IF(ISNA(VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,3,FALSE)),"""",VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,3,FALSE))" '.ToString
                moSheet1.Cells(row, col).Formula = mMaterialCostFormulaToEnter
                col = col + 1
                'J
                Dim lLabourPriceFormulaToEnter As String = Nothing
                lLabourPriceFormulaToEnter = "=IF(F" & qQty & "="""", """",F" & qQty & "*H" & qQty & ")"
                moSheet1.Cells(row, col).Formula = lLabourPriceFormulaToEnter
                col = col + 1
                'K
                Dim mMaterialPriceFormulaToEnter As String = Nothing
                mMaterialPriceFormulaToEnter = "=IF(F" & qQty & "="""", """",F" & qQty & "*I" & qQty & ")"
                moSheet1.Cells(row, col).Formula = mMaterialPriceFormulaToEnter
                col = col + 1
                'L
                Dim tTotalCostFormulaToEnter As String = Nothing
                tTotalCostFormulaToEnter = "=IF(F" & qQty & "="""", """",J" & qQty & "+K" & qQty & ")"
                moSheet1.Cells(row, col).Formula = tTotalCostFormulaToEnter
                col = col + 1
                row = row + 1
                qQty = qQty + 1
            Next
            MessageBox.Show("6")
            'add accessories
            If chkAss.Checked Then
    ps, the software creates them DIR's when started....
    Last edited by toecutter; Jul 9th, 2016 at 05:08 PM.
    Reply With Quote Reply With Quote
  9. Jul 9th, 2016, 04:59 PM #8
    wes4dbt
    wes4dbt is offline
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,518

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    Target CPU = x86, Any Cpu?
    To find Project->Properties->Compile->Advanced Settings

    I'm using VS 2010 Pro
    Reply With Quote Reply With Quote
  11. Jul 9th, 2016, 05:07 PM #9
    toecutter
    toecutter is offline

    Thread Starter
    Frenzied Member toecutter's Avatar
    Join Date
    Apr 2006
    Location
    Brisbane, Australia
    Posts
    1,160

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    VS2008
    x86
    Reply With Quote Reply With Quote
  12. Jul 9th, 2016, 06:19 PM #10
    wes4dbt
    wes4dbt is offline
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,518

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    Does the problem machine have an x86 version of Excel. Even though your late binding, your still targeting x86. I THINK?? I'm sure there are people here that know more than me about this subject.
    Reply With Quote Reply With Quote
  13. Jul 9th, 2016, 06:22 PM #11
    toecutter
    toecutter is offline

    Thread Starter
    Frenzied Member toecutter's Avatar
    Join Date
    Apr 2006
    Location
    Brisbane, Australia
    Posts
    1,160

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    will check, doesn't the 3.5 FW handle that?
    Reply With Quote Reply With Quote
  14. Jul 9th, 2016, 06:35 PM #12
    wes4dbt
    wes4dbt is offline
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,518

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    Quote Originally Posted by toecutter View Post
    will check, doesn't the 3.5 FW handle that?
    Like I said, I'm not sure. Hopefully someone else will chime in with a definite answer.
    Reply With Quote Reply With Quote
  15. Jul 9th, 2016, 07:15 PM #13
    dbasnett
    dbasnett is offline
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,897

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    Take a look at this, it might help https://social.msdn.microsoft.com/Fo...orum=vbgeneral
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<
    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein
    Reply With Quote Reply With Quote
  16. Jul 10th, 2016, 09:50 PM #14
    TnTinMN
    TnTinMN is offline
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    This is probably not the source of the COM error, but your VLOOKUP syntax looks strange.
    Quote Originally Posted by toecutter View Post
    Code:
    lLabourCostFormulaToEnter = "=IF(ISNA(VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,2,FALSE)),"""",VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,2,FALSE))"
    Syntax
    VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
    You are using the range "A:A" for the lookup_value argument where I would expect a single value reference. I would expect that to result in a formula error.
    Reply With Quote Reply With Quote
  17. Jul 11th, 2016, 04:17 AM #15
    toecutter
    toecutter is offline

    Thread Starter
    Frenzied Member toecutter's Avatar
    Join Date
    Apr 2006
    Location
    Brisbane, Australia
    Posts
    1,160

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    Hi TnTinMN,

    It is working fine so i have no comment
    Reply With Quote Reply With Quote
  18. Jul 11th, 2016, 04:28 AM #16
    toecutter
    toecutter is offline

    Thread Starter
    Frenzied Member toecutter's Avatar
    Join Date
    Apr 2006
    Location
    Brisbane, Australia
    Posts
    1,160

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    I added numbered messagebox's.

    I have removed the try catch statement.

    Sent a version to user to install.

    Logged into his pc with TeamVeiwer.

    It is producing the error after messagebox 5

    After clicking ok on messagebox 5 i get an unhandled exception message [see below]



    When i click "Continue" it no hangs.

    Code:
    MessageBox.Show("5")
        With Me.lstTakeoffList
            Dim qQty As Integer = 4
            For i = 0 To .Items.Count - 1
                col = 1
                moSheet1.Cells(row, col) = .Items(i).Text
                col = col + 1
                moSheet1.Cells(row, col) = .Items(i).SubItems(1).Text
                col = col + 1
                moSheet1.Cells(row, col) = .Items(i).SubItems(2).Text
                col = col + 1
                moSheet1.Cells(row, col) = .Items(i).SubItems(3).Text
                col = col + 1
                moSheet1.Cells(row, col) = .Items(i).SubItems(4).Text
                col = col + 1
                moSheet1.Cells(row, col) = .Items(i).SubItems(5).Text
                col = col + 1
                moSheet1.Cells(row, col) = .Items(i).SubItems(6).Text
                col = col + 1
                '******************************************************************
                '23/02/2016   remove $ sign
                'H
                Dim lLabourCostFormulaToEnter As String = Nothing
                lLabourCostFormulaToEnter = "=IF(ISNA(VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,2,FALSE)),"""",VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,2,FALSE))" '.ToString
                moSheet1.Cells(row, col).Formula = lLabourCostFormulaToEnter
                col = col + 1
                'I
                Dim mMaterialCostFormulaToEnter As String = Nothing
                mMaterialCostFormulaToEnter = "=IF(ISNA(VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,3,FALSE)),"""",VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,3,FALSE))" '.ToString
                moSheet1.Cells(row, col).Formula = mMaterialCostFormulaToEnter
                col = col + 1
                'J
                Dim lLabourPriceFormulaToEnter As String = Nothing
                lLabourPriceFormulaToEnter = "=IF(F" & qQty & "="""", """",F" & qQty & "*H" & qQty & ")"
                moSheet1.Cells(row, col).Formula = lLabourPriceFormulaToEnter
                col = col + 1
                'K
                Dim mMaterialPriceFormulaToEnter As String = Nothing
                mMaterialPriceFormulaToEnter = "=IF(F" & qQty & "="""", """",F" & qQty & "*I" & qQty & ")"
                moSheet1.Cells(row, col).Formula = mMaterialPriceFormulaToEnter
                col = col + 1
                'L
                Dim tTotalCostFormulaToEnter As String = Nothing
                tTotalCostFormulaToEnter = "=IF(F" & qQty & "="""", """",J" & qQty & "+K" & qQty & ")"
                moSheet1.Cells(row, col).Formula = tTotalCostFormulaToEnter
                col = col + 1
                row = row + 1
                qQty = qQty + 1
            Next
            MessageBox.Show("6")
    I will now add messagebox's in the loop i guess....


    thx
    Reply With Quote Reply With Quote
  19. Jul 11th, 2016, 01:35 PM #17
    wes4dbt
    wes4dbt is offline
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,518

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    You might want to consider writing to a Log file instead of using MessageBox. It seems likely that the problem is the Data, since the Excel app is created and worksheets. You could get more information using a Log file.

    Simple example,
    Code:
    Imports System.IO
Imports System.IO.File
Public Class Form1
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim filename As String = Application.StartupPath & "\test.log"
        Dim sw As StreamWriter = AppendText(filename)
        sw.WriteLine(Now() & " " & "sample log file entry")
        sw.Close()
    End Sub
End Class
    Google "Visual Basic log file" lots of info
    Reply With Quote Reply With Quote
  20. Jul 12th, 2016, 06:09 AM #18
    toecutter
    toecutter is offline

    Thread Starter
    Frenzied Member toecutter's Avatar
    Join Date
    Apr 2006
    Location
    Brisbane, Australia
    Posts
    1,160

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    Code is executing until after mesagebox 17, so it appears to be the VLOOKUP formulas that it is causing the error.

    Code:
                   moSheet1.Cells(row, col) = .Items(i).SubItems(6).Text
                MessageBox.Show("16")
                col = col + 1
                '******************************************************************
                '23/02/2016   remove $ sign
                'H
                MessageBox.Show("17")
                Dim lLabourCostFormulaToEnter As String = Nothing
                lLabourCostFormulaToEnter = "=IF(ISNA(VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,2,FALSE)),"""",VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,2,FALSE))" '.ToString
                moSheet1.Cells(row, col).Formula = lLabourCostFormulaToEnter
                col = col + 1
                MessageBox.Show("18")
                'I
                Dim mMaterialCostFormulaToEnter As String = Nothing
                mMaterialCostFormulaToEnter = "=IF(ISNA(VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,3,FALSE)),"""",VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,3,FALSE))" '.ToString
                moSheet1.Cells(row, col).Formula = mMaterialCostFormulaToEnter
                col = col + 1
                MessageBox.Show("19")
    I tried below and it worked fine on my W7 PC with Excel 2007 but again failed on users W10 PC with office 365
    Code:
    lLabourCostFormulaToEnter = "=IF(ISNA(VLOOKUP(A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,2,FALSE)),"""",VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,2,FALSE))" '.ToString
                moSheet1.Cells(row, col).Formula = lLabourCostFormulaToEnter
    Reply With Quote Reply With Quote
  21. Jul 12th, 2016, 12:11 PM #19
    wes4dbt
    wes4dbt is offline
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,518

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    Have you checked the value of the data involved? dDirectory, sShortenedpPricingSheetName, lLabourCostFormulaToEnter. They might not be what you think. Also, there is an Office Development forum on this site. Maybe now that you have it narrowed down, you should post your question there.
    Reply With Quote Reply With Quote
  22. Jul 12th, 2016, 02:46 PM #20
    wild_bill
    wild_bill is offline
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,993

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    Did you look at DBasnett's link and try the solution?
    1.Create directory "C:\Windows\SysWOW64\config\systemprofile\Desktop " (for 64 bit Windows)
    or "C:\Windows\System32\config\systemprofile\Desktop " (for 32 bit Windows)
    That is the very essence of human beings and our very unique capability to perform complex reasoning and actually use our perception to further our understanding of things. We like to solve problems. -Kleinma

    Does your code in post #46 look like my code in #45? No, it doesn't. Therefore, wrong is how it looks. - jmcilhinney
    Reply With Quote Reply With Quote
  23. Jul 13th, 2016, 04:31 AM #21
    toecutter
    toecutter is offline

    Thread Starter
    Frenzied Member toecutter's Avatar
    Join Date
    Apr 2006
    Location
    Brisbane, Australia
    Posts
    1,160

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    Hi wild_bill,

    Both DIR's were not present, created both and tried and not luck,

    Also tried another suggestion form a Odesk contact re, pasting formula into users excel to check and it worked fine.

    Code:
    =IF(ISNA(VLOOKUP(A:A,'C:\Users\DAVID\Documents\TakeoffExpress\PricingSheet.xls'!A1:D5000,2,FALSE)),"",VLOOKUP(A:A,'C:\Users\DAVID\Documents\TakeoffExpress\PricingSheet.xls'!A1:D5000,2,FALSE))
    Got me stumped....
    Reply With Quote Reply With Quote
  24. Jul 13th, 2016, 04:33 AM #22
    toecutter
    toecutter is offline

    Thread Starter
    Frenzied Member toecutter's Avatar
    Join Date
    Apr 2006
    Location
    Brisbane, Australia
    Posts
    1,160

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    Quote Originally Posted by wes4dbt View Post
    Have you checked the value of the data involved? dDirectory, sShortenedpPricingSheetName, lLabourCostFormulaToEnter. They might not be what you think. Also, there is an Office Development forum on this site. Maybe now that you have it narrowed down, you should post your question there.
    Yes wes4dbt, as mentioned it works fine on hundreds of PC's.

    Thx for the suggestion re the office forums.

    thx
    Reply With Quote Reply With Quote
  25. Jul 13th, 2016, 07:39 AM #23
    dbasnett
    dbasnett is offline
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,897

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    When you deploy this I assume you remove the messagebox.shows. Instead of replacing them change them to

    Threading.Thread.Sleep(20)

    I had weird, only on a handful of PC's, issues when using Word.Interop and the sleeps fixed the issue. Don't remember where I found that 'fix' but it worked, and being where you are now I just accepted it as faith.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<
    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein
    Reply With Quote Reply With Quote
  26. Jul 13th, 2016, 12:15 PM #24
    wes4dbt
    wes4dbt is offline
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,518

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    Quote Originally Posted by toecutter View Post
    Yes wes4dbt, as mentioned it works fine on hundreds of PC's.

    Thx for the suggestion re the office forums.

    thx
    So your saying everyone uses the same data on these hundreds of pc's?
    Reply With Quote Reply With Quote
  27. Jul 13th, 2016, 04:25 PM #25
    toecutter
    toecutter is offline

    Thread Starter
    Frenzied Member toecutter's Avatar
    Join Date
    Apr 2006
    Location
    Brisbane, Australia
    Posts
    1,160

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    Quote Originally Posted by wes4dbt View Post
    So your saying everyone uses the same data on these hundreds of pc's?
    It is deployed with example data then a user can customize, this is a new install using default data so yes.
    Reply With Quote Reply With Quote
  28. Jul 13th, 2016, 04:46 PM #26
    wes4dbt
    wes4dbt is offline
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,518

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    Quote Originally Posted by toecutter View Post
    It is deployed with example data then a user can customize, this is a new install using default data so yes.
    Sorry, got no answer for you. Don't know if you tried this or not, but I Googled "system.runtime.interopservices.comexception (0x800a03ec) excel" got several answers, they might not be the same as yours but that's all I could thing of. Oh, also a few results when I Googled "Microsoft Excel forum"

    Good luck
    Reply With Quote Reply With Quote
  29. Aug 2nd, 2016, 04:05 AM #27
    toecutter
    toecutter is offline

    Thread Starter
    Frenzied Member toecutter's Avatar
    Join Date
    Apr 2006
    Location
    Brisbane, Australia
    Posts
    1,160

    Re: System,Runtime.InterloopServices.COMException when writing to excel

    turns out the users name had a ' so when excel was writing the vlookup formulas to the spreadsheet it wasn't happy...

    thx all for advise.
    Reply With Quote Reply With Quote
  30. Aug 2nd, 2016, 01:07 PM #28
    wes4dbt
    wes4dbt is offline
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,518

    Re: [RESOLVED] System,Runtime.InterloopServices.COMException when writing to excel

    So actually the data wasn't the same as on the hundreds of PC's

    Quote Originally Posted by wes4dbt View Post
    So your saying everyone uses the same data on these hundreds of pc's?
    It is deployed with example data then a user can customize, this is a new install using default data so yes.
    Reply With Quote Reply With Quote
  31. Aug 2nd, 2016, 04:31 PM #29
    toecutter
    toecutter is offline

    Thread Starter
    Frenzied Member toecutter's Avatar
    Join Date
    Apr 2006
    Location
    Brisbane, Australia
    Posts
    1,160

    Re: [RESOLVED] System,Runtime.InterloopServices.COMException when writing to excel

    I didn't expect you would call the address where the data resides data
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules


Click Here to Expand Forum to Full Width