EXCEL 2003 and VB.NET

Hello

How to SUCCESSFULY automate EXCEL 2003 with VB.NET?

I referenced Excel 11.0 object library with my VB.NET project. But I can not do anything, becouse VB sends me errors on the first lines (example: "xlBook = xlApp.Workbooks.Add") . I looked MSDN but there is no soultions (I copied advices line into my VB.NET by line but nothing works). I even used SHELL posted here ( xtremevbtalk.com (http://www.xtremevbtalk.com/showthread.php?t=160433 ) ), but without success (I get error 2). What to do?

ERROR 1:
An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll
Additional information: Exception from HRESULT: 0x800A03EC.

ERROR 2:
An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in <project name>.exe

Additional information: ...


Thanks for replay,
Stajner

Didn't You recently make a post in another thread about this?

Anyway, before moving on I would like to take part of the the whole procedure and also want to know more about any references made.

Didn't You recently make a post in another thread about this?

Anyway, before moving on I would like to take part of the the whole procedure and also want to know more about any references made.
--------------------------------------------------------------------------
Yes, I did, becouse I find there the same problem. I hope that is not a problem.

Well problem is, that when I run code, which I got on net:
In the beginign of form:

Imports Microsoft.Office.Interop

and further:


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim oApp As New Excel.Application()
Dim oBook As Excel.Workbook = oApp.Workbooks.Add '<--ERROR HERE
Dim oSheet As Excel.Worksheet = oApp.ActiveSheet

oSheet = Nothing
oBook.Close(False)
oBook = Nothing
oApp.Quit()
oApp = Nothing

Debug.WriteLine("Sleeping...")
System.Threading.Thread.Sleep(5000)
Debug.WriteLine("End Excel")
End Sub

And this is the only code. I also referenced Excel 11.0 Object library (COM reference). I do not have MUI installed! I hope this is not the problem :( .

Error is:
An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in EXCEL_TEST8.exe

Additional information: Old format...

Thanks for helping,
Stajner

OK, since we talk about Excel 2003 let see if we can fix it with the following:

On top on the form replace the present import-statement with:

Imports Excel = Microsoft.Office.Interop.Excel


and replace the present code for the button with the following:


Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook = xlApp.Workbooks.Add
xlApp.Visible = True


Does the error-message still exist?

No, new code does not work. It breaks on the same line. Mabe I could tip you with MSDN help about MUI:

Dim oApp As New Excel.Application()
Dim oBooks As Object = oApp.Workbooks
Dim ci As System.Globalization.CultureInfo = New System.Globalization.CultureInfo("en-US")
oBooks.GetType().InvokeMember("Add", Reflection.BindingFlags.InvokeMethod, Nothing, oBooks, Nothing, ci)
oApp.Visible = True
oApp.UserControl = True


This code in button works just fine. Excel opens and new workbook appear. But I can not fill a single cell. I aslo managed to expand this code with line:

MsgBox(oApp.Sheets.Count())

But this does not solves my problem! I still can not open .xls file and changed it. I simply can not. I do not understand that previous versions did fine job without language problems. I do not understant this MUI, why I need it. I want to do it without it. There must be a way to pass this bug. Mabe this is not a BUG (like MS said), but a simple fact to do something for MS, what we usually wouldnt? Please help further.
Stajner

First of all, let us agree that there exist problems with automation of Excel via VB.NET :(

Second, I assume You're running VB.NET 2003 english version and not any local version of it, right? If so then the MUI-approach is weird :confused:

Third, do You have Option Strict on or off? You can check it via the command Project | Name of the project.properties | Build.

Finally, have faith :)

Running the provided code on my machine cause the app to crash...

If You continue to use my approach as above BUT change the add-statement to the following:


Dim xlBook As Excel.Workbook = CType(xlApp.Workbooks.Add, Excel.Workbook)


Will that make any changes?

BTW, per default when open Excel it always add a new workbook unless we open an already saved workbook.

First of all, let us agree that there exist problems with automation of Excel via VB.NET :(

1) VB.NET 2003 english version
2) Third, do You have Option Strict on or off?
3) Will that make any changes?


- 1) Yes and English Office 2003
- 2) It is OFF
- 3) NO. On the same line it breaks. Also on the repleaced line.

