VB for excel/access
VB for excel/access
VB for excel/access
VB for excel/access
VB for excel/access
VB for excel/access VB for excel/access VB for excel/access VB for excel/access VB for excel/access VB for excel/access VB for excel/access VB for excel/access
VB for excel/access VB for excel/access
VB for excel/access
Go Back  Xtreme Visual Basic Talk > > > VB for excel/access


Reply
 
Thread Tools Display Modes
  #1  
Old 07-24-2006, 06:53 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default VB for excel/access


Hi!

I have recently been creating applications using VBA but now I need to use VB. The problem I have is finding info. showing how to use VB to manipulate excel/access. Does anyone know of a book (or something else) which shows the a to z of using VB to automate excel and access?

thanks!
Reply With Quote
  #2  
Old 07-24-2006, 08:44 AM
PrOpHeT's Avatar
PrOpHeTVB for excel/access PrOpHeT is offline
Hopelessly confused...

* Expert *
 
Join Date: Mar 2001
Location: Tyler, Tx.
Posts: 3,055
Default

Basically if you are familiar with the VBA aspect, it is easy.

You need to create your .net project as a dll and register it for com.

There is tons of information online for this. You can start here

http://www.google.com/search?hl=en&l...et+dll+for+com

and save yourself buying a book.
__________________
When you earnestly believe you can compensate for a lack of skill by doubling your efforts, there's no end to what you can't do ;)

For the love of Gold...
Reply With Quote
  #3  
Old 07-24-2006, 08:56 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Well I'd get my boss to buy the book actually!

Thanks I took a quick look there but I'm not really too sure what you mean. So I save the project as dll and register it for com (whatever that means?) and then this allows me to run a VBA project from the .net platform or what does it do?

Thanks for your help!
Reply With Quote
  #4  
Old 07-24-2006, 09:14 AM
PrOpHeT's Avatar
PrOpHeTVB for excel/access PrOpHeT is offline
Hopelessly confused...

* Expert *
 
Join Date: Mar 2001
Location: Tyler, Tx.
Posts: 3,055
Default

No it allows you to program in VB and then compile it as a dll that can be used in VBA projects.
__________________
When you earnestly believe you can compensate for a lack of skill by doubling your efforts, there's no end to what you can't do ;)

For the love of Gold...
Reply With Quote
  #5  
Old 07-24-2006, 10:26 AM
jwakeman's Avatar
jwakeman jwakeman is offline
Contributor
 
Join Date: Jul 2005
Posts: 420
Default

It sounds to me like you might be more interested in this:

VB.Net Office Automation FAQ

The links on this page were good enough to show me how to open excel, manipulate it, and even run macros contained in excel books.

p.s. check out the .net office automation forum, it is geared toward this topic!

http://www.xtremevbtalk.com/-net-office-automation/
Reply With Quote
  #6  
Old 07-25-2006, 06:32 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Thanks yeah I noticed yesterday that I stumbled into the wrong forum!
Reply With Quote
  #7  
Old 07-29-2006, 02:05 PM
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

JFitz, Are you looking to use VB.NET here or VB 6.0?
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #8  
Old 08-04-2006, 05:39 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Hi Mike, I am looking to use VB.net.
Reply With Quote
  #9  
Old 08-04-2006, 07:40 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 JFitz,

I would start with these two links:

VB.Net Office Automation FAQ
Automating Office Programs with VB.Net / COM Interop

As for a book, I really do not know of any good ones. The books by Lippert and Carter on Visual Studio Tools (VSTO) are excellent for using VSTO, but for general Automation or making a .NET Addin for MS Office applications, you'll do better by getting information off of the Internet.

I don't know of any good sources for Access unfortuntely. However, virtually all MS Office .NET Automation and Addins are based on the same principles, so if you read a tutorial on Excel it will be the same -- other than the specific object model involved, of course.

I hope this helps ,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #10  
Old 08-04-2006, 10:35 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default Code not working

