Opening excel files in vb 2005

Opening excel files in vb 2005

• Jim Mace

  ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/dv_vbalr/html/ec11fd03-b420-412f-b25a-057f877cefbc.htm
  The following example uses the CreateObject function to create a Microsoft Excel worksheet and saves the worksheet to a file. To use this example, Excel must be installed on the computer where this program runs. Also, you must add a reference to the type library from the COM tab of the Add Reference dialog box on the Project menu. The name of the type library varies depending on the version of Excel installed on your computer. For example, the type library for Microsoft Excel 2002 is named Microsoft Excel 10.0 Object Library.
  
  
  Sub TestExcel()
  Dim xlApp As Microsoft.Office.Interop.Excel.Application
  Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
  Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
  
  xlApp = CType(CreateObject("Excel.Application"), _
  Microsoft.Office.Interop.Excel.Application)
  xlBook = CType(xlApp.Workbooks.Add, _
  Microsoft.Office.Interop.Excel.Workbook)
  xlSheet = CType(xlBook.Worksheets(1), _
  Microsoft.Office.Interop.Excel.Worksheet)
  
  ' The following statement puts text in the second row of the sheet.
  xlSheet.Cells(2, 2) = "This is column B row 2"
  ' The following statement shows the sheet.
  xlSheet.Application.Visible = True
  ' The following statement saves the sheet to the C:\Test.xls directory.
  xlSheet.SaveAs("C:\Test.xls")
  ' Optionally, you can call xlApp.Quit to close the workbook.
  End Sub
  
  
  

• Carl J

  You still have the problem of how to gain access to the different classes and objects within Excel.

  I did find a solution in the article called "How to automate Microsoft Excel from Visual Basic .NET" at this URL

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

  However, I did want to do more within Excel and just "hook" the VB application into the open Excel file.

  Thanks anyhow

  -Aubrey

  
  

• Angele

  If you just want to open the Excel file

  Process.Start("test.xls")

  Will open the file test.xls with the associated application, Excel. So as long as Excel is on the machine this will open excel with this file already loaded.

  All you need to do is stick this in the button click event on your form.

  
  

• DJO

  Nope. Makes no difference.
  

• Francois Paradis

  I bought Visual Studio 2005 this morning (the professional edition for academics) in the hope that your code might then work because on the web I found some mention that the express freebee did not do these things. So I spent good money as well as a morning loading loads of info in my PC and the installation seems to have worked just fine..
  
  I created a form with a simple button and then created a class that I call from the button_click code.
  the class defines quaternion math and works just fine when I call it from the button_click module.
  
  However, when I cut and paste your above code into the button_click it gives me blue squiggly lines and tells me that 'type 'Excel.application' as well as workbook and worksheet are not defined.
  
  Not surprisingly it does not run either.
  
  
  
  
  
  
  
  

• oiji2000

  I'm not fully understanding what you want to do but this will open an existing workbook for you and give you access to automate it.

  Dim oExcel As Excel.Application

  Dim oBook As Excel.Workbook

  Dim oSheet As Excel.Worksheet

  oExcel = CreateObject("Excel.Application")

  oBook = oExcel.Workbooks.open("c:\Book1.xls", [ReadOnly]:=False)

  oExcel.Visible = True

  oExcel.DisplayAlerts = False

  oSheet = oBook.Worksheets("Sheet1") '.Worksheets("testsheet")

  oSheet.Select()

  oSheet.Range("A1").Value = "Test worked"

  'some other examples of automating the sheet

  oSheet.Range("A2").Select()

  oExcel.ActiveCell.Value = 2

  oExcel.ActiveCell.Offset(1, 1).Select()

  oExcel.ActiveCell.Value = 3

  
  
  

• Harris Sayed

  This looks like page 453 of Visual Basic .NEt by Petroutsos. Unfortunately is I type DimEXC as New Excel.Applicaiton into VB 2005 express it tells me that Excel.Application is an unknown type. Is this becasue you need the commercail version to do this
  
  
  

