locked
How to open an excel file in Outlook vba code. RRS feed

  • Question

  • In Outlook vba, I want to check a cell value in an excel file. I used the below code for opening the excel.

    Application.Workbooks.Open ("Excel File path")

    But i am getting "Runtime Error = 438 (Object doesn't support this property or method)"

    Can anyone help on the above issue

    Friday, December 30, 2011 7:21 AM

Answers

  • Hello,

    Outlok.Appplication doesn't provide the Workbooks property. You need to create an instance of Excel.Application first, see How to automate Microsoft Excel from Visual Basic .NET.


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader
    • Marked as answer by Calvin_Gao Monday, January 9, 2012 3:19 AM
    Friday, December 30, 2011 8:20 AM
  • Hi Bala,

    Thanks for your post. Please try calling this below  method in your outlook VBA code.

    Function openExcel()

    Dim xlApp As Object
    Dim sourceWB As Workbook
    Dim sourceWS As Worksheet

    Set xlApp = CreateObject("Excel.Application")

    With xlApp
    .Visible = True
    .EnableEvents = False
    End With


    strFile = "C:\Das\SalesSheet.xls"  'Put your file path.

    Set sourceWB = Workbooks.Open(strFile, , False, , , , , , , True)
    Set sourceWH = sourceWB.Worksheets("SalesForm")
    sourceWB.Activate
    End Function

     

    Hope this helps and put mark as answer if its solves your problem.

    Thanks

    Dwipayan Das

     

     


    Dwipayan Das
    • Edited by Dwipayan Das Friday, December 30, 2011 8:20 AM
    • Marked as answer by Calvin_Gao Monday, January 9, 2012 3:19 AM
    Friday, December 30, 2011 8:20 AM
  • Answers

    1) The Basic Programming Language was developed by WANG Corp in the 1960's and was designed to be used by people who didn't have a lot of computer background.  Also back at that time memory was very expensive.  The designer of the language try to make the statements as short as possible to minimize the amount of memory. Also the programming language didn't do a lot of error checking to keep the executable portions of the program small so it didn't use a lot of memory.

    Over the years different companies added addiional error checking.  But Basic (VBA) is still considered very "Error Forgiving" meaing you don't have to declare variables and can easily change one type variable to another (like adding a float number and an integer together).

    The Application.Run is not really using Early Bindings or Late binding since it isn't using any OBJECTs.  It is simply calling a procedure inside another module and passing parameter list.  What the issue is there is no method in VBA to Declare a function/subroutine inside another workbook.  This is a deficientcy of the VBA language.  The declare function would perform error checking to make sure the parameter list is correct (the correct number of parameters and the correct types of parameters).  Instead the error checking isn't performed until the code is run.  Some people may want to call this error checking Late Bindings.  I consider the "BINDING" as objects and not variables.

    2) An OBJECTs is part of Class.  Dynamic Binding is really using a memory pointer to assign a variable to an object which is part of a Class.  Some Class objects are compatible and others are not compatible.  For example a Rectangle is a Shape so you can declare a shape object (Dim shp as Shape)  and bind a rectangle object to the shape object.  You will be able to find the location of the shape (x,y,width, height) but no know the dimensions of the 4 sides.

    You can't Declare an object as a Rectangle and then bind a Circle to the Rectangle.  Like they say "You can't put a round Peg into a Square Hole".

    If you are getting crashes using early bindings then the clases are not exactly the same.  It is possible the new version of software are adding more parameters to an Class making them incompatible.  An object (part of a Class) is a block of memory.  The compatible portions of the Class should be located at the same locations of memory for the Classes to be compatible.

    I guess Late bindings may reduce the number of crashes that could occur.  But if you are developing excel code that is backwards compatible you should make sure you are refrencing the correct verion of the Microsoft Excel XX.X object library.  If you want your application to run with Excel 2003 you should be using 10.0 and not 12.0 or 13.0 or newer.


    jdweng
    • Marked as answer by Calvin_Gao Monday, January 9, 2012 3:19 AM
    Monday, January 2, 2012 3:36 PM

