VB 2010 Express & Excel

[Vladamir]

I am attempting to write some code in VB 2010 Express to interact with Excel 2007 spreadsheets. However, thus far all my attempts have fallen flat. I have searched and found many articles on the subject but no matter which way I try it the codes fail to compile in VB 2010 Express. Here is an example:

http://support.microsoft.com/kb/219151

This is an article on the MSDN website and yet when you follow through the instructions on this, it will not run or complete the build.

I'm looking for a simple block of code which will allow me to access an Excel file, read and write some data to it and then close. I've been able to make this work using VBA but all the news lately is that VBA will soon be history in favor of VB.NET. Trouble is, all the docs seem to be outdated.

[Jack in the UK]

This will open Excel and write some data .. should get you started...

Once you got that working i am happy to try add to this code, see if it works first

jiuk

Please Login or Register  to view this content.

[Vladamir]

Jack,

Many thanks for your reply. I'm still quite new at this and don't fully understand something because this just caused a couple of errors to appear when I ran it.

I opened a new Windows Application Project, added the Microsoft Excel 12.0 Object Library Reference and then opened the code for the button I installed in the blank form. I then inserted your code like this:

Please Login or Register  to view this content.

I then started pressed F5 to start the debugging and got 2 error messages about "Declaration expected". Sorry, but I'm still too inexperienced to know where to go next for resolution.

[Jack in the UK]

This is why . its in a form you did not say that ie in VBExpress so my code will fail

My codes a bit VBS really and opens excel and write to the cells

try copy my code to txt file and change to VBS then run see it the magic works... if so ok we might be able to to more in the morning


the key the object and names to locking objects ir workbooks and drill down to the the tasks required

[OnErrorGoto0]

all the news lately is that VBA will soon be history in favor of VB.NET.

That is news to me - where did you hear that?

[Jack in the UK]

Rory
I hear a lot, that’s not quite right things are changing i.e. Metro interface and the cloud

Jacks say is no it will be VBA or VBS(Sorts) outside Excel as its cloud based or you run them local same way

Win 8 and Office 17 (whatever its called) next two versions will show us. Were easy save for few years yet

jiuk

[Vladamir]

Most of the applications I'm working on are for AutoCAD, and AutoDesk has already announced they no longer support VBA. They still and will still support VLISP, but VBA is going by the wayside. I can't recall the other source I read about this but it was from M$ themselves, that VBA is being abandoned in favor of .NET. We're just trying to not get caught in the cross-fire in 24 to 36 months from now so I"m making the move to get moved over to VB.NET and try to be ahead of the curve.

There appears to be some setup code needed in the beginning of a VB program to attach to Excel. And what works in VBA, and VBS is apparently not the same syntax as VB 2010 Express. I'm using Express right now because I don't have $900 US to shell out for a personal copy of VBS for the moment. Express is supposed to be able to connect to Excel but so far all the examples I've found will not work. Thanks again for the assistance and if there are any additional references you know of I'd appreciate it.

[OnErrorGoto0]

There is absolutely no chance of MS abandoning VBA in the next few years. They still haven't got rid of XLM after all. It is true that MS focus is not on developing VBA, but to suggest it will soon be abandoned is ridiculous. That would be commercial suicide.

[Vladamir]

There is absolutely no chance of MS abandoning VBA in the next few years. They still haven't got rid of XLM after all. It is true that MS focus is not on developing VBA, but to suggest it will soon be abandoned is ridiculous. That would be commercial suicide.

"The final release was version 6 in 1998. Microsoft's extended support ended in March 2008 and the designated successor was Visual Basic .NET (now known simply as Visual Basic)."

That puts the final release of VBA at 14 years ago, which is like a century in the software business. Let's not argue this point. The syntax for VB.NET is very different than VBA and that is my question. How does one go about opening and reading and writing to an Excel file with VB 2010 Express. I discovered several new methods which involve 3rd party software which claim to be 100's of times faster than the M$ method. Probably true, but at $900 to $1,200 US per seat, I just need to stay focused on how to get VB 2010 Express working with my spreadsheets.

[OnErrorGoto0]

I don't know what you are quoting from but that refers to VB and not VBA. VBA is currently on version 7.

The code you linked to originally is VB6 and not VB.Net, so perhaps you could tell us what you tried in VB.Net? (Also, bear in mind this is an Excel forum and not a .Net forum)

[Kyle123]

