?
Solved

Microsoft.Office.Interop.Excel problem

Posted on 2011-10-19
29
Medium Priority
?
2,840 Views
Last Modified: 2012-05-12
I have a VB.net application.  One of the subroutines allows the user to export the display from a datagridview on the screen to an Excel spreadsheet.  It works properly on over 30 workstations.  But on one workstation, when attempting the export, we get an error message that includes this:

"Could not load file or assembly 'Microsoft.Office.Interop.Excel, Version=12.0.0.0,Culture=neutral,PublicKeyToken =?1e9bce111e9429c' or one of its dependencies.  The system cannot find the file specified."

This is the only workstation with the problem.  Office 2007 is installed . Running Repair on Office 2007 does not resolve the issue.  It seems this workstation is missing a dll or a registry key or something, but I don't know what, nor how to fix it.

T
0
Comment
Question by:thoecherl
  • 15
  • 12
  • 2
29 Comments
 
LVL 11

Expert Comment

by:cup
ID: 36996095
You probably need to install .net framework on that machine.
0
 

Author Comment

by:thoecherl
ID: 36996663
The machine has .net 3.5 SP1 installed.
0
 
LVL 11

Expert Comment

by:cup
ID: 36997938
It is probably a missing DLL but which one?   Here is a brute force way of finding out what it is looking for

Get process monitor from http://technet.microsoft.com/en-us/sysinternals/bb896645

Run it on the machine, set the filter to process your program.  Run your program.  When it comes up with the message, scroll back in process monitor (you may have to go quite a long way) and see what it is looking for.  

 If the DLL is there and it can't find it, check the path.  If you are getting a side by side, you may need a config file.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37000719
0
 

Author Comment

by:thoecherl
ID: 37000785
It is installed on the dev machine.  I understood it only needed to be there and when I create the msi package to go on the recipient machines the msi has everything needed.  Is that not correct?  Does the Office PIA need to be on all of the machines on which the msi package is going to be installed?  I have already installed this package on over 30 machines and it is working successfully on all but 1, but I have not installed the Office PIA on any of the machines other than the dev machine.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37002468
If you have included the DLL in your setup then you dont need the PIA. How is this computer different from others? Operating system? 32bit/64bit?
0
 

Author Comment

by:thoecherl
ID: 37002544
32 bit, Windows XP.  All of the workstations on which the program is currently working properly are 32 bit.  Some are Windows XP and some are Windows 7.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37002577
>Could not load file or assembly 'Microsoft.Office.Interop.Excel, Version=12.0.0.0,Culture=neutral,PublicKeyToken =?1e9bce111e9429c

Did you notice the ? in public key token?

0
 

Author Comment

by:thoecherl
ID: 37002706
Yes, I noticed it, but I don't know what it means.  
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37002720
The references are in your .vbproj file. Open it in text editor and modify it so that

PublicKeyToken =?

0
 

Author Comment

by:thoecherl
ID: 37002858
Is this something I can find in the Visual Studio interface?  I did a search on the dev machine looking for .vbproj and came up empty.  Same thing when I searched files, entire project, inside Visual Studio for .vbproj, as well as for PublicKey=?.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37002903
It would be within your solution folder. Is it VB.NET though? Or C#? would be csproj if that's the case?
0
 

Author Comment

by:thoecherl
ID: 37003234
VB.net.  I did find the vbproj file and I found the key.  In the file, the ? was replaced by a 7.  I changed it to ? and then launched Visual Studio.  Then I launched the program.  It runs without error and I am still able to export the datagridview to Excel on the dev machine.  However, the Imports statement for the Excel.interop:

Imports Excel = Microsoft.Office.Interop.Excel
 
is now underlined in green and when I hover over it with the mouse, I get this popup message:

"Namespace or type specified in the Imports 'Microsoft.Office.Interop.Excel' doesn't contain any public member or cannot be found.  Make sure the namespace or the type is defined and contains at least one public member.  Make sure the imported element name doesn't use any aliases."

Despite the message, the program works.

