Difference between revisions of "Excel Automation and Services"

From Dot2DotCommunications
Jump to: navigation, search
 
(3 intermediate revisions by one user not shown)
Line 1: Line 1:
−
Excel exposes an Automation (COM) API that allows other applications to use Excel to create spreadsheets. This works fairly well if those other applications are interactive but some issues present themselves if they are services or command line utilities run by the Windows task scheduler. Those programs don't run in the context of an interactive user log in and Excel misbehaves if they try use it through Automation. Fortunately, it's possible to make this work by making a few configuration tweaks on the machine where the applications will run and by following a few rules when writing those applications.
+
Excel exposes an Automation (COM) API that allows other applications to use Excel to create spreadsheets. This works fairly well if those other applications are interactive but some issues present themselves if they are services or command line utilities run by the Windows task scheduler. Those programs don't run in the context of an interactive user log in and Excel misbehaves if they try to use it through Automation. Fortunately, it's possible to make this work by performing a few configuration tweaks on the machine where the applications will run and by following a few rules when writing those applications.
  
 
== Configuring the machine ==
 
== Configuring the machine ==
  
−
TODO
+
=== Determine the user account the service or scheduled task will run as ===
 +
 
 +
It can be a local or an Active Directory account and may or may not be a member of the machine's Administrators group.
 +
 
 +
=== Grant DCOM permissions to the user account ===
 +
 
 +
The user account the service or scheduled task will run as needs to have '''Local Launch''' and '''Local Activation''' permissions for the '''Microsoft Excel Application''' DCOM application.
 +
 
 +
If the user account is a member of the machine's Administrators group, this step may be unnecessary, but it won't hurt to verify that the permissions are in place.
 +
 
 +
# Open '''Adminstrative Tools''' -> '''Component Services''' -> '''Computers''' -> '''My Computer''' -> '''DCOM Config'''.
 +
# Find '''Microsoft Excel Application''', right-click on it, and select '''Properties'''.
 +
# Go to the '''Security''' tab.
 +
# Change '''Launch and Activation Permissions''' to '''Customize''' and click '''Edit'''.
 +
# Add the user account and check '''Local Launch''' and '''Local Activation'''.
 +
# Click '''OK'''.
 +
# Click '''OK'''.
 +
 
 +
=== Configure the systemprofile directory ===
 +
 
 +
On a 32 bit system, the path of the <code>systemprofile</code> directory is <code>C:\Windows\System32\config\systemprofile\</code>
 +
 
 +
On a 64 bit system, the path of the systemprofile directory is <code>C:\Windows\SysWOW64\config\systemprofile\</code>
 +
 
 +