Thanks Mike, I'll definitely take a look. I had noticed one thing though in looking through your tutorials... you have code like

Dim oApp As New Excel.Application
Dim oWB As Excel.Workbook = oApp.Workbooks.Add()
Dim oWS As Excel.Worksheet = CType(oWB.Worksheets(1), Excel.Worksheet)

But if I paste this into a VB module the second two lines come up in red (i.e. there's something wrong with the code). I mentioned this to someone and they said that it's because you can't write a Dim statement like that... you'd have to write two statements, one declaring the workbook and then another adding the workbook or something like that. What's going on?

Thanks, JF
Reply With Quote
  #11  
Old 08-04-2006, 10:52 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 JF,

Ok, just to be very clear: we are talking about VB.NET, right?

Whoever you talked to would be correct for VBA or VB 6.0, but for VB.NET a single line declaration and assignment is allowed. In fact, assigning an initial value is often preferred.

My guess is that you did not assign your references or your Imports statements correctly. (Either that or you are not actually using .NET?)

What does the error message actually say for these lines?

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #12  
Old 08-04-2006, 07:44 PM
dilettante's Avatar
dilettanteVB for excel/access dilettante is offline
Underclocked lifestyle

Forum Leader
* Guru *
 
Join Date: Feb 2005
Location: Michigan, USA
Posts: 4,529
Default

Classic VB (i.e. VB6 for most purposes) can interact with Excel and Access in at least 3 ways:
  1. From outside (OLE Automation of an instance of the Office program)
  2. From inside (generally as a compiled component invoked internally using VBA or VBScript)
  3. From... around the side (manipulating the MDB or XLS file via a data access component like ADO plus Jet)

.Net languages can do (1.) and (3.) for sure, and probably (2.) as well through some sort of COM Interop/Fusion technique.

VSTO (an extra $800 USD) gives you a way to use .Net languages in place of VBA internally (more or less) too.
Reply With Quote
  #13  
Old 08-04-2006, 08:24 PM
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 Dilletante,

Good summary... I think that JF is using Automation (choice #1), based on the code he is showing. But because of the error that he is getting, I'm not 100% sure that he is actually using .NET. We'll have to see what he says when he gets back.

Controlling Excel, Access or other MS Office apps "from the Inside" (choice #2) is usually done using VBA, or a COM DLL that implements IDTExtensibility2. Using .NET we can do the same by exposing the .NET assembly to COM and implementing IDTExtensibility2.

As you said, Visual Studio Tools (VSTO), allows one to create a document-centric solution and so could be thought of as "VBA.NET", if you will -- although the code is not as tightly bound to the document. That is, the code sits separate from the document itself, unlike VBA code which is embedded within the document.

Because of security/trust issues, and multi-version compatibility problems, .NET deployment for MS Office solutions is tough, and even more so for VSTO. Using VBA or even VB 6.0 is much, much easier. That said, I personally love .NET and give as much advice as I can on the subject. But unless one has a dying need for .NET's language advantages such as Inheritance, Generics, Option Strict, etc., the deployment issues tend to overwhelm the advantages for MS Office solutions. Only in large, professional programs, or perhaps for .NET programmers who would only consider managed solutions, does this equation tilt in favor of .NET.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 08-04-2006 at 09:00 PM.
Reply With Quote
  #14  
Old 08-08-2006, 09:06 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Hi Mike,

Yes you're right, I copied the code into a VBA module and VB5 to see if it worked, which it of course wouldn't have from what you've said.

I should be getting Visual Studio 2005 in the next few days and so I'll use it then.

Itís hard to understand what you guys are talking about above J but it sounds like youíre saying that .NET isnít the best platform for performing office automationÖ i.e. youíd be better off using VBA (which I have been) or VB6.

My manager here wants to move the applications that Iíve made (which are fairly simple reallyÖ just generally involving manipulation of cells in excel, inserting formulas etc up to running queries and using ADO (all from excel)), so he wants to move these applications onto a more professional platform, which made sense to me as there are limitations to VBA (itís not a complete language). I havenít had too many problems with VBA as yet (I can always find some workaround if I have a problem) but potentially in the future if the applications were to become more complicated I assume there will be problems with VBA.

And so one of the things Iíve been pondering over is just what to do. After consideration, Visual Studio 2005 seemed to be the right choice and since youíve made some points against itís use Iím wondering what you think of this situation!

Iíve learned a lot of VBA at this stage and Iíd be happy enough to increase the difficulty level of the programs in order to learn VB (good for my CV!) and Iím assuming VB.NET is the best language and version to learn at the moment (Iím the only one in my office at the moment who has even the faintest knowledge of VB/VBA and so I can do pretty much well what I want as long as there isnít too much expense J.) What would your view be on that situation?

Thanks a lot for your help,

JF
Reply With Quote
  #15  
Old 08-08-2006, 02:29 PM
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

You cannot make the leap to .NET until you are ready. So get VS 2005 (or the Express version, which is free) and then get yourself a good .NET book. I would go with VB.NET, because it is an easier transition from VBA than to jump to C#, which will be hard for you.

On the other hand, if the firm you work with is a strict C# shop, then you need to make C# a priorety. In either case, I would either focus on VB.NET and learn C# on the side, or focus on C# and learn VB.NET on the side. As a .NET MS Office developer you sort of need the perspective of both... At least for a while.

It will take you at least 3 mos. solid to become reasonably proficient with .NET -- not a master by a long shot, but starting to feel comfortable.

The problem with .NET currently is that although it is a fantastic language, it has issues dealing with complex legacy COM applications such as Excel. Deployment and multi-verson compatibility are two major headaches. VBA and VB 6.0 are much, much easier to use with MS Office applications compared to using .NET.

My advice to you would be to progress your skills by learning how to make a COM addin for Excel by using VB 6.0. This will move you up a rung in your capabilites. Don't go too far into VB 6.0, just learn enough to make an Addin and to control Excel via Automation. Past that, learn .NET as fast as you can... but the reality is that VBA & VB 6.0 will be your platform of choice for a LONG time before you would prefer .NET. (I still don't use .NET for actual development myself, although, I think I'm close...)

Eventually, you might become so proficient at .NET and so comfortable with the deployment issues and multi-version compatiblity that you'll *want* to make the change. Until then, you really should stick with VBA and/or VB 6.0 as your development platform for Excel.


Here are two links that can help you get started if you haven't seen these yet to get a sense of using .NET and Excel:

Automating Office Programs with VB.Net / COM Interop
VB.Net Office Automation FAQ

And here's a couple recarding using VB 6.0 and Excel:

Automating Excel from VB 6.0 Tutorial
How to build an Office 2000 COM add-in in Visual Basic

Just my 2c...
I hope it helps!
,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 08-08-2006 at 05:25 PM.
Reply With Quote
  #16  
Old 08-08-2006, 05:34 PM
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

Hmm... and I want to address this quote you made directly:
Quote:
so he wants to move these applications onto a more professional platform, which made sense to me as there are limitations to VBA (itís not a complete language). I havenít had too many problems with VBA as yet (I can always find some workaround if I have a problem) but potentially in the future if the applications were to become more complicated I assume there will be problems with VBA.
There is nothing wrong with VBA, except maybe it's ability to scale and it's ability to be called from other applications (other than those also running from within Excel). Any other problem other than that, and it's your (bad) code, not VBA that's at fault.

VB 6.0 improves scale and compiles to native code and so the source code cannot be hacked nearly as easily (if that's a concern). It also scales better than VBA for larger projects and can be compiled to a dll which is callable from any other COM application or COM dll. The only downside to using VB 6.0 is that the dll must be registered -- it's not copy-paste as with VBA -- but this is a fairly minor hassle and generally worth it for large projects. (Although, generally not worth it for small projects, or document-specific solutions where your code needs to be tightly bound to a given Workbook.)

.NET scales the best, it definately can handle much larger projects more easily than can VB 6.0 (and certainly better than VBA) and with 'Option Strict' enforces a very strict-typing discipline that is a very nice help to the coder. It also has advanced features such as Inheritance, Generics and a vast library of commands in the Base Class Library of .NET Framework itself. The .NET language, however, will take you a while to master, and has deployment and multi-version compatibility issues when working with Excel... So you really cannot just say "it's better" and jump right in, because for MS Office applications such as Excel, it really does have some difficult hurdles to master.

Again, I think that VB 6.0 is the correct next step for you, but learn .NET on the side with zeal...
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #17  
Old 08-09-2006, 09:28 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Thanks so much for your input.

Seems to me that converting my applications from VBA to VB6 to VB.net would require a lot of work.

What might be better is that I continue using VBA and, as you suggest, fervently learn vb.net on the side until I know it well enough and am comfortable enough to convert. It would just require me using VBA in the meantime (which I am already fairly comfortable with) instead of VB6. The jump would be bigger but there's not a huge hurry to convert.

If you're sure this would be a mistake though I'd go the longer route?

Many thanks,

JF
Reply With Quote
  #18  
Old 08-09-2006, 11:49 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

Yes, I think sticking with VBA at this point, but learning .NET on the side is perfect.

However, I would learn how to use VB 6.0 and Excel as well. It is VERY easy once you know VBA, and understanding the basics of VB 6.0, it will help you when you move on to .NET. I would read these two:

Automating Excel from VB 6.0 Tutorial
How to build an Office 2000 COM add-in in Visual Basic

I would read them both first on your own time, and then if your firm provides a copy of VB 6.0, I would try to follow these step-by-step's yourself. Worst case, maybe try and get some time on a co-worker's machine that has VS 6.0 installed, if you have to. (Best if it's on your own PC, but you do what you have to do...)

I would not seek to *master* VB 6.0 however. It is essentially the same as VBA, so I would only learn enough to be able to deploy a COM addin within Excel and to be able to Automate Excel from a VB 6.0 application. Once you have that down, I would stop. At that point you will then have the option of making VBA solutions or VB 6.0 addins, which is all you need.

By the way, I would not *convert* any VBA solutions to VB 6.0, unless there were a good reason to put the time in. It's just worth learning VB 6.0 so that you have a choice when you design future projects and so that you also build up some COM understanding before moving on to .NET.

From there I would learn .NET on the side, which will take quite a while before you are comfortable enough to use at work, but it is the future and it is an outstanding language, so I would definately learn this on your own time...

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #19  
Old 08-10-2006, 05:37 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Hi Mike,

On reflection it seems the best platform to use at work really is vb6 (for what I need to do) and that's what I think I'll go for. I can get a copy of vb.net and so I'll learn it on the side but I don't think I'll use it for work (seems a bit risky!). It might be better to wait until they've upgraded vb.net so that it can be used more efficiently with office before going wholeheartedly for it.

Thanks again, your input has really helped!

JF
Reply With Quote
  #20  
Old 08-10-2006, 08:43 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

Sounds good!

Yes, I have confidence that at some point VSTO will be better integrated and deployment and cross-version compatibility issues will become much easier. And if you are learning .NET, you will eventually become strong enough that you are able to understand these complexities and are willing to work with .NET and Excel, even if it's tricky.

At some point the lines will cross, so all you can do is learn .NET on the side until one day it feels like the right choice. You'll know when that day arrives, you'll feel it...

Good luck! ,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
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

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
VB for excel/access
VB for excel/access
VB for excel/access VB for excel/access
VB for excel/access
VB for excel/access
VB for excel/access VB for excel/access VB for excel/access VB for excel/access VB for excel/access VB for excel/access VB for excel/access
VB for excel/access
VB for excel/access
 
VB for excel/access
VB for excel/access
 
-->