• darentan

  Thanks: You are right: you first have to create that link by going ot the com tab of 'add reference' om the porject menu item. Actually there seem to be two a version 12 and a version 5.0 I pick the 12 and it seems to work fine. provided you add
  

  Imports Microsoft.office.interop
  

  
  at the top of the module.
  
  So I got it to work but there are still many mysteries. E.g. in VBA the range object has a cells method that you can add a value property to but in visual basic 2005 that does not seem to work.
  
  I tried
  
  mysheet.Range.Cells(aa,bb).value= a1
  
  nope
  but this works
  
  mysheet.Range("a1").Offset(aa,bb).Value= a1
  
  Another question is how do I destroy all these instances of excel I keep creating. It froze up my system to the point I had to physically pull the plug. I now kill the in the task manager.
  
  As to academic versions: I am an academic and I am trying to write some scientific code to unscramble to half a TB or so we collect anytime we go to the synchrotron. No commercial aims just scientific ones.
  
  (Yes sometimes even scientists do program, sometimes it even gets mathematical ::-0)
  
  
  

• ymac

  Change all references from long to integer.
  
  

• bygosh

  you have to add the microsoft excel object library reference for it to work

  project menu - add reference - com tab

  i have been using express and eveything works fine

  the other thing is when you use academic versions, they are for learning only

  the license agreement does not allow you to use it for any other purpose

  you can transfer your code you create with it into vb express or a full paid version though

  the only thing is that you will find some of the controls and code in pro will not be available to you in express

  
  
  

• Latesh Patel

  You can open an Excel file and have access to the Excel object model using code like this:

  ' Start Excel and get Application object.
  Dim xlApp As Excel.Application
  xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
  xlApp.Visible = True
  'open the workbook (read only)
  Dim xlWorkbook As Excel.Workbook
  xlWorkbook = xlApp.Workbooks.Open(Me.txtXLWorkbook.Text, , True)

  If you want the user to take control of the Excel interface, add one more command:
  xlApp.UserControl = True

  
  

