Microsoft.Office.Interop.Excel, What am I not understanding?!

[evad4682]

Grrrrrr

File or assembly name Microsoft.Office.Interop.Excel or one of its dependancies was not found

I am trying to use an app I made on a separate machine than it was developed on. The app exports the contents of a data grid to an excel spreadsheet. On the pc the app was developed on I can do the exporting with success, on the other pc I am getting the above error. I have checked my Assembly directory (c:\windows\assembly) and verified that both machines have the same Microsoft.Office.Interop.Excel file with the same version (11) and the same public key. What is the deal?

This what I am doing

[/COLOR]
[COLOR=black][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] Microsoft.Office.Interop[/SIZE]
[SIZE=2][/SIZE] 
[SIZE=2][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intColumn, intRow, intColumnValue [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strFileName [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Excel [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Excel.Application[/SIZE][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] user [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = Environment.UserName
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] filename [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = InputBox("File Name", "Save As", ".xls")
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] fpath [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "c:\Documents and Settings\" & user & "\Desktop\" & filename[/SIZE]
[SIZE=2] 
[/SIZE][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE][SIZE=2] Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()
[/SIZE][SIZE=2][COLOR=#008000]'For displaying the column name in the the excel file.
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] intColumn = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] ds1.Tables(0).Columns.Count - 1
.Cells(1, intColumn + 1).Value = ds1.Tables(0).Columns(intColumn).ColumnName.ToString
[/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'For displaying the column value row-by-row in the the excel file.
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] intRow = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] ds1.Tables(0).Rows.Count - 1
[/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] intColumnValue = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] ds1.Tables(0).Columns.Count - 1
.Cells(intRow + 1, intColumnValue + 1).Value = ds1.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString
[/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000] 
[/COLOR][/SIZE][SIZE=2].ActiveWorkbook().SaveAs(fpath)
.ActiveWorkbook.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]With
[/COLOR][/SIZE][SIZE=2]MessageBox.Show("File exported sucessfully.", "Exporting done", MessageBoxButtons.OK, MessageBoxIcon.Information)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][/SIZE][/COLOR][COLOR=black]

The above works on the pc it was developed on but not on any others. Thanks in advance for any help.

 

[JohnH]

The Interops are just description wrappers for the real libraries, also the relevant Office application have to be installed to make use of the different applications Object Libraries.

 

[evad4682]

So then it matters what version of office I have on each machine? It doesn't matter that the Assembly file is the same?

On the working machine I have Office 2003 and the nonworking I have Office 2000.

If it does matter what version of Office I have on each machine, how do I write my code to be nondiscriminate?

Thanks again for all the help.

 

[JohnH]

It's the 'real' library version that matters if you use the early binding method (as you seem to do, and recommended), it you use the late binding method (a pain to develop with) the only requirement is that the methods and objects you use in the libraries are compatible between the client versions.

Examples of both methods: "How To Use Visual Basic .NET for Binding for Office Automation Servers" http://support.microsoft.com/kb/304661/

 

[evad4682]

Thanks for the advice! I will take a look at this doc that you have posted.

Thanks again John. I appears that Microsoft is suggesting that I develop the app with the oldest version of Office that I plan to distribute to. I read through the article and found no reference to Office 2000. Sadly enough I need to distribute this app to that version as well.

I was hoping that there was something that I could add to my code so that my app would be compatible with all versions.

 

[JohnH]

To automate for different office versions you can either use late binding method and as I said make sure all code is compatible for all targeted versions, it's not uncommon to do but a hassle to work out, OR you could do one development copy for each version you wish to target with the convenient early binding method (you would need all binding versions installed on development machine too).

Online references for Office applications versions 2000+XP(2002)+2003 http://msdn.microsoft.com/office/downloads/vba/default.aspx

 

[evad4682]

Thanks John, I will look into that link. I appreaciate all the help.

 

[evad4682]

I got it! Thanks for the references they were a huge help! I am able to run this app on pc's regardless of the Office version.

This is what I have now

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] mnuExpport_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] mnuExpport.Click[/SIZE]
 
[SIZE=2][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] objApp [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Object
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] objBook [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Object
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] objBooks [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Object
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] objSheets [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Object
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] objSheet [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Object
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] range [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Object
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intRow [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intColumnValue [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]' Instantiate Excel and start a new workbook.
[/COLOR][/SIZE][SIZE=2]objApp = CreateObject("Excel.Application")
objBooks = objApp.Workbooks
objBook = objBooks.Add
objSheets = objBook.Worksheets
objSheet = objSheets.Item(1)
 
[/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] intRow = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] ds1.Tables(0).Rows.Count - 1
[/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] intColumnValue = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] ds1.Tables(0).Columns.Count - 1
objSheet.cells(intRow + 1, intColumnValue + 1).Value = ds1.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString
[/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'Return control of Excel to the user.
[/COLOR][/SIZE][SIZE=2]objApp.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2]objApp.UserControl = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][SIZE=2]MessageBox.Show("File exported sucessfully.", "Exporting done", MessageBoxButtons.OK, MessageBoxIcon.Information)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][/SIZE]

I used a lot of the MS sample code from the first link that you had posted. Thanks again.

 

[UncleRonin]

*blink* Isn't all that's required the Interop.Excel dll? I developed an Excel app a while back and all that was necessary to run it was the NET framework and the dll. It was compatible with a PC which doesnt have Excel at all and was running Win98. (The Excel code i used included the stuff mentioned in the last code snippet)

I havent tried it yet but instead of needing Office installed could you not use Excel Viewer?

 

[evad4682]

The last code I posted is for late binding. Checkout the links that John had put in. The last code will work on any pc like you said, my first attempt will not.

I have not tried an Excel viewer so I don't know.