Forum Discussion

obaid_shirwani's avatar
obaid_shirwani
Contributor
10 years ago
Solved

Unable to find the APIs for COM / Excel

Hi all,



I was looking for the API for COM/Excel but with no success. I actually want to know what other functions are exposed and what else can I do with Excel / COM.



All the functions I know now is from this thread: http://support.smartbear.com/viewarticle/20878/



Regards,



Obaid Shirwani
  • HKosova's avatar
    HKosova
    10 years ago
    The Excel COM API reference is here:

    Excel developer reference

    Excel object model



    Sys.OleObject("Excel.Application") returns the Application object. It's the main object, from which you can access all other objects.



    Using the COM API, you can do basically anything you can do through Excel's GUI. There're lots of VBScript examples online. Just search for "excel vbscript ...", for example, excel vbscript create chart. Even if you are using another scripting language, these examples can be useful.

6 Replies

Sort By
  • HKosova's avatar
    HKosova
    SmartBear Alumni (Retired)
    10 years ago
    The Excel COM API reference is here:

    Excel developer reference

    Excel object model



    Sys.OleObject("Excel.Application") returns the Application object. It's the main object, from which you can access all other objects.



    Using the COM API, you can do basically anything you can do through Excel's GUI. There're lots of VBScript examples online. Just search for "excel vbscript ...", for example, excel vbscript create chart. Even if you are using another scripting language, these examples can be useful.
  • Colin_McCrae's avatar
    Colin_McCrae
    Community Hero
    10 years ago
    What scripting language are you using?



    I've never found a comprehensive piece of documentation which explains everything you can do with Excel via COM, but either googling, or recoding a macro in Excel itself and then inspecting the code (VBA) that generates will usually get you on the right path.



    In a nutshell, just about anything you can do manually with Excel, you can do via the Excel COM object. I format, filter, move, add/remove sheets ... etc etc. (As well as the obvious reading and writing of data)



    Some starters for 10:



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

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

    http://stackoverflow.com/questions/16881025/automate-excel-with-vbscript-to-process-nagios-logs



    .... and google will expose TONS more.



    Be careful if you use the macro recorder. It's handy as a starting reference, but there are differences between what it generates, and what you need to do from other scripting languages. For instance: (from memory) the VBA generated by Excel when you right align a cell applied a keyword "rightAlign" (or something like that) to the cell object. But in VBScript (which I use), you have to use a number (3206 or something crazy like that) instead of "rightAlign" as it's not recognised.
  • HKosova's avatar
    HKosova
    SmartBear Alumni (Retired)
    10 years ago
    Excel also has a built-in object model reference. Press Alt+F11, then F1 and search for needed method or property. The local docs have VBA code examples - they are very similar to COM API code, but use the global Application object instead of creating it via Sys.OleObject(...).



    As for the VBA constants (thanks Colin for the heads up!) you can see their values in Excel's Object Browser. In Excel, press Alt+F11, then select View > Object Browser and search the constant name.
  • Colin_McCrae's avatar
    Colin_McCrae
    Community Hero
    10 years ago
    Heh. Beat me to it Helen!



    The link to the Excel COM reference is handy. I've never found that particular link before!
  • obaid_shirwani's avatar
    obaid_shirwani
    Contributor
    10 years ago
    Colin: I am using JScript.



    Helen: I love you for this solution.



    Thank you guys. Ill post here in case I myself find something interesting.