I have not yet created a new deployment package for the problem machine because I wanted to run this past you first.  The code for the Excel export process is this:

    Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcel.Click
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim strFile As String
        Dim Excel As Object
        Excel = CreateObject("Excel.Application")
        Excel.screenupdating = True
        Excel.visible = True

        Try
            Dim i As Integer
            Dim j As Integer
            Dim saveFileDialog1 As New SaveFileDialog
            saveFileDialog1.Filter = "Excel File|*.xls"
            saveFileDialog1.Filter = "Excel File|*.xlsx"
            saveFileDialog1.Title = "Save Excel File"
            saveFileDialog1.ShowDialog()
            strFile = saveFileDialog1.FileName

            xlApp = New Excel.ApplicationClass
            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = xlWorkBook.Sheets("Sheet1")

            'Create column headings
            xlWorkSheet.Cells(1, 1) = "Manf Order"
            xlWorkSheet.Cells(1, 2) = "Tag Printed"
            xlWorkSheet.Cells(1, 3) = "SO #"
            xlWorkSheet.Cells(1, 4) = "Item"
            xlWorkSheet.Cells(1, 5) = "Description"
            xlWorkSheet.Cells(1, 6) = "Quantity"
            xlWorkSheet.Cells(1, 7) = "Qty Complete"
            xlWorkSheet.Cells(1, 8) = "Ship Date"
            xlWorkSheet.Cells(1, 9) = "Imprint Color"
            xlWorkSheet.Cells(1, 10) = "Ship Via"
            xlWorkSheet.Cells(1, 11) = "Work Center"
            xlWorkSheet.Cells(1, 12) = "SEQ_DISPLAY"
            xlWorkSheet.Cells(1, 13) = "Req Date Time"
            xlWorkSheet.Cells(1, 14) = "STATUS"
            xlWorkSheet.Cells(1, 15) = "Complete?"
            xlWorkSheet.Cells(1, 16) = "CURR_OP"
            xlWorkSheet.Cells(1, 17) = "IN_TIMECLK"

            For i = 0 To PCT_SNUGZ_FILTERDataGridView.RowCount - 2
                For j = 0 To PCT_SNUGZ_FILTERDataGridView.ColumnCount - 4
                    xlWorkSheet.Cells(i + 2, j + 1) = _
                        PCT_SNUGZ_FILTERDataGridView(j, i).Value.ToString()
                Next
            Next

            xlWorkBook.SaveAs(strFile)
            Excel.quit()
            Excel = Nothing

        Catch ex As Exception
            MessageBox.Show("Excel connection error: Please contact your System Administrator.", "Communication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        Finally

            xlWorkBook.Close()
            xlApp.Quit()
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
        End Try

        Shell("C:\Program Files\Microsoft Office\Office12\Excel.exe " & strFile)
    End Sub

These three lines:
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
are underlined in blue and the message says "Type 'xxx' is not defined"
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37003245
Does the line still appear if you use

Imports Microsoft.Office.Interop
0
 

Author Comment

by:thoecherl
ID: 37003290
The line is still there and now I get build errors:

Error      2      Type 'Excel.Application' is not defined.      C:\VSProjects\ShopFloor\ShopFloorProject\ShopFloorProject\Main.vb      1865      22      ShopFloorProject

Error      3      Type 'Excel.Workbook' is not defined.      C:\VSProjects\ShopFloor\ShopFloorProject\ShopFloorProject\Main.vb      1866      27      ShopFloorProject

Error      4      Type 'Excel.Worksheet' is not defined.      C:\VSProjects\ShopFloor\ShopFloorProject\ShopFloorProject\Main.vb      1867      28      ShopFloorProject

Error      5      Type 'Excel.ApplicationClass' is not defined.      C:\VSProjects\ShopFloor\ShopFloorProject\ShopFloorProject\Main.vb      1885      25      ShopFloorProject
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37003433
How about

Imports Microsoft.Office.Interop.Excel
0
 

Author Comment

by:thoecherl
ID: 37003609
Imports Microsoft.Office.Interop.Excel now gives me 66 build errors, most of which seem to be unrelated to Excel.  When I change the import statement back to Imports Excel = Microsoft.Office.Interop.Excel those errors disappear.   Here are the scenarios:

1.  Import statement = Imports Microsoft.Office.Interop.Excel -- 66 build errors.
2.  Import statement = Imports Microsoft.Office.Interop, PublicKeyToken=71e9bce111e9429c -- program works on dev machine, have not deployed to XP workstation.
3. Import statement = Imports Microsoft.Office.Interop, PublicKeyToken=?1e9bce111e9429c -- 4 build errors.
4.  Import statement = 'Imports Excel = Microsoft.Office.Interop.Excel, PublicKeyToken= 71e9bce111e9429c - program works on dev machine but not on XP workstation.
5.  Import statement = 'Imports Excel = Microsoft.Office.Interop.Excel, PublicKeyToken=1e9bce111e9429c - 4 build errors

T
0
 

Author Comment

by:thoecherl
ID: 37003624
The other thing to remember is that with the public key token of 71e9bce111e9429c, the program works on other XP machines to which it has been deployed.  Only one machine out of 35 gets the error pointing to the ? in the key.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37003675
>PublicKeyToken=71e9bce111e9429c

Install the DLL to GAC on the XP machine.
0
 

Author Comment

by:thoecherl
ID: 37003782
Like this?

gacutil /i path/ Microsoft.Office.Interop.Excel.dll
0
 

Author Comment

by:thoecherl
ID: 37007904
Sorry CodeCruiser.  I guess I don't quite know what you mean.  I tried this on the XP machine:

gacutil /i path/ Microsoft.Office.Interop.Excel.dll

I got an error saying that gacutil is not recognized on that machine.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 37008065
GacUtil is within .NET framework

navigate to

C:\windows\microsoft.net\framework\v2.0xxxx\

use the correct framework folder where I used xxxx.
0
 

Author Comment

by:thoecherl
ID: 37009786
Found gacutil.exe.

Registered dll

Program now works.  Problem solved.  Thanks CodeCruiser
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37010881
Glad to help :-)

The easier way though is to download the PIA MSI and it takes care of GAC registration.
0
 

Author Comment

by:thoecherl
ID: 37011196
I downloaded the PIA on my development machine when I was first developing the program.  I think that's why the program worked on 35 other machines.  Is the PIA MSI a separate program?  If so and I am in fact missing a piece, why would the program work on all of those other machines, most of which are using XP OS? (this is a question, not an argument.  I don't know the answer and I figure if anyone does, it will be CodeCruiser)
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37011244
How is your program installed? Is it just copied? Copied? You can usually just copy the dll in your folder and it should work. But it did not work in this case for some reason.

PIA MSI is a setup file which installs and gac registers these dlls on a computer.
0
 

Author Comment

by:thoecherl
ID: 37011304
In Visual Studio, I change the Solution Configuration from Debug to Release.  Then I build the solution, which creates two files:  ShopFloor.msi and setup.exe.  I copy both files to the target machine and then run the msi.

At the beginning of my development I installed O2003PIA.ee and oxppia.exe on the dev machine.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37011354
Can not see the problem really.
0
 

Author Comment

by:thoecherl
ID: 37011363
OK.  Well, if it happens again, you have given me an effective workaround.  Thanks again.  This one was worth 1,000 points.  Wish they gave that option.

T
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
Suggested Courses
Course of the Month13 days, 15 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question