Error Using Office

I add reference office.dll and Microsoft Office Excel Component to my project and use :
---------------------------------------
Dim App As Excel.Application, BarName as String

...

Dim Bar As Microsoft.Office.Core.CommandBar = App.CommandBars(BarName)

...
----------------------------------------

Than recieve this error in my task board for the second code line:
+ 'Reference required to assembly 'Interop.Microsoft.Office.Core' containing the type 'Microsoft.Office.Core.CommandBars'. Add one to your project.'

What can I do in order to solve this problem?

ns2K,

Hmmm I'm not 100% sure what is gonig on. You might want to start with a new project, just to get a sense of how to get these references right, and then once we've figured it out, apply those changes to your *real* project.

You should not have to explicitly add a reference to the 'Microsoft.Office.Core', for adding a reference to 'Excel' should take care of that step for you... so I am guessing that your adding the refrerence to the 'office.dll' is interfering?

Here is how I loaded the references in my project and it ran fine. Starting with a brand-new Project, I show the following references in my Solution Explorer:

System
System.Data
System.Drawing
System.Windows.Forms
System.XML

I then look to add the Microsoft Excel Object Library, choosing Alt|Project|Add Reference... then clicking the COM Tab, and then scrolling down until I find "Microsoft Excel 10.0 Object Library" (which could be 9.0 or some other # for you).

I then click <Select> and then <Ok>.

The following references are added for me:

Excel
Microsoft.Office.Core
VBIDE
stdole

So you can see that 'Microsoft.Office.Core' is added automatically for you. (Well, it was for me!)

I then ran the following code, without any trouble:Public Class Form1
Inherits System.Windows.Forms.Form

Dim oExcel As Excel.Application
Dim oCommandBars As Microsoft.Office.Core.CommandBars
Dim oBar As Microsoft.Office.Core.CommandBar

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

oExcel = New Excel.Application
oCommandBars = oExcel.CommandBars ' <-- *** Critical! ***

sBarName = "Drawing" ' <-- (Change as Needed)
oBar = oCommandBars(sBarName)

oExcel.Visible = True
oBar.Visible = True
End Sub

Private Sub Form1_Disposed(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Disposed
oExcel.Quit()
NAR(oExcel)
NAR(oBar)
End Sub

Private Sub NAR(ByVal o As Object)
' This sub cleanly releases the COM Automation Object.
' Source: http://support.microsoft.com/?kbid=317109
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
Catch
Finally
o = Nothing
End Try
End Sub
End Class Note some issues in the above:

(1) Note the line that reads: oCommandBars = oExcel.CommandBars ' <-- *** Critical! *** Unfortunately, when using Automation in .Net one cannot directly call oBar = oExcel.CommandBars(sBarName) in one line as you could in VB 6.0 (and, frankly, in virtually every other circumstance in .Net as well). Well, you could, and it will work, but your Excel.Application will never close when you are done (if you check your Task Manager, you'll see it hanging around even after your .Net Application is closed).

You must break up the line as follows:
oCommandBars = oExcel.CommandBars
oBar = oCammandBars(sBarName) And lastly, you must terminate your COM Interop Objects by making use of the NAR() Sub, shown above.

For more info on these complexities, I would see the FAQ on .Net Automation, above: http://www.visualbasicforum.com/t129690.html It's a bit long, but I think (hope!) you'll find it useful.

I hope this helps!

Mike