All replies

  • Hi Bala,

    Thanks for your post. Please try calling this below  method in your outlook VBA code.

    Function openExcel()

    Dim xlApp As Object
    Dim sourceWB As Workbook
    Dim sourceWS As Worksheet

    Set xlApp = CreateObject("Excel.Application")

    With xlApp
    .Visible = True
    .EnableEvents = False
    End With


    strFile = "C:\Das\SalesSheet.xls"  'Put your file path.

    Set sourceWB = Workbooks.Open(strFile, , False, , , , , , , True)
    Set sourceWH = sourceWB.Worksheets("SalesForm")
    sourceWB.Activate
    End Function

     

    Hope this helps and put mark as answer if its solves your problem.

    Thanks

    Dwipayan Das

     

     


    Dwipayan Das
    • Edited by Dwipayan Das Friday, December 30, 2011 8:20 AM
    • Marked as answer by Calvin_Gao Monday, January 9, 2012 3:19 AM
    Friday, December 30, 2011 8:20 AM
  • Hello,

    Outlok.Appplication doesn't provide the Workbooks property. You need to create an instance of Excel.Application first, see How to automate Microsoft Excel from Visual Basic .NET.


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader
    • Marked as answer by Calvin_Gao Monday, January 9, 2012 3:19 AM
    Friday, December 30, 2011 8:20 AM
  • Let me explain the difference between late binding (Dwipayan Das solution), and early binding (Smolin solution).

    1. Dwipayan Das Solution

    The Excel application is being defined using the following line

    Set xlApp = CreateObject("Excel.Application")

    Using the above line the VBA compiler will create the application will the code is running which slows down the program.  Also there is no error checking when you use the variable xlApp.  You also don't a list of properties and method available (the schema) while you are typing your VBA macro.

    2.  Smolin solution

    You add the Excel Library reference to your project by going to the VBA menu Tools - Reference - Microsoft Office Excel XX.X Object library.  Using the library will eliminate the disadvantages in  Dwipayan Das Solution.

     

    You can then use these statements in  Dwipayan Das Solution

    From :

    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")

    To:

    Dim xlApp As Excel.Application

    set xlApp = new Excel.Application

     



     

     


    jdweng
    • Proposed as answer by Paul Ceely Thursday, August 11, 2016 4:00 PM
    Friday, December 30, 2011 12:16 PM
  • Wish you all a very happy new year.

    I am completly agreeing to the points suggeted by Joel Engineer but i have certain questions which needs to clarify from this post.

    1) If i have a requirement to generate a Chart from some data sheet ,and the above code is triggered from a managed code (Application.Run(vba macro method), in this scenario can i use early binding as my VBA Macro just resides inside a plain workbook not an excel add-in (.xla)

    2) To my knowledge with dynamic binding the vba code never crashes (though it doesn't give the intellisence schema to a developer while writing the code)with different client versions of excel as it uses the DISPID and do the runtime binding.Please let me know if i am wrong with my statement.

    MS Support has a KB artcile on this :http://support.microsoft.com/kb/247579

    Bala : Nothing from your side !!!!?

    Thanks

     

    Dwipayan Das


    Dwipayan Das



    • Edited by Dwipayan Das Monday, January 2, 2012 6:50 AM Bala
    Monday, January 2, 2012 6:12 AM
  • Answers

    1) The Basic Programming Language was developed by WANG Corp in the 1960's and was designed to be used by people who didn't have a lot of computer background.  Also back at that time memory was very expensive.  The designer of the language try to make the statements as short as possible to minimize the amount of memory. Also the programming language didn't do a lot of error checking to keep the executable portions of the program small so it didn't use a lot of memory.

    Over the years different companies added addiional error checking.  But Basic (VBA) is still considered very "Error Forgiving" meaing you don't have to declare variables and can easily change one type variable to another (like adding a float number and an integer together).

    The Application.Run is not really using Early Bindings or Late binding since it isn't using any OBJECTs.  It is simply calling a procedure inside another module and passing parameter list.  What the issue is there is no method in VBA to Declare a function/subroutine inside another workbook.  This is a deficientcy of the VBA language.  The declare function would perform error checking to make sure the parameter list is correct (the correct number of parameters and the correct types of parameters).  Instead the error checking isn't performed until the code is run.  Some people may want to call this error checking Late Bindings.  I consider the "BINDING" as objects and not variables.

    2) An OBJECTs is part of Class.  Dynamic Binding is really using a memory pointer to assign a variable to an object which is part of a Class.  Some Class objects are compatible and others are not compatible.  For example a Rectangle is a Shape so you can declare a shape object (Dim shp as Shape)  and bind a rectangle object to the shape object.  You will be able to find the location of the shape (x,y,width, height) but no know the dimensions of the 4 sides.

    You can't Declare an object as a Rectangle and then bind a Circle to the Rectangle.  Like they say "You can't put a round Peg into a Square Hole".

    If you are getting crashes using early bindings then the clases are not exactly the same.  It is possible the new version of software are adding more parameters to an Class making them incompatible.  An object (part of a Class) is a block of memory.  The compatible portions of the Class should be located at the same locations of memory for the Classes to be compatible.

    I guess Late bindings may reduce the number of crashes that could occur.  But if you are developing excel code that is backwards compatible you should make sure you are refrencing the correct verion of the Microsoft Excel XX.X object library.  If you want your application to run with Excel 2003 you should be using 10.0 and not 12.0 or 13.0 or newer.


    jdweng
    • Marked as answer by Calvin_Gao Monday, January 9, 2012 3:19 AM
    Monday, January 2, 2012 3:36 PM
  • Thanks again for the detailed explanation on the history of VBA and fundamentals of Objects bindings.

    Its really helpful but i still believe in building excel applications that is backward compatible(No suprises for a user,as its hard to make him understand about the shape and Rectangle examples) as far as usability is concerned.

    In a large scale organiation you will find users using different versions of Office products (Office 2000 to Office 2010) and you have to ship one product to them .

    Dwipayan


    Dwipayan Das




    Tuesday, January 3, 2012 6:05 AM
  • Dwipayan, 

    your code didn't quite work, try this:

    Public Function openExcel()

        Dim xlApp As Object
        Dim sourceWB
        Dim sourceWS
        
        Set xlApp = CreateObject("Excel.Application")
        
        With xlApp
            .Visible = True
            .EnableEvents = True
        End With
        
        strFile = "C:\Users\Public\Documents\Book1.xlsx"  'Put your file path.
        
        Set sourceWB = xlApp.Workbooks.Open(strFile, , False, , , , , , , True)
        Set sourceWH = sourceWB.Worksheets("Sheet1")
        sourceWB.Activate
        
    End Function

    Tuesday, June 10, 2014 7:56 PM
  • Hello Andrei,

    I am using the Microsoft Excel reference and Dwipayan Das's function, however the Macros runs just the first time. If I try to open the file again its opens a blank Excel Window and closes it. Do you know what might cause the problem? When I restart outlook its runs again the first time that I run the Macros, but it does not work properly on my second attempt. 

    Tuesday, August 21, 2018 1:13 PM
  • I found a solution: http://www.vboffice.net/en/developers/open-excel-file-from-within-outlook/

    The code is:

    Public Sub OpenMyExcelFile()
      Dim File$
      Dim Xl As Object ' Excel.Application
      Dim Wb As Object ' Excel.Workbook
      Dim Ws As Object ' Excel.Worksheet
      Dim Rn as Object ' Excel.Range
    
      File = "c:\file.xls"
    
      On Error Resume Next
      Set Xl = GetObject(, "excel.application")
      On Error GoTo 0
      If Xl Is Nothing Then Set Xl = New Excel.Application
      Set Wb = Xl.Workbooks.Open(File)
      Set Ws = Wb.Sheets(1)
      Ws.Activate
      Set Rn = Ws.Range("a1")
      Rn.Activate
      Xl.Visible = True
    End Sub

    Tuesday, August 21, 2018 2:14 PM