Thread: VB 2010 Express & Excel

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

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.

without any reference to Excel in VB2010 Express:

Sub snb2()
 My.Computer.FileSystem.WriteAllText("E:\TextFile3.csv", "nevertheless", false)
end sub

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

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

Imports Microsoft.Office.Interop

And just an extract of working with an excel workbook

' Create a new instance of Excel and start a new workbook.
                objApp = New Excel.Application
                'objApp.Visible = True

                objBook = objApp.Workbooks.Open(fileLocation)
                objSheet = objBook.Worksheets(1)
                'Unprotect Workbook with macro
                objApp.Run("UnProtectAll")
                objSheet.Select()
                dataSet(0) = Format(DateTimePicker1.Value, "dd/MM/yyyy")
                lastRow = objSheet.Range("A65000").End(Excel.XlDirection.xlUp).Row
                lastRow = lastRow + 1
                For t = 1 To 10
                    If t = 8 Then
                        objSheet.Cells(lastRow, t) = "'" & dataSet(t - 1)
                    Else
                        objSheet.Cells(lastRow, t) = dataSet(t - 1)
                    End If
                Next
                'copy down the Fate of App Drop Down Box (dont do on 1st entry)
                If lastRow > 4 Then
                    objSheet.Range("K" & lastRow - 1).Copy()
                    objSheet.Range("K" & lastRow).PasteSpecial()
                End If
                MsgBox("Referral Register has been updated successfully")

                'reprotext workbook
                objApp.Run("ProtectAll")

                'save and close workbook and exit ONLY THIS EXCEL Instance
                objBook.Save()
                objApp.Workbooks.Close()
                objApp.Quit()
                NAR(objApp)

                'Garbage cleanup
                GC.Collect()

                'clean up
                objApp = Nothing
                objBook = Nothing
                objSheet = Nothing
            End If

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.

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.

Private Sub Command1_Click()
      Dim oXL As Excel.Application
      Dim oWB As Excel.Workbook
      Dim oSheet As Excel.Worksheet
      Dim oRng As Excel.Range
      

      'On Error GoTo Err_Handler
      
   ' Start Excel and get Application object.
      Set oXL = CreateObject("Excel.Application")
      oXL.Visible = True

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.

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

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

Originally Posted by Kyle123

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.

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

Dim dirInfo As New System.IO.DirectoryInfo("c:\")

With Imports

Imports System.IO

Public Function GetFolders() As String
Dim dInfo As New DirectoryInfo("c:\")

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

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:

Imports Microsoft.Office.Interop

Public Class Form1

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

' Create a new instance of Excel and start a new workbook.
                objApp = New Excel.Application
                'objApp.Visible = True

                objBook = objApp.Workbooks.Open(fileLocation)
                objSheet = objBook.Worksheets(1)
                'Unprotect Workbook with macro
                objApp.Run("UnProtectAll")
                objSheet.Select()
                dataSet(0) = Format(DateTimePicker1.Value, "dd/MM/yyyy")
                lastRow = objSheet.Range("A65000").End(Excel.XlDirection.xlUp).Row
                lastRow = lastRow + 1
                For t = 1 To 10
                    If t = 8 Then
                        objSheet.Cells(lastRow, t) = "'" & dataSet(t - 1)
                    Else
                        objSheet.Cells(lastRow, t) = dataSet(t - 1)
                    End If
                Next
                'copy down the Fate of App Drop Down Box (dont do on 1st entry)
                If lastRow > 4 Then
                    objSheet.Range("K" & lastRow - 1).Copy()
                    objSheet.Range("K" & lastRow).PasteSpecial()
                End If
                MsgBox("Referral Register has been updated successfully")

                'reprotext workbook
                objApp.Run("ProtectAll")

                'save and close workbook and exit ONLY THIS EXCEL Instance
                objBook.Save()
                objApp.Workbooks.Close()
                objApp.Quit()
                NAR(objApp)

                'Garbage cleanup
                GC.Collect()

                'clean up
                objApp = Nothing
                objBook = Nothing
                objSheet = Nothing
            End If

      End Sub
End Class

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.

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 .

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.

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

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.