EXCEL 2003 and VB.NET
EXCEL 2003 and VB.NET
EXCEL 2003 and VB.NET
EXCEL 2003 and VB.NET
EXCEL 2003 and VB.NET
EXCEL 2003 and VB.NET EXCEL 2003 and VB.NET EXCEL 2003 and VB.NET EXCEL 2003 and VB.NET EXCEL 2003 and VB.NET EXCEL 2003 and VB.NET EXCEL 2003 and VB.NET EXCEL 2003 and VB.NET
EXCEL 2003 and VB.NET EXCEL 2003 and VB.NET
EXCEL 2003 and VB.NET
Go Back  Xtreme Visual Basic Talk > > > EXCEL 2003 and VB.NET


Reply
 
Thread Tools Display Modes
  #1  
Old 04-22-2004, 10:00 AM
Stajner Stajner is offline
Newcomer
 
Join Date: Apr 2004
Location: Slovenia
Posts: 7
Question 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 ), 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
Reply With Quote
  #2  
Old 04-22-2004, 12:57 PM
XL-Dennis XL-Dennis is offline
Banned
 
Join Date: Mar 2003
Location: Östersund Sweden
Posts: 908
Default

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.
Reply With Quote
  #3  
Old 04-22-2004, 04:38 PM
Stajner Stajner is offline
Newcomer
 
Join Date: Apr 2004
Location: Slovenia
Posts: 7
Default

Quote:
Originally Posted by XL-Dennis
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:

Code:
Imports Microsoft.Office.Interop

and further:

Code:
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
Reply With Quote
  #4  
Old 04-22-2004, 05:35 PM
XL-Dennis XL-Dennis is offline
Banned
 
Join Date: Mar 2003
Location: Östersund Sweden
Posts: 908
Default

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:
Code:
Imports Excel = Microsoft.Office.Interop.Excel

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

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

Does the error-message still exist?
Reply With Quote
  #5  
Old 04-22-2004, 05:54 PM
Stajner Stajner is offline
Newcomer
 
Join Date: Apr 2004
Location: Slovenia
Posts: 7
Default

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

Code:
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:

Code:
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
Reply With Quote
  #6  
Old 04-22-2004, 06:07 PM
XL-Dennis XL-Dennis is offline
Banned
 
Join Date: Mar 2003
Location: Östersund Sweden
Posts: 908
Default

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

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:

Code:
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.
Reply With Quote
  #7  
Old 04-22-2004, 06:25 PM
Stajner Stajner is offline
Newcomer
 
Join Date: Apr 2004
Location: Slovenia
Posts: 7
Default

Quote:
Originally Posted by XL-Dennis
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.
Reply With Quote
  #8  
Old 04-22-2004, 06:29 PM
XL-Dennis XL-Dennis is offline
Banned
 
Join Date: Mar 2003
Location: Östersund Sweden
Posts: 908
Default

OK, last try this day/night for me:

If You only use:

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

What will the output be?
Reply With Quote
  #9  
Old 04-22-2004, 06:34 PM
Stajner Stajner is offline
Newcomer
 
Join Date: Apr 2004
Location: Slovenia
Posts: 7
Default

Quote:
Originally Posted by XL-DENIS
What will the output be?
Result: Excel stared, but did not add workbook... Without ERRORS!
Reply With Quote
  #10  
Old 04-22-2004, 11:23 PM
MaggieMay77 MaggieMay77 is offline
Newcomer
 
Join Date: Jan 2004
Location: Australia
Posts: 4
Default

Quote:
Originally Posted by Stajner
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
Reply With Quote
  #11  
Old 04-23-2004, 03:16 AM
Stajner Stajner is offline
Newcomer
 
Join Date: Apr 2004
Location: Slovenia
Posts: 7
Unhappy

Quote:
Originally Posted by 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:
Code:
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
Code:
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. 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

Last edited by Stajner; 04-23-2004 at 05:06 AM.
Reply With Quote
  #12  
Old 04-23-2004, 06:27 AM
XL-Dennis XL-Dennis is offline
Banned
 
Join Date: Mar 2003
Location: Östersund Sweden
Posts: 908
Default

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:

Code:
'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
Reply With Quote
  #13  
Old 04-23-2004, 07:00 AM
Stajner Stajner is offline
Newcomer
 
Join Date: Apr 2004
Location: Slovenia
Posts: 7
Angry

Quote:
Originally Posted by XL-Dennis
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
__________________
use tags ['VB'] and ['/VB'] without '
Reply With Quote
  #14  
Old 04-23-2004, 07:24 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

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
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #15  
Old 04-23-2004, 07:25 AM
reboot's Avatar
rebootEXCEL 2003 and VB.NET reboot is offline
Keeper of foo

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Graceland
Posts: 15,614
Default

Reply With Quote
  #16  
Old 04-23-2004, 07:29 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

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
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #17  
Old 04-23-2004, 08:24 AM
XL-Dennis XL-Dennis is offline
Banned
 
Join Date: Mar 2003
Location: Östersund Sweden
Posts: 908
Default

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.
Reply With Quote
  #18  
Old 05-09-2004, 01:00 AM
mzbaharak mzbaharak is offline
Newcomer
 
Join Date: May 2004
Location: iran
Posts: 1
Default

i get error in workbooks.add
Reply With Quote
  #19  
Old 05-09-2004, 04:41 AM
XL-Dennis XL-Dennis is offline
Banned
 
Join Date: Mar 2003
Location: Östersund Sweden
Posts: 908
Default

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.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
VB.Net and EXCEL??? Jamirez .NET Office Automation 6 04-05-2004 02:14 PM
Open VB.NET 2002 project within VB.NET 2003 geo1st487 .NET General 2 01-10-2004 04:13 PM
Run Excel Macro From VB.Net Monkey_Man .NET Office Automation 2 12-18-2003 02:16 AM
PageSetup, Excel XP, and VB.NET? PrimusGreg Word, PowerPoint, Outlook, and Other Office Products 1 04-08-2003 07:37 PM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
EXCEL 2003 and VB.NET
EXCEL 2003 and VB.NET
EXCEL 2003 and VB.NET EXCEL 2003 and VB.NET
EXCEL 2003 and VB.NET
EXCEL 2003 and VB.NET
EXCEL 2003 and VB.NET EXCEL 2003 and VB.NET EXCEL 2003 and VB.NET EXCEL 2003 and VB.NET EXCEL 2003 and VB.NET EXCEL 2003 and VB.NET EXCEL 2003 and VB.NET
EXCEL 2003 and VB.NET
EXCEL 2003 and VB.NET
 
EXCEL 2003 and VB.NET
EXCEL 2003 and VB.NET
 
-->