[RESOLVED] Excel COM issues

[morpheus3230]

Hi there,

this is the issue I am facing:

I have written an app that contains a feature which exports datasets to an excel sheet and does all the little formatting things to make my users happy.
Anyway, on my machine, I have Office 2003 installed (which means Excel COM 11.0) so everything works great.
Unfortunately some of my target audience has Office 2000, which means Excel COM 10.0 I believe). So therein lies the problem. As soon as they try to export, error message pops up about access to protected memory etc.
So I thought since I was using early binding, that if I change it to late binding it would work. Unfortunately ... not the case. My guess is that some methods and properties I use are not available in the 10.0 version.

In general...How do people approach this issue? Any advice will be appreciated.
many thanx

[si_the_geek]

What version of VB are you using? (5, 6, .Net, VBA in an Office application,...)

For Classic VB, the steps you need to convert to Late Binding are in a post in my Excel tutorial - I'd recommend reading it to check you haven't missed anything.

[RobDog888]

Since it sounds like your already using late binding I would ask if you removed the reference to Excel 11? Excel 2000 is Excel 9.0 version.

You need to search msdn and verify that all the functions, properties, events etc are existing in 2000 and above.

[disruptivehair]

What version of VB are you using? (5, 6, .Net, VBA in an Office application,...)

For Classic VB, the steps you need to convert to Late Binding are in a post in my Excel tutorial - I'd recommend reading it to check you haven't missed anything.

Hey si, slightly off-topic here, don't want to hijack the thread. I followed your late-binding tutorial and it's working, but I had to change pretty much every Excel function I had because I was passing Excel objects. Y'know, had to change them from Excel.Worksheet to Object. Should I not be passing objects like that? TIA.

[morpheus3230]

I'm using vb.net VS2005.
I think Rob is right. I need to verify that all the methods and properties are found in 2000 and later, because even with late binding, if they are not available, I'll be screwed.

[morpheus3230]

and yes the refs were removed :-)

[disruptivehair]

Hi there,

this is the issue I am facing:

I have written an app that contains a feature which exports datasets to an excel sheet and does all the little formatting things to make my users happy.
Anyway, on my machine, I have Office 2003 installed (which means Excel COM 11.0) so everything works great.
Unfortunately some of my target audience has Office 2000, which means Excel COM 10.0 I believe). So therein lies the problem. As soon as they try to export, error message pops up about access to protected memory etc.
So I thought since I was using early binding, that if I change it to late binding it would work. Unfortunately ... not the case. My guess is that some methods and properties I use are not available in the 10.0 version.

In general...How do people approach this issue? Any advice will be appreciated.
many thanx

Morph, maybe if you posted the exact code I could check it against Excel 2002, which I'm running here. The available object library for Excel on VB and VBA is 10 and 10 only.

[morpheus3230]

dh, excel 2002 won't help because some of my users have excel 2000 which is 9.0 so I need to get to msdn and check the availability of some of the stuff

however here is the code

Dim xlApp As Object
            Dim xlBook As Object
            Dim xlSheet As Object
            Dim xlRange As Object
 
            If File.Exists("C:\Program Files\Hawkeye Customs\ECN Database\Reports\" & strJob & ".xls") Then
                File.Delete("C:\Program Files\Hawkeye Customs\ECN Database\Reports\" & strJob & ".xls")
            End If
 
            'xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
            xlApp = CreateObject("Excel.Application")
            xlBook = xlApp.Workbooks.Add()
            xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
            ' Place some text in the second row of the sheet.
            xlSheet.Cells(1, 1) = "RUN ON"
            xlSheet.Cells(1, 2) = Date.Now
 
 
 
            For intR As Integer = 0 To Me.DsecnEntriesV.Tables(0).Rows.Count - 1
                xlSheet.Cells(intR + 2, 1) = DsecnEntriesV.Tables(0).Rows(intR).Item(31)
                xlSheet.Cells(intR + 2, 2) = CType(DsecnEntriesV.Tables(0).Rows(intR).Item(28), Date).Date
                xlSheet.Cells(intR + 2, 3) = DsecnEntriesV.Tables(0).Rows(intR).Item(29)
                If Trim(UCase(DsecnEntriesV.Tables(0).Rows(intR).Item(30))) = "TRUE" Then
                    xlSheet.Cells(intR + 2, 4) = "Approved"
                Else
                    xlSheet.Cells(intR + 2, 4) = "Not Approved"
                End If
 
                If (intR Mod 2) > 0 Then
                    xlRange = xlSheet.Range("A" & (intR + 2) & ":A" & (intR + 2))
                    xlRange.EntireRow.Interior.ColorIndex = 15
                End If
            Next
 
            xlRange = xlSheet.Range("C1:C1")
            xlRange.EntireColumn.ColumnWidth = 50
            xlRange.EntireColumn.WrapText = True
            
            xlRange = xlSheet.Range("A1:d1000")
            xlRange.EntireColumn.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
            xlRange.EntireRow.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
            xlRange.EntireRow.Font.Size = 8
 
            xlRange = xlSheet.Range("A1:d1000")
            xlRange.Columns.AutoFit()
            'xlRange = xlSheet.Range("D1:D1")
            'xlRange.Columns.AutoFit()
 
            xlBook.SaveAs("C:\Program Files\Hawkeye Customs\ECN Database\Reports\" & strJob & ".xls", Excel.XlFileFormat.xlWorkbookNormal)
 
            xlBook.Close()
            xlApp.Quit()
            Marshal.ReleaseComObject(xlApp)
            xlApp = Nothing
            MsgBox("Finished Excel Export!", MsgBoxStyle.Information, "ECN")

[morpheus3230]

Rob, is there an easy way to verify the existance of methods and properties in an Excel version on MSDN. I'm not even sure how and where to start?
thanx

[si_the_geek]

Assuming that DsecnEntriesV is not Excel related (I'm guessing it's the source of the data), it all looks like fairly basic stuff - and should be supported by Excel 97 upwards.

The only thing that concerns me is the constants, eg: Excel.XlHAlign.xlHAlignLeft
I don't use VB.Net, but these bits look early bound to me.

Hey si, slightly off-topic here, don't want to hijack the thread. I followed your late-binding tutorial and it's working, but I had to change pretty much every Excel function I had because I was passing Excel objects. Y'know, had to change them from Excel.Worksheet to Object. Should I not be passing objects like that? TIA.

When using Late Binding in Classic VB (and possibly .Net), you cannot use Excel.Anything in your code, so you should use Object the data type instead. If you want me to check your code, create a new thread and PM me a link to it.

[RobDog888]

According to this initial line I believe your reference is still added. You should be getting a Cast error of "Excel.Worksheet" is not defined.

xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)

Did you also remove any "Imports" for Excel at the top of your class?

[morpheus3230]

I did not :-(
I will try it again.
As far as the line you quoted goes...how do I rewrite that so that it works with late binding?
Thanx

[morpheus3230]

just for the record... I have removed all instances of the early binding and replaced it with late and changed all the references to any excel properties to corresponding integer values and life is good now.
Everything is working great on excel 2000 and later, so I am good.
Many thanx to all