SeriesCollection, VB.NET, Excel 2003 vs. 2007

My quesion is mostly basic, I have the following line of code:
            chartWorkbook.SeriesCollection(1).Interior.Color = RGB(0, 255, 0)

When this line of code is added I get this error:
Unhandled Exception: System.Runtime.InteropServices.COMException (0x800A03EC): U
nable to set the Color property of the Interior class
   at Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateSet(Object
o, Type& objType, String name, Object[] args, String[] paramnames, Boolean Optim
isticSet, CallType UseCallType)
   at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateSet(Object Insta
nce, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, T
ype[] TypeArguments, Boolean OptimisticSet, Boolean RValueBase, CallType CallTyp
e)
   at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateSetComplex(Objec
t Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentN
ames, Type[] TypeArguments, Boolean OptimisticSet, Boolean RValueBase)
   at ConsoleApplication1.Module1.MakeGraph(Int32 ArrayWidth, Int32 ArrayLength,
 String MetricType, Int32 jstart, Object WorksheetName, String ChartViewportName
)
   at ConsoleApplication1.Module1.SetupGraph(String MetricType, Object XLSXWorkb
ook)
   at ConsoleApplication1.Module1.Main()


I have a pretty good idea on why this is a problem, well if you look at the end of the error you see "XLSXWOrkbook", 2003 does not support XLSX files!!!

But I am saving the file as "excelWorkbook.SaveAs(FileInput & ".xls")", so I would not think that the file is an xlsx file, but it must be???

Maybe the problem is that I am compling on a machine with Office 2007??

HELP!
sporenzaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
It is pretty tricky working between Office 2003 and 2007.  If you compile on a machine with 2007, then it will use the type libraries from 2007, and not 2003.

Bob
0
sporenzaAuthor Commented:
OK, so I took the code to a machine that never had 2007 installed and only has 2003.... i get the same type of error message :(

Unhandled Exception: System.Runtime.InteropServices.COMException (0x800A03EC): U
nable to set the Name property of the Series class
   at Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateSet(Object
o, Type& objType, String name, Object[] args, String[] paramnames, Boolean Optim
isticSet, CallType UseCallType)
   at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateSet(Object Insta
nce, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, T
ype[] TypeArguments, Boolean OptimisticSet, Boolean RValueBase, CallType CallTyp
e)
   at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateSetComplex(Objec
t Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentN
ames, Type[] TypeArguments, Boolean OptimisticSet, Boolean RValueBase)
   at ConsoleApplication1.Module1.MakeGraph(Int32 ArrayWidth, Int32 ArrayLength,
 String MetricType, Int32 jstart, Object WorksheetName, String ChartViewportName
) in C:\Documents and Settings\user.SHUTTLEBLAKE\My Documents\Visual Studio 2008
\Projects\CopyExcelSheet\CopyExcelSheet\Module1.vb:line 229
   at ConsoleApplication1.Module1.SetupGraph(String MetricType, Object XLSXWorkb
ook) in C:\Documents and Settings\user.SHUTTLEBLAKE\My Documents\Visual Studio 2
008\Projects\CopyExcelSheet\CopyExcelSheet\Module1.vb:line 196
   at ConsoleApplication1.Module1.Main() in C:\Documents and Settings\user.SHUTT
LEBLAKE\My Documents\Visual Studio 2008\Projects\CopyExcelSheet\CopyExcelSheet\M
odule1.vb:line 84
0
Bob LearnedCommented:
That is a different line, so what line is that error on?

Bob
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

sporenzaAuthor Commented:
it is the same line of code I just uncommited it out earlier in the code...

I do also realize that "XLSXWorkbook" is the name of the object so, the code is NOT trying to make an xlsx file....

i will try to update with more information

0
Bob LearnedCommented:
If you could give me a code example of what you mean, I could test it on my machines with 2003 and 2007.

Bob
0
sporenzaAuthor Commented:
Bob,

I am working on it. I will give you something simple soon..
0
sporenzaAuthor Commented:
OK here is the code, I cut it down to as simple as possible...

when I compile it on my 2007 machine everything is good, when I compile it on my 2003 machine I get the attached error...

Also I am attaching the "Test.psnr" file that is used in the code.. (You will have to rename it from .txt to .psnr)

This should make it more clear????

Thanks so much for your help Bob, I am dead without it!!!

Cheers,
Adam
Imports System.IO
Imports System.Console
 
Module Module1
 
    Sub Main()
 
        Dim excelApplication As Object = CreateObject("Excel.Application")
        excelApplication.DisplayAlerts = False
 
        Dim metricWorkbook As Object = excelApplication.Workbooks.Add()
        excelApplication.Workbooks.OpenText(FIlename:="C:\Test.psnr", _
                                            Space:=True, DataType:=1, ConsecutiveDelimiter:=True)
        metricWorkbook = excelApplication.ActiveWorkbook
 
        metricWorkbook.ActiveSheet.Name = "PSNR"
 
        Dim chartWorkbook As Object
        chartWorkbook = metricWorkbook.charts.add()
        chartWorkbook.ChartType = 4
        chartWorkbook.PlotBy = 2
 
        'Create Data Series
        chartWorkbook.SetSourceData(Source:=(metricWorkbook.worksheets("PSNR").range("H30:J180")))
 
        'Set Colors of Graph
        chartWorkbook.SeriesCollection(1).Interior.Color = RGB(255, 255, 0)
        chartWorkbook.SeriesCollection(1).Border.Color = RGB(255, 255, 0)
 
        metricWorkbook = excelApplication.ActiveWorkbook
        metricWorkbook.SaveAs(filename:="C:\Test.xls", FileFormat:=56)
 
        ' Close the Workbook object.
        If Not metricWorkbook Is Nothing Then
            metricWorkbook.Close(False)
            metricWorkbook = Nothing
        End If
 
 
        ' Close the ApplicationClass object.
        If Not excelApplication Is Nothing Then
            excelApplication.Quit()
            excelApplication = Nothing
        End If
 
        GC.Collect()
        GC.WaitForPendingFinalizers()
 
        Exit Sub
 
    End Sub
 
End Module

Open in new window

Error.JPG
Test.txt
0
Bob LearnedCommented:
With 2003, this line:

       chartWorkbook.SeriesCollection(1).Interior.Color = RGB(255, 255, 0)

is the same as this line:

        chartWorkbook.SeriesCollection(1).Border.Color = RGB(255, 255, 0)

You should be able to use the 2nd line, without the first.

Bob
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sporenzaAuthor Commented:
Great work Bob, look for another post soon, i feel that when i go farther down the code I will find some more problems :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.