Excel Automation and Services - Revision history

Ivan.belenski at 18:24, 8 December 2017

2017-12-08T18:24:15Z

Ivan.belenski: /* Using Excel in the application */

2017-12-08T18:18:43Z

‎Using Excel in the application

Ivan.belenski: /* Configuring the machine */

2017-12-08T17:59:03Z

‎Configuring the machine

Ivan.belenski: /* Configuring the machine */

2017-12-08T17:54:29Z

‎Configuring the machine

Ivan.belenski at 17:06, 8 December 2017

2017-12-08T17:06:29Z

Ivan.belenski: Created page with "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 bu..."

2017-12-08T17:03:12Z

Created page with "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 bu..."

New page

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.

← Older revision		Revision as of 18:24, 8 December 2017
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 ==

← Older revision		Revision as of 18:18, 8 December 2017
Line 38:		Line 38:
	== 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.

@@ Line 9: / Line 9: @@
 === Grant DCOM permissions to the user account ===
-The user account the service or scheduled task wil lrun as needs to have '''Local Launch''' and '''Local Activation''' permissions for the '''Microsoft Excel Application''' DCOM application.
+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.
@@ Line 23: / Line 23: @@
 === Configure the systemprofile directory ===
-On a 32 bit system, the path of the '''systemprofile''' directory is <code>C:\Windows\System32\config\systemprofile\</code>
+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>
@@ Line 29: / Line 29: @@
 (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 '''systemprofile''' directory.
+# Navigate to the <code>systemprofile</code> directory.
-# Make sure it contains a '''Desktop''' sub-directory; create it, if it doesn't exist.
+# Make sure it contains a <code>Desktop</code> sub-directory; create it, if it doesn't exist.
-# Grant the specified permissions to the following sub-directories of the '''systemprofile''' directory:
+# 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;

@@ Line 3: / Line 3: @@
 == Configuring the machine ==
-TODO
+=== Determine the user account the service or scheduled task will run as ===
 == Using Excel in the application ==
 TODO

← Older revision		Revision as of 17:06, 8 December 2017
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 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.
		+
		+	== Configuring the machine ==
		+
		+	TODO
		+
		+	== Using Excel in the application ==
		+
		+	TODO