-
[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.
http://i354.photobucket.com/albums/r...pslx49wm31.jpg
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
-
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.
-
Re: System,Runtime.InterloopServices.COMException when writing to excel
Quote:
Originally Posted by
KGComputers
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.
-
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.
-
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
-
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\
-
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....
-
Re: System,Runtime.InterloopServices.COMException when writing to excel
Quote:
Target CPU = x86, Any Cpu?
To find Project->Properties->Compile->Advanced Settings
I'm using VS 2010 Pro
-
Re: System,Runtime.InterloopServices.COMException when writing to excel
-
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.
-
Re: System,Runtime.InterloopServices.COMException when writing to excel
will check, doesn't the 3.5 FW handle that?
-
Re: System,Runtime.InterloopServices.COMException when writing to excel
Quote:
Originally Posted by
toecutter
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.
-
Re: System,Runtime.InterloopServices.COMException when writing to excel
-
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
Code:
lLabourCostFormulaToEnter = "=IF(ISNA(VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,2,FALSE)),"""",VLOOKUP(A:A,'" & dDircetory & sShortenedpPricingSheetName & "'!A1:D5000,2,FALSE))"
Quote:
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.
-
Re: System,Runtime.InterloopServices.COMException when writing to excel
Hi TnTinMN,
It is working fine so i have no comment
-
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]
http://i354.photobucket.com/albums/r...pshqingvpp.jpg
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
-
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
-
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
-
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.
-
Re: System,Runtime.InterloopServices.COMException when writing to excel
Did you look at DBasnett's link and try the solution?
Quote:
1.Create directory "C:\Windows\SysWOW64\config\systemprofile\Desktop " (for 64 bit Windows)
or "C:\Windows\System32\config\systemprofile\Desktop " (for 32 bit Windows)
-
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....
-
Re: System,Runtime.InterloopServices.COMException when writing to excel
Quote:
Originally Posted by
wes4dbt
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
-
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.
-
Re: System,Runtime.InterloopServices.COMException when writing to excel
Quote:
Originally Posted by
toecutter
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?
-
Re: System,Runtime.InterloopServices.COMException when writing to excel
Quote:
Originally Posted by
wes4dbt
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.
-
Re: System,Runtime.InterloopServices.COMException when writing to excel
Quote:
Originally Posted by
toecutter
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
-
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.
-
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:
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.
-
Re: [RESOLVED] System,Runtime.InterloopServices.COMException when writing to excel
I didn't expect you would call the address where the data resides data