• Wolex

  This example works well, but I have tried numerous ways to open an existing file and I just don't seem to find the right sequence. Ideally I would like to check if an excel file is open and then access that file. As an alternative, I could simply open the file.

  The example in the help file for the GetObject function gets close (at least in description)

  Example

  The following example uses the GetObject function to obtain a reference to a specific Microsoft Excel worksheet (excelObj). It uses the worksheet's Application property to make Excel visible, to close it, and to perform other actions. Using two API calls, the detectExcel procedure looks for Excel, and if it is running, enters it in the Running Object table. The first call to GetObject causes an error if Excel is not already running, which in this example causes the excelWasNotRunning flag to be set to True. The second call to GetObject specifies a file to open. If Excel is not already running, the second call starts it and returns a reference to the worksheet represented by the specified file, test.xls. The file must exist in the specified location; otherwise, Visual Basic throws a FileNotFoundException. Next, the example code makes both Excel and the window containing the specified worksheet visible.

  This example requires Option Strict Off because it uses late binding, where objects are assigned to variables of type Object. You can specify Option Strict On and declare objects of specific object types if you add a reference to the Excel type library from the COM tab of the Add Reference dialog box of the Project menu in Visual Studio.

  Visual Basic	Copy Code
  ' Add Option Strict Off to the top of your program. Option Strict Off

  Visual Basic	Copy Code
  ' Declare necessary API routines. Declare Function findWindow Lib "user32.dll" Alias _ "FindWindowA" (ByVal lpClassName As String, _ ByVal lpWindowName As Long) As Long Declare Function sendMessage Lib "user32.dll" Alias _ "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, _ ByVal wParam As Long, ByVal lParam As Long) As Long

  Visual Basic

  Copy Code

  Sub getExcel() Dim excelObj As Object Dim excelWasNotRunning As Boolean ' Test to see if a copy of Excel is already running. On Error Resume Next ' GetObject called without the first argument returns a ' reference to an instance of the application. If the ' application is not already running, an error occurs. excelObj = GetObject(, "Excel.Application") If Err().Number <> 0 Then excelWasNotRunning = True Err().Clear() ' If Excel is running, enter it into the Running Object table. detectExcel() ' Set the object variable to refer to the file you want to use. excelObj = GetObject("c:\vb\test.xls") ' Show Excel through its Application property. Then show the ' window containing the file, using the Windows collection of ' the excelObj object reference. excelObj.Application.Visible = True excelObj.Parent.Windows(1).Visible = True ' Insert code to manipulate the test.xls file here. End Sub

  Visual Basic

  Copy Code

  Sub detectExcel() ' Procedure detects a running Excel and registers it. Const WM_USER As Long = 1024 Dim hWnd As Long ' If Excel is running, this API call returns its handle. hWnd = findWindow("XLMAIN", 0) If hWnd = 0 Then ' 0 means Excel is not running. Exit Sub Else ' Excel is running, so use the sendMessage API function ' to enter it in the Running Object table. sendMessage(hWnd, WM_USER + 18, 0, 0) End If End Sub

  When you call the getExcel function, a check is made to see if Excel is already running. If it is not, then an instance is created.

  Security Note

  For simplicity, the preceding example assumes that any window called XLMAIN belongs to an instance of Microsoft Excel. If another object, possibly launched by illicit tampering, created a window with that name, it would receive all the messages you intended for Excel. In an application to be used for production, you should include some more rigorous testing to verify that XLMAIN really belongs to Excel.

  First of all, no error is detected when no file is open. I modified the code of Err().Number() to Err.Number, but with no avail.

  In addition the hWnd=findWindow... statement in detectExcel() triggers the following run-time error:

  PInvokeStackImbalance was detected

  Message: A call to PInvoke function 'Modbus!Modbus.Form1::findWindow' has unbalanced the stack. This is likely because the managed PInvoke signature does not match the unmanaged target signature. Check that the calling convention and parameters of the PInvoke signature match the target unmanaged signature.

  Can anybody suggest

  a) how to open an excel file from VB

  b)how to get the above code to work. I am using VB Express. My OS is Windows2000 (Version 5.00 SP4)and my version of Excel is Excel2000

  Thanks

  -Aubrey

  
  

• Krad

  To destroy the instance, just call oExcel.quit() or whatever your excel object instance name is

  To help with creating a bunch of instances, you can declare the ojects at the class level and then work with an 1 open book instead of recreating the instance everytime. If that makes it easier.

  Public Class Form1

  Dim oExcel As Excel.Application

  Dim oBook As Excel.Workbook

  Dim oSheet As Excel.Worksheet

  Now you can break up the rest of your code into more events and use them as you need them

  EDIT -- What is it that you are trying to do with the cells

  What do you want the cells method for

  
  
  

• Willis777

  I don't know what your area of expertise is, so you may want to ignore this if you feel it is too far from your comfort zone.

  I used the information I derived from this thread plus my own work to write a two part article "A Generic Modbus Simulator" which appeared in March and April 2007 issues of Circuit Cellar. The actual Modbus map was created as an Excel worksheet and the project opens the Excel file and derives the information which it then formats and transmits over the serial port. All was done with VB2005 Express.

  For a small charge ($1.50 each) you can download the article here

  http://www.circuitcellar.com/magazine/200.html and

  http://www.circuitcellar.com/magazine/201.html

  and the code ( under my last name) is available for free at

  ftp://ftp.circuitcellar.com/pub/Circuit_Cellar/2007/200/

  and

  ftp://ftp.circuitcellar.com/pub/Circuit_Cellar/2007/201/

  -Aubrey

  
  
• Opening excel files in vb 2005