Problem in Automation of Handling Excel Data Using VB.NET

Hello All,

I’m trying to automate the crunching and graphic display of Excel data. For this purpose I’m trying to write a VisualBASIC.NET program that would read the data from two columns of a sample Excel 2007 file test.xls, then multiply each couple (the two data values in each row) individually, place the integrated value of these individual products in a third column and also present that integrated values as a function of row number (eventually, as a function of time, set in a separate column).

Thus, I downloaded the oxppia.exe from the site http://download.microsoft.com/download/c/4/8/c4813cc0-a4d4-4bb4-b486-9cbd56f38235/oxppia.exe, unzipped it in a separate folder and installed it using the batch file there.

Then I installed the references by going to:
Project->Add Reference->Browse->Microsoft.Office.Interop.Excel
and
Project->Add Reference->COM->Microsoft Excel 8.0 Object Library 1.2

Installing of the references was done after writing the following sample code in Form1:


Imports Excel = Microsoft.Office.Interop.Excel
Imports Office = Microsoft.Office.Core



Public Class Form1

    Inherits System.Windows.Forms.Form


 Windows Form Designer generated code

   

    Dim xlsApp As Excel.Application
    Dim xlsWorkBook As Excel.Workbook
    Dim xlsWorkSheet As Excel.Worksheet
    Dim xlsCell As Excel.Range


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        ' Initialise Excel Object
        xlsApp = New Excel.Application

        ' Open test Excel spreadsheet
        xlsWorkBook = xlsApp.Workbooks.Open("d:\test.xls")


        ' Open worksheet (can open by number or name)
        xlsWorkSheet = xlsWorkBook.Worksheets(1) ' You could also do it by name: "sheet1"

        ' Read the first cell
        xlsCell = xlsWorkSheet.Range("A1")

        ' Display the first cell
        MsgBox(xlsCell.Text)
   
   End Sub  

End Class


The code above compiles without a problem, however, when debugging it the following error message appears:



“An unhandled exception of type 'System.InvalidCastException' occurred in Spheres.exe

Additional information: QueryInterface for interface Microsoft.Office.Interop.Excel._Application failed.”



Because the debugger highlights  the line:

xlsWorkBook = xlsApp.Workbooks.Open("d:\test.xls")

it appears that the test.xls file cannot be read. Tried to fix this by reinstalling Office 2007, to no avail. Obviously, will have to solve this before proceeding with writing the result into an Excel file and having the result presented graphically, as well as other details of the project. I’m working under Windows XP. Thanks in advance for your help.
judicoAsked:
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.

CodeCruiserCommented:
Did you try with any other file? What version of Office APIs have you installed and referenced?
0
judicoAuthor Commented:
Just tried it with another file. Same thing. Don't know how to check the Office API version.
0
CodeCruiserCommented:
Check the version of the Excel interop DLL in your references to see if its 11 or 12.
0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

judicoAuthor Commented:
Properties read as follows: Microsoft.Office.Interop.Excel, Runtime Version v1.0.3705, Version 10.0.4504.0.
0
CodeCruiserCommented:
I think that's not the right version for Office 2007. You need version 12 library for Office 2007. Now either change the references (and probably code) to use Office 2007 or install Office XP.

http://www.microsoft.com/downloads/details.aspx?FamilyID=59daebaa-bed4-4282-a28c-b864d8bfa513&displaylang=en
0
judicoAuthor Commented:
I'm sorry, I'm using Office97. Maybe that's the problem. Is there an Excel interop DLL for that oleder version of Office that I can try?
0
CodeCruiserCommented:
Never came across any example of Office 97 automation and have not seen the APIs either. I would be surprized if Office 97 supports automation!
0
judicoAuthor Commented:
I guess I shouldn't go on pursuing Office97 automation and should move on to an appropriate version of Office. Which one do you recommend to move on to so that what I've done in Office97 so far would be backward compatible?
0
CodeCruiserCommented:
I think Office 2003 would be safer(in terms of compatability) than office 2007
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
judicoAuthor Commented:
Thanks @CodeCruiser. I will now assign you the points and will close this question. Will try Office 2003, as you suggested, and if there are still problems will open a new question. All the best.
0
CodeCruiserCommented:
Glad to help:-)
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
Microsoft Development

From novice to tech pro — start learning today.