(If you're dealing with a 64 bit system, it might be a good idea to perform the steps below on both the System32 and SysWOW64 sub-directories.)
 +
 
 +
# Navigate to the <code>systemprofile</code> directory.
 +
# Make sure it contains a <code>Desktop</code> sub-directory; create it, if it doesn't exist.
 +
# Make sure the user account the service or scheduled task will run as has the specified access permissions to the following sub-directories of the <code>systemprofile</code> directory:
 +
## <code>AppData\Local\Microsoft</code>: Modify, Read & execute, List folder contents, Read, Write;
 +
## <code>AppData\Roaming\Microsoft</code>: Modify, Read & execute, List folder contents, Read, Write;
 +
## <code>Desktop</code>: Read & execute, List folder contents, Read;
  
 
== Using Excel in the application ==
 
== Using Excel in the application ==
  
−
TODO
+
Attempting to create a new <code>Excel.Workbook</code> object or calling the <code>Excel.Application.Worbooks.Add()</code> method will fail. Instead, include a blank workbook file in the applications deployment package and use it as a template for any new workbooks you need to create:
 +
 
 +
* open the blank workbook using the <code>Excel.Application.Workbooks.Open()</code> method;
 +
* immediately save it as a new file using the <code>Excel.Workbook.SaveAs()</code> method;
 +
* after that you can periodically save the workbook using the <code>Excel.Workbook.Save()</code> method;
 +
* remember to save and close the workbook when you're done with it using the <code>Excel.Workbook.Close()</code> method with a <code>True</code> argument.
 +
 
 +
For example, in VB6:
 +
 
 +
  Dim oExcel As Object
 +
  Set oExcel = VBA.CreateObject("Excel.Application")
 +
  Dim oWorkbook As Object
 +
  Set oWorkbook = oExcel.Workbooks.Open(VB.App.Path & "\Blank.xlsx", , True)
 +
  With oWorkbook
 +
      .SaveAs VB.App.Path & "\Result.xlsx"
 +
      .Sheets(1).Cells(1, 1) = "Hello!"
 +
      .Save
 +
      .Sheets(1).Cells(2, 1) = "Good bye!"
 +
      .Close True
 +
  End With
 +
 
 +
The <code>Excel.Workbook.SaveAs()</code> method may fail if called with a network path, whether a mapped drive or a UNC path. If your application needs to be able to create workbooks in network directories:
 +
 
 +
* create the workbook in a local directory;
 +
* save and close it;
 +
* use the file system to move the workbook file to the target directory.

Latest revision as of 13:24, 8 December 2017

Excel exposes an Automation (COM) API that allows other applications to use Excel to create spreadsheets. This works fairly well if those other applications are interactive but some issues present themselves if they are services or command line utilities run by the Windows task scheduler. Those programs don't run in the context of an interactive user log in and Excel misbehaves if they try to use it through Automation. Fortunately, it's possible to make this work by performing a few configuration tweaks on the machine where the applications will run and by following a few rules when writing those applications.

Contents

Configuring the machine

Determine the user account the service or scheduled task will run as

It can be a local or an Active Directory account and may or may not be a member of the machine's Administrators group.

Grant DCOM permissions to the user account

The user account the service or scheduled task will run as needs to have Local Launch and Local Activation permissions for the Microsoft Excel Application DCOM application.

If the user account is a member of the machine's Administrators group, this step may be unnecessary, but it won't hurt to verify that the permissions are in place.

  1. Open Adminstrative Tools -> Component Services -> Computers -> My Computer -> DCOM Config.
  2. Find Microsoft Excel Application, right-click on it, and select Properties.
  3. Go to the Security tab.
  4. Change Launch and Activation Permissions to Customize and click Edit.
  5. Add the user account and check Local Launch and Local Activation.
  6. Click OK.
  7. Click OK.

Configure the systemprofile directory

On a 32 bit system, the path of the systemprofile directory is C:\Windows\System32\config\systemprofile\

On a 64 bit system, the path of the systemprofile directory is C:\Windows\SysWOW64\config\systemprofile\

(If you're dealing with a 64 bit system, it might be a good idea to perform the steps below on both the System32 and SysWOW64 sub-directories.)

  1. Navigate to the systemprofile directory.
  2. Make sure it contains a Desktop sub-directory; create it, if it doesn't exist.
  3. Make sure the user account the service or scheduled task will run as has the specified access permissions to the following sub-directories of the systemprofile directory:
    1. AppData\Local\Microsoft: Modify, Read & execute, List folder contents, Read, Write;
    2. AppData\Roaming\Microsoft: Modify, Read & execute, List folder contents, Read, Write;
    3. Desktop: Read & execute, List folder contents, Read;

Using Excel in the application

Attempting to create a new Excel.Workbook object or calling the Excel.Application.Worbooks.Add() method will fail. Instead, include a blank workbook file in the applications deployment package and use it as a template for any new workbooks you need to create:

  • open the blank workbook using the Excel.Application.Workbooks.Open() method;
  • immediately save it as a new file using the Excel.Workbook.SaveAs() method;
  • after that you can periodically save the workbook using the Excel.Workbook.Save() method;
  • remember to save and close the workbook when you're done with it using the Excel.Workbook.Close() method with a True argument.

For example, in VB6: 

 Dim oExcel As Object
 Set oExcel = VBA.CreateObject("Excel.Application")
 Dim oWorkbook As Object
 Set oWorkbook = oExcel.Workbooks.Open(VB.App.Path & "\Blank.xlsx", , True)
 With oWorkbook
     .SaveAs VB.App.Path & "\Result.xlsx"
     .Sheets(1).Cells(1, 1) = "Hello!"
     .Save
     .Sheets(1).Cells(2, 1) = "Good bye!"
     .Close True
 End With

The Excel.Workbook.SaveAs() method may fail if called with a network path, whether a mapped drive or a UNC path. If your application needs to be able to create workbooks in network directories:

  • create the workbook in a local directory;
  • save and close it;
  • use the file system to move the workbook file to the target directory.
Retrieved from "http://wiki.d2dcm.com/index.php?title=Excel_Automation_and_Services&oldid=3051"