OK, last try this day/night for me:

If You only use:


Dim xlApp As New Excel.Application
xlApp.Visible = True


What will the output be?

What will the output be?

Result: Excel stared, but did not add workbook... Without ERRORS!

Result: Excel stared, but did not add workbook... Without ERRORS!
Hi Stajner
I just got an email that you'd replied to a post that I was previously involved in: Excel 97 Issue...

Our circumstances are a bit different - I'm using vb .net 2002 / Excel 97 - our only similarity is that automation is a tricky beast!!!

However I have 3 suggestions:
1) Reinstall Excel - it sounds really suspect that the default behaviour of adding a workbook has gone missing... sounds dumb but try it - you might save yourself hours!

2) Mike (Expert + Leader) has suggested in a tutorial to flesh out the object model so that you only ever have one '.' between your operands:
EG:
Dim oApp as new Excel.Application()
Dim oWbsCollection as Excel.Workbooks = oApp.Workbooks <-Returns collection
Dim oBook as Excel.Workbook = oWbsCollection.Add

Dim oSheetsCollection as Excel.Sheets = oBook.Sheets
Dim oSheet as Excel.Sheet = oSheetsCollection.ActiveSheet

...see what I mean? (ps: just typed this off the top of my head - so I apologise
if there are any typos - I just want to show you the [operand].[operand] idea)

This means more com objects to kill when you're done.

3) Encase your dim statements + the rest of your code in one big try catch:

private sub ...
try
//Dim excel stuff
//rest of code
catch ex as system.exception
console.writeline(ex.tostring)
end try
end sub

In the ouput, you'll see ex's message AND stack trace - I only suggest this because I was getting com interop errors - mind you I was trying to open a spreadsheet - but I could tell that the sheet was open because "access denied" appeared in the output - becuase it was crashing. I don't know whether this will help or not... but thought I would share :)

I hope this helps - I now how frustrating it is - Denis is right... have faith mate!

Cheers
MaggieMay77

Hi Stajner
1) I'm using vb .net 2002 / Excel 97
2) Reinstall Excel
3) to flesh out the object model so that you only ever have one
4) Encase your dim statements
5) have faith mate!


Thanks, but...

-1) me to (object library Excel 8 and 11 (but with 8 and 11 i get the same error), I instaled 97 after Office 2003)

-2) OK. I uninstal office 97 and reinstal Office 2003. I also reboot computer. ERRORS are stil there!!!

3) code does not work:

Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
Dim oApp As New Excel.Application()
Dim oWbsCollection As Excel.Workbooks = oApp.Workbooks
Dim oBook As Excel.Workbook = oWbsCollection.Add '<--THE SAME ERROR

Dim oSheetsCollection As Excel.Sheets = oBook.Sheets
Dim oSheet As Excel.Worksheet = oSheetsCollection.Add
End Sub


-4)Code does nothing. I mean, nothing. It skips excel part

Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
Try
Dim xlApp As New Excel.Application()
Dim xlBook As Excel.Workbook = CType(xlApp.Workbooks.Add, Excel.Workbook)
xlApp.Visible = True
Catch ex As System.Exception
Console.WriteLine(ex.ToString)
End Try
End Sub


-5) Thanks once again. I feel reall dummer, becouse I left VB6. I think this was one of the most stupid things i done. I do not know which one to blame: MS, or US antitrust department, or just myself, becouse I am dummer and using vb.net instead of VB6 or even vb5,vb4...

Please help. I am realy confused. I spend entire week (5 days and nights) just do do in VB.NET something, what i usually did in VB6 in a second - to open a Workbook and change just one cell. :confused: Please help. I do not know what else to do. I read all help here and on MSDN, aslo F1 and I do not know where else. The problem remains, and error is still the same:

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred...

