Saving Excel file to any location

I would like to save Excel file to specified folder using macro code which is executed from ER Studio. There is a code for creating excel object Set Excel = CreateObject("Excel.Application"). Any code/command to save this excel file to local windows folder

  • The answer is probably is something like:


    If that works, I've probably left you almost as helpless as you started though because it begs the question "how did I know that?".  All too often the examples you find for manipulating Excel define (as you apparently have) the Excel variable like this:

    Dim Excel As Object

    But if you add a reference to the Excel object library things get much easier. 
    To add a reference, right click on the macro editor background to bring up the menu dialog and in the [Edit] sub-menu you should see [References...].  Within that dialog find 'Microsoft Excel 16.0 Object Library' (or whatever version you have) and check its box.

    Now you can define your variable like this:

    Dim Excel As Excel
    Set Excel = New Excel

    After the reference is added intellisense will show Excel as valid types when you build the Dim and New commands.  All the other objects like workbooks, charts and cells can also be defined in a type-safe way and intellisense helps you build other references Too. 

    You probably need to look around within the object and you may need to refer to the Excel Object Model documentation online but your development should go much smoother.