You can open and edit Excel with Automation or interop services this is easy and the most flexible - you don't have to worry about the differences between .xls and later xml based formats, it is also the slowest. There are also additional requirements like having to have Excel installed on the machine that the code is running on, which is a no-no in Web based environments. That said, you haven't said what you are looking to achieve.

If you are just looking at creating excel workbooks that contain data dumps, csv is a very valid (and simple) approach.

If you are looking for something with a little more control, for .xlsx onwards:

Have you had a look at the MS open XML SDK? http://msdn.microsoft.com/en-us/libr...ffice.14).aspx.

For earlier file formats, I've used NPOI which is very good - http://npoi.codeplex.com/

Both of the above are free, but understand that the difference between and xls and xlsx file is huge!!

If you are looking for an Excel automation/Interop approach, have a look here: http://www.java-samples.com/showtuto...utorialid=1039

It's VB so should be easy to follow, you'll find there are more examples in C# though which should be easy enough to follow

[Vladamir]

I appreciate your replies. The code I tried is in the link in my original posting. It's from a Microsoft site which says that it will automate Excel.....that's as in Excel which you are reminding this forum is about. I submit that I too don't know the answer, but that is why I'm posting my question in an Excel "Programming" forum.

What I have tried is just about every snippet of code I could find which claims it will allow VB.NET to connect with Excel and thus far I found no success with VB 2010 Express. I did find a 3rd party piece called Gembox which I got to work, But I cannot choose this option because it will eventually cost my budget $1,000 per seat and the budget for this project is only what we can do with M$ products.

And thanks Kyle. Your reply came in while I was typing. I need only to open an existing Excel file and then read and write some data from/to it, then close it. My code will then write out a text file which AutoCAD's VLISP will process and create a drawing for the sales people. That part I have covered. And I have it working with VBA...but in anticipation of having to move to the .NET format, I'm trying to get it to work using VB 2010 Express.

[snb]

did you try

Please Login or Register  to view this content.

or with an existing Excel file e.g. E:\OF\example.xlsx

Please Login or Register  to view this content.

[Vladamir]

Again, thank you, but I guess my original question got lost in the politics. I can follow the methods for reading and writing to cells. What is not working are the opening stanzas of code like Import Load Excel.Interop and objExcel=Excel.Application....again my lack of experience and my need to finish shaving and get to the office is showing...but I have tried many combinations of code I found and so far nothing works with VB 2010 Express...I either get Non-Declaration errors, or the IDE fills up with little red tick marks that require correction and when I examine them I get often 4-5 different solutions on how to resolve it or non at all.

[Kyle123]

have you tried something like the below? You'll also need to set a COM reference to the Excel Object Library

Please Login or Register  to view this content.

[OnErrorGoto0]

I appreciate your replies. The code I tried is in the link in my original posting. It's from a Microsoft site which says that it will automate Excel.....that's as in Excel which you are reminding this forum is about. I submit that I too don't know the answer, but that is why I'm posting my question in an Excel "Programming" forum.

The Excel Programming forum is really about programming in Excel, not trying to manipulate Excel with any other language possible, but you never know your luck.

What I have tried is just about every snippet of code I could find which claims it will allow VB.NET to connect with Excel and thus far I found no success with VB 2010 Express.

Perhaps you can post a .Net version that you have tried and explain what error(s) you got. Most examples should work with Excel assuming you have the correct PIA referenced.

but in anticipation of having to move to the .NET format, I'm trying to get it to work using VB 2010 Express.

Perhaps you are simply trying to be prepared a long way in advance, but just to reiterate: VBA is not going away anytime soon.

[snb]

without any reference to Excel in VB2010 Express:

Please Login or Register  to view this content.

after that you can open E:\TextFile3.csv in Excel.

[smuzoen]

I have been writing applications in Visual Studio using VB.net for a long time and you certainly can interact with Excel - you need to use the correct imports for it to work - This is really not the right forum for this type of question however for what it is worth.
1. Make sure you use the correct Imports

Please Login or Register  to view this content.

And just an extract of working with an excel workbook

Please Login or Register  to view this content.

This is a simple application which writes data from a form to an Excel Workbook. With the correct references I guarantee that VB.net can talk to Excel.

[Vladamir]

Thanks many times over for the advice. I am still new to setting up VB to talk with Excel and it's not easy to understand all the prerequisites required. Here is what I have tried which did not work. Remember, I am using VB 2010 Express which is a limited version of VB but according to all I have read will do what I want to do.

Please Login or Register  to view this content.