Stajner
Thanks to all,
Stajner

Sound not good at all :(

As for VB 6.0 ----> VB.NET I believe that we still need VB6.0 to secure so called production-versions when working with Office COM. I have had several different problems where we (me and clients) have agreed to switch back.

Could You pls provide us with the references that are available in the Solution Explorer.

Meanwhile here is a revised approach:


'On top of the Form:
Imports Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook

xlApp = New Excel.Application
xlBook = xlApp.Workbooks.Add
xlApp.Visible = True

Sound not good at all :(

1) switch back.
2) references in the Solution Explorer.
3) revised approach

-1) Denis, code dose NOT work. I can not help it. I will swich back today. I will format computer, and install everything again without VB.NET. I can not trust him. I newer expacted, that this proram will let me down so hard. I work with it couple of years with SQL and other things, but this with Excel exceeds my limit! I can not be mor terified than to look back this week (form monday afternoon till today), hom much hours I spend just to fix one of MS bugs (BUG is most common word in articles on MSDN for my problem). I had enough. I quit. **** VB.NET!!!

-2) Ok, sorry for that. But I can not think otherwise. But for your help I will give you references. (Excel, Imports Microsoft.Office.Core, System, System.Data, System.Drawing, System.Windows.Form, System.XML, VBIDE). That is it.

-3) As I said code DOES NOT WORK!!! It breaks on 'workbook.add' line. I will go back on VB6. I recommend all with the same problem: "You will never fix this MS bug!". I hope I did not say to much, but only the truth.

Denis, Thank you very much. Realy.
Stajner

Stajner,

Sorry that this has been such a miserable experience for you :(. Installing .Net so that it can interact with MS Office programs such as Excel can be quite tricky.

And the truth is, due to such issues, it is very difficult to feel confident of a VB.Net solution that is Automating MS Office if you have many clients, for they will all likely have such issues.

I personally think that .Net is fantastic and I wish that I could do all my development on it. However, since all my development is on Excel and the COM Interop is both tricky to get working on the client machine as well as executing at slower speeds than in VB6.0, I still find that, realistically, I still have to do all my Excel development in VB6.

But I await eagerly for the day when such interaction is much easier... Excel 12.0 maybe? We'll see, I think it will be a while, unfortunately. :(

-- Mike

Before you give up on .Net, check these links.

http://msdn.microsoft.com/vstudio/office/officetools.aspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/VSTOIntro.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/VSTOFAQ.asp

http://msdn.microsoft.com/library/default.asp?url=/code/list/vsforoffice.asp

http://msdn.microsoft.com/vstudio/howtobuy/officetools/

Hey Reboot,

Those are some really good links...

Stajner, Visual Studio Tools for Office 11.0 (it doesn't work for 10.0 and below) is a nice suite of tools. I don't know if it will help with your installation issues however.

Dennis, Reboot: do you guys know if VS Tools can help with installation? (I've not used it because I'm on Office 10.0.)

-- Mike

First of all, VSTO is the first .NET-version of the MS Office Developer version (or we may call it the present developer-version of Office 2003 as it only works with 2003).

Before installing VSTO I had no problems like the issue discussed here, i e it worked very well with Excel 2003, both early & late binding.

OK, if all other options are tested and You got a copy of VSTO then install it but I'm not sure if it will fix it or not.

However, I remember that during the setup of Office 2003 there was an option to install so call .NET-support-files which I did. Perhaps this is the key?

My own reflection, in addition to the subject here, is if we should write a line for adding a workbook at Excel's startup or not. My findings indicates it has no effect with 2003 so perhaps this may cause the problem?

Like Mike_R I like very much VB.NET but at this stage there are too many weird & strange problems reported when working with Office-automation that need to sorted out and documented in MS KB. However as it looks now many issues for earlier version of Excel will not be fixed and the focus is set to the coming version Excel 12.0.

i get error in workbooks.add

Welcome to the board :)

I strongly suggest that You start a new thread with a relevant subject and it's also necessary to provide the code You're working with.