You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » COM Scripting » Server side Excel automation replacement
Register User       Forum List       Calendar       FAQ
Page 1 of 1 1
Topic Options
#212482 - 2017-05-05 10:01 AM Server side Excel automation replacement
green78 Offline
Fresh Scripter

Registered: 2007-05-02
Posts: 34
Hi folks,

I have a KIX script running on a server using some automation in Excel. What I've read recently is that server-side automation is not a good idea for MS Office products (see the below article):

https://support.microsoft.com/en-us/help/257757/considerations-for-server-side-automation-of-office

One of the suggestions is to use the System.IO.Package.IO namespace in .NET Framework.

Could anyone please help on how to replace the below code with the above suggestion without initializing Excel application itself?

 Code:
 $Excel = CreateObject("Excel.Application")
 $WorkBook = $Excel.WorkBooks.Open("\\server\folder\EUR.xls")
 $WorkSheet = $WorkBook.Sheets.Item("EUR")
    $xlsValueEUR01 = $WorkSheet.Range("E3").Value
 $Excel.quit

Top
#212483 - 2017-05-05 11:18 AM Re: Server side Excel automation replacement [Re: green78]
green78 Offline
Fresh Scripter

Registered: 2007-05-02
Posts: 34
After reading a bit more I realized that this won't work for binary (XLS) files but only for Open XML (.xlsx) files. Wonder if it is at all possible to read data from XLS files without Excel application itself. I've seen some ADODB examples but can't figure out if I can get values in of single says with this method..
Top
#212486 - 2017-05-05 03:43 PM Re: Server side Excel automation replacement [Re: green78]
Arend_ Moderator Offline
MM club member
*****

Registered: 2005-01-17
Posts: 1895
Loc: Hilversum, The Netherlands
this should help you on your way:
 Code:
$adOpenStatic = 3
$adLockOptimistic = 3
$adCmdText = &0001

$objConnection = CreateObject("ADODB.Connection")
$objRecordSet = CreateObject("ADODB.Recordset")

$objConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Scripts\Test.xls;Extended Properties='Excel 8.0';HDR=Yes;;")

$objRecordset.Open("Select * FROM [Sheet1$]", $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText)

While NOT $objRecordset.EOF
  ? $objRecordset.Fields.Item("Name"), $objRecordset.Fields.Item("Number")
  $objRecordset.MoveNext
Loop

Top
#212532 - 2017-06-01 03:06 PM Re: Server side Excel automation replacement [Re: Arend_]
green78 Offline
Fresh Scripter

Registered: 2007-05-02
Posts: 34
Thanks Arend_ but I can't make it work for some reason. Has anyone tried it? What I did was the following:

Create an XLS file named: C:\Scripts\Test.xls with "Sheet1" and the following values in cells in "Sheet1" A1 to B6 (in cells "A1" and "B1" respectively are the headers "Name" and "Number"

Name Number
John 1
Joe 2
Jane 3
Doe 2
Jack 1

Running the script that Arend_ provided I expected to see the output as displayed above but it displayed nothing...
Note sure if I'm doing something wrong or is there a glitch in the syntax?

Thanks!
Top
#212533 - 2017-06-01 03:37 PM Re: Server side Excel automation replacement [Re: green78]
green78 Offline
Fresh Scripter

Registered: 2007-05-02
Posts: 34
Ok, as usually it happens - the moment I posted the previous message I made it work. Had to change the code (changed "Jet" to "ACE" and moved the single quote after HDR=Yes' ). Something like this for my purpose would fit perfect - extract all cell values which are negative in a given worksheet in a given range:

 Code:
$adOpenStatic = 3
$adLockOptimistic = 3
$adCmdText = &0001

$objConnection = CreateObject("ADODB.Connection")
$objRecordSet = CreateObject("ADODB.Recordset")

$objConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\Merger.xls;Extended Properties='Excel 12.0;HDR=Yes'")

$objRecordset.Open("Select Number FROM [Sheet1$A1:B20] WHERE Number < 0", $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText)

While NOT $objRecordset.EOF
  ? $objRecordset.Fields.Item("Number").Value
  $objRecordset.MoveNext
Loop

Top
#212536 - 2017-06-02 12:06 PM Re: Server side Excel automation replacement [Re: green78]
green78 Offline
Fresh Scripter

Registered: 2007-05-02
Posts: 34
Ok, to finalize the topic completely: When I uninstalled Office from the machine (because the idea is not to have MS Office installed on a server) my code above stopped working. So I dug a bit more and it turns out the original code that Arend_ provided works just with a small tweak - it looks like HDR is not supported for Office 97-2003 files, so I just removed it and the proper code to read such files from KIX should look like this:

 Code:
$adOpenStatic = 3
$adLockOptimistic = 3
$adCmdText = &0001

$objConnection = CreateObject("ADODB.Connection")
$objRecordSet = CreateObject("ADODB.Recordset")

$objConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Scripts\Test.xls;Extended Properties=Excel 8.0;")

$objRecordset.Open("Select * FROM [Sheet1$]", $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText)

While NOT $objRecordset.EOF
  ? $objRecordset.Fields.Item("Name")
  ? $objRecordset.Fields.Item("Number")
  $objRecordset.MoveNext
Loop


Edited by green78 (2017-06-02 12:09 PM)
Top
Page 1 of 1 1


Moderator:  Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 370 anonymous users online.
details
Newest Members
Timothy, Jojo67, MaikSimon, kvn317, kixtarts2025
17874 Registered Users
My Cookies · Mark all read Powered by UBB.threads™ Contact Us · KiXtart.org website · Top

Generated in 0.056 seconds in which 0.023 seconds were spent on a total of 13 queries. Zlib compression enabled.
Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org