This is straight from the Microsoft website and it doesn't even have the Imports line in it. Which sadly, I found out is required...or is it? That's been my problem in searching for how to get VB 2010 Express to talk to the Excel file in the first place. No two methods I've read about are the same, none of them have worked and the only way I did get it to work, and fairly simply I must add, was to use a 3rd party program "Gembox". And to answer your question, yes, with each new attempt I have add the Microsoft Excel 12.0 Object Reference. The IT guy is supposed to give me permission to load VB 2010 Express on this box today so I will try this new example you offered a little later. Again, many thanks for sticking with me on this issue.

[OnErrorGoto0]

The presence of an On Error Goto statement tells me that that again is not VB.Net code - it's VB6.

[Kyle123]

Indeed that is VB6 code.

Whether you need to import the object library very much depends on whether you are using late binding or early binding.

If you are late binding as per snb's and your example above, there is no need to set the reference and import. Late binding is where you are using the CreateObject() method to create the instance of Excel, early binding is as my example and smuzoen's

[OnErrorGoto0]

Late binding is where you are using the CreateObject() method to create the instance of Excel

That's not strictly true. You can use CreateObject with early binding. Binding only relates to the variable declaration. If you declare as Object (or don't declare) then it's late bound regardless of how you instantiate it.

[smuzoen]

The way I work with VB.net is that I use Imports to make it easier to code - shorter names can be used.
For example with no Imports

Please Login or Register  to view this content.

With Imports

Please Login or Register  to view this content.

By using System.IO in the Namespace I can shorten my code as in the second code example.
You do not have to use the Imports statement it just makes coding easier.
If you are going to write an application to work with Excel then on the Project Menu (e.g. for a Windows Application) click Add Reference and on the Com tab select Microsoft Excel Object Library. You are now ready to interact with Excel from VB.net

[Vladamir]

Ok, let me see if I can report my latest effort without confusion...because I am confused. I opened up VB 2010 Express and started a new project, a Windows Application, which of course brought up a form. I will eventually be using a form for the users to input their data and to place some of the data read from the spreadsheet so I guess this is a good place to start. I add the reference for Microsoft Excel 12.0 Object Library. I then put a single button in the form and then double click on it to bring up the code window. Now perhaps this is where I'm doing it all wrong but in this code window I place the code above like this:

Please Login or Register  to view this content.

This immediately produces 35 errors in the error list window before I even start the Debugging process, so obviously I'm doing something very wrong.

[snb]

So why are you using vb2010 express in the first place ?
Probably you can accomplish anything you want using Excel and it's VBA facilities .

[Vladamir]

As Edmond Hillary said...."because it's there". No disrespect guys, but my question was all about VB 2010 Express. That's the tool I'm selecting to use because according to what I have read, it will work. I already have this working in VBA inside of the Excel spreadsheet. I am trying to look forward to what the next 24 to 36 months will bring and the word is that VB6, VBA et.al will no longer be the norm in favor of .NET. I know for sure that AutoDesk has formally announced they have abandoned VBA and will only be working with .NET and that's what my focus will be on. Again, let me apologize for my curtness, but this is not about VBA...it's about VB 2010 Express and being able to use it to access an Excel file. And again, I have done it but only with using a 3rd party package (Gembox). I am attempting to do this with the Microsoft tools because the company has already made that investment. Every user has a copy of Office 2007 and most of them have AutoCAD 2009 or later. The trick now is to discover a way to keep us on the edge so in 2-3 years we don't look back wondering why we didn't start the change sooner.

[smuzoen]

There is a difference between errors and warnings. I only posted part of my code. You have many undeclared variables in the code for example which will prevent it from compiling. I will give you the solution and this is the complete code. It may help you more however this forum is for excel vba not vb.net. Click on the file ReferralRegister.sln and this will open Visual Studio Express. I write using the full Visual Studio suite so see how you go.
Remember Google is your friend.
PS: I cannot support you past this example as I am surprised the moderators here have not put a stop to this thread already

[Vladamir]

Many thanks. I guess your point is correct. Perhaps this is not the correct forum to ask questions about VB 2010 Express. I will seek my resolutions elsewhere. I was able to open the code you sent and will do my best to learn from it.

To close this thread out I should mention that tonight I finally found what I was looking for at the Microsoft website. This link is to a very similar article as the first but it is definitely for .NET and not just VB or VBA. It worked first time and without complications and all of it worked just like it claimed to.

http://support.microsoft.com/kb/302094

Thanks everyone for your assistance. This has me headed in the right direction now.