Thread: VB 2010 Express & Excel

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.

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

Dim Jacks_OBJECT_EXCEL
Set Jacks_OBJECT_EXCEL = WScript.CreateObject("Excel.Application")

Jacks_OBJECT_EXCEL.Workbooks.Add


Jacks_OBJECT_EXCEL.Cells(1, 1).Value = "Jack"
Jacks_OBJECT_EXCEL.Cells(1, 2).Value = "in"
Jacks_OBJECT_EXCEL.Cells(1, 3).Value = "the"
Jacks_OBJECT_EXCEL.Cells(1, 4).Value = "UK"

Jacks_OBJECT_EXCEL.Columns(1).ColumnWidth = 5
Jacks_OBJECT_EXCEL.Columns(2).ColumnWidth = 10
Jacks_OBJECT_EXCEL.Columns(3).ColumnWidth = 15
Jacks_OBJECT_EXCEL.Columns(4).ColumnWidth = 20


Jacks_OBJECT_EXCEL.Range("A1:D1").Select
Jacks_OBJECT_EXCEL.Selection.Font.ColorIndex = 3

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:

Public Class Form1

    Dim Jacks_OBJECT_EXCEL
        Jacks_OBJECT_EXCEL = WScript.CreateObject("Excel.Application")
        Jacks_OBJECT_EXCEL.Workbooks.Add()

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        

        Jacks_OBJECT_EXCEL.Cells(1, 1).Value = "Jack"
        Jacks_OBJECT_EXCEL.Cells(1, 2).Value = "in"
        Jacks_OBJECT_EXCEL.Cells(1, 3).Value = "the"
        Jacks_OBJECT_EXCEL.Cells(1, 4).Value = "UK"

        Jacks_OBJECT_EXCEL.Columns(1).ColumnWidth = 5
        Jacks_OBJECT_EXCEL.Columns(2).ColumnWidth = 10
        Jacks_OBJECT_EXCEL.Columns(3).ColumnWidth = 15
        Jacks_OBJECT_EXCEL.Columns(4).ColumnWidth = 20


        Jacks_OBJECT_EXCEL.Range("A1:D1").Select()
        Jacks_OBJECT_EXCEL.Selection.Font.ColorIndex = 3
    End Sub
End Class

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.

Originally Posted by Vladamir

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?

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.

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.

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

"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.

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)

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

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.

did you try

sub snb()
  with createobject("Excel.Application")
    .visible=true
    with .workbooks.add
      .sheets(1).cells(1).value="so what ?"
   end with
  end with
end sub

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

sub snb2()
  with getobject("E:\OF\example.xlsx")
    .application.visible=true
    .sheets(1).cells(1).value="guess what ?"
  end with
End sub

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.

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

Imports Microsoft.Office.Interop
Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim xl = New Excel.Application
        Dim wb As Excel.Workbook

        wb = xl.Workbooks.Add

        xl.Visible = True

        With wb
            .Sheets(1).cells(1).value = "Kyle"
        End With


    End Sub
End Class