Asp Excel integration: open/close xls files on server – read/write cell

This is a particular scenario: I have to use an excel worksheet as calculation engine on a web page. MS itself discourages the asp-excel (application) integration but this is the easiest method I found. Here are my test’s steps and the code for the test, in asp:

  • Create an Excel Object and open a worksheet of an xls file;
  • 1
    2
    3
    4
    5
    
    Set ex = server.CreateObject("Excel.Application")
    If IsObject(ex) Then
    ex.Application.DisplayAlerts = False
    ex.Application.Visible= True
    ex.Workbooks.Open("C:\Inetpub\wwwroot\testExcel\Test.xls")
  • Write a pair of values on two cells (I prepared a third cell with the SUM formula for these two cells, to test the formulas execution);
  • 6
    7
    
    ex.ActiveSheet.Cells(1,1).Value = "4"
    ex.ActiveSheet.Cells(1,2).Value = "3"
  • Save (that means execution of the formulas, the file Test1.xls is only for cache, the real file saved is the same Test.xls) and close the file and Excel Application;
  • 8
    9
    10
    11
    12
    13
    
    ex.Save("C:\Inetpub\wwwroot\testExcel\Temp1.xls")
    ex.Workbooks.Close
    ex.Quit
    Else
    Response.write("Can Not Connect to Excel.Application")
    End IF
  • Reopen the xls file, as first point;
  • Read the cell of the formula result;
  • 1
    
    Response.write(ex.ActiveSheet.Cells(4,4).Value)
  • close the file and Excel Application, as before;

The great limitation of the solution is that Excel must be installed on the web server.

Share this post:
  • Print
  • Digg
  • StumbleUpon
  • del.icio.us
  • Facebook
  • Twitter
  • Google Buzz
  • LinkedIn
  • Tumblr
Leave the first comment

Android 2.2 Froyo on Motorola Milestone – how to (not official version)

(Obiouvsly) I am a Motorola Milestone owner (I friendly hate touchscreen keyboards) and during this summer holidays I spent a little time to find how to have my smartphone updated to Android 2.2, aka Froyo. Motorola haven’t released an official update yet but I can’t wait :) …here is a solution (follow these steps at your own risk!) [UPDATED 14-10-2010]:

  • wipe your phone/factory reset (always have a backup…);
  • Download OpenRecovery from here (latest version is 1.04a); UPDATE: download new open recovery here: androidiani-openrecovery, copy the contents of the zip into the root of the sd card;
  • Download the custom 2.2 Rom, MotoFrenzy, from here (by now is the  version 0.52 and the only issue is a focus disfunction of the camera), CyanogenMod 6 for Milestone from here (here it is the official thread on modmymobile, here the official page from the developer nadlabak), and copy the zip into: sd card/openrecovery/updates/
  • Start OpenRecovery: reboot in recovery mode (shutdown the phone, press camera button + stanby button, when a triangle appear press volume up + camera button), now select “apply update.zip” and you are in OpenRecovery menu;
  • Install the rom from OpenRecovery menu->nandroid->restore image->motofrenzy (or the name of the directory you copied the rom files of point 3); use OpenRecovery Apply Update menu to install the custom ROM;
  • Done! …Enjoy flash (download the flash player from the market), app to sd, jit, etc! Now with working camera and better battery life!
Share this post:
  • Print
  • Digg
  • StumbleUpon
  • del.icio.us
  • Facebook
  • Twitter
  • Google Buzz
  • LinkedIn
  • Tumblr
Leave the first comment

Sap Inbound idoc folder rfc processing script / Batch (on Windows server)

This time the problem is: I had to periodically read an ftp folder for inbound idoc files to process with Sap; the simpliest way to process an inbound idoc file is with a call of the program startrfc.exe, that you can find on every Sap installation executables folder, passing the following parameters:

startrfc -3 -d <sys_id> -u <sap_rfc_user> -p <password>
-c <mandt> -l <login_language, E for English> -h <sap_host_name>
-s <system_number> -F EDI_DATA_INCOMING
-E PORT=<edi_port> -E PATHNAME=<file_path>

But this call process only a single file, it doesn’t admit “<drive>/<file_directory>/*.*” like strings as PATHNAME.

I solved the problem using the dos command “FORFILES” (scheduled every n minutes); here is the batch script:

FORFILES -p <folder_path> -m * -c "startrfc -3 -d <sys_id>
-u <sap_rfc_user> -p <password> -c <mandt> -l <login_language>
-h <sap_host_name> -s <system_number> -F EDI_DATA_INCOMING
-E PORT=<edi_port> -E PATHNAME=\\<host>\<folder>\...\\@FILE"

In my specific scenario there was some other points to take care of:

  • I had to read from a network smb shared folder (of an ftp server) but the <folder_path> is not admitted as “\\<host>\<path>” so I had to map a network drive;
  • at the opposite front the PATHNAME of the EDI_DATA_INCOMING function call doesn’t admit mapped network drive so I built the files path this way: \\<host>\<folder>\…\\@FILE , instead of simply using the @PATH of the FORFILES command; It’s mandatory to use a double back-slash \\ before the @FILE parameter to get the script working.
  • the -m * parameter is used as mask for file names, I used “*” to process all files, even without extensions.


As ever I hope this could help someone out there! :)

Share this post:
  • Print
  • Digg
  • StumbleUpon
  • del.icio.us
  • Facebook
  • Twitter
  • Google Buzz
  • LinkedIn
  • Tumblr
One comment so far, add another

Bypass Filemaker iwp login via url

Here it comes the problem: I had to access a Filemaker Database shared via Instant Web Publish from a web app (a private area), without passing through the filemaker login (very ugly and another uname/password to digit/remember) but preserving security -> no guest account on DB.

It is possible to build an url to directly access the shared database, this way:

http://<filemaker_server_ip:port>/fmi/iwp/cgi?dbpath=
%2Ffmi%2Fiwp%2Fcgi%3F-db%3D<database_file_name>%26
-startsession
&amp;acct=account&amp;name=<username>
&amp;password=<password>&amp;login=Login&amp;-authdb

  • parameters to change are between <>
  • the middle part is the URL encoding of: “/fmi/iwp/cgi?db=<database_file_name>&-startsession”, for the table of URL encoding see here.

That’s all!

Share this post:
  • Print
  • Digg
  • StumbleUpon
  • del.icio.us
  • Facebook
  • Twitter
  • Google Buzz
  • LinkedIn
  • Tumblr
Leave the first comment

Sap DMS – direct print of pdf drawings on Windows frontend

My problem was to print a pdf from SAP document management system automatically, without user actions needed;
I solved this using the classic function cl_gui_frontend_services=>execute giving as parameter not only the path of the file to open, but the concatenation of the specific Adobe Reader call parameters to print and hide the main window (simply: /p /h ).
Here it is the entire code of my function:

FUNCTION z_print_pdf_draw.
*”  IMPORTING
*”     VALUE(PDOKAR) TYPE  DRAW-DOKAR
*”     VALUE(PDOKNR) TYPE  DRAW-DOKNR
*”     VALUE(PDOKVR) TYPE  DRAW-DOKVR
*Direct Print of pdf drawings, Windows Frontend only
TABLES: draw.
SELECT SINGLE * FROM draw INTO draw
WHERE  dokar = pdokar AND
doknr = pdoknr AND
dokvr = pdokvr.
IF sy-subrc = 0.
DATA: sourcef TYPE string,
targetf TYPE string,
printcommand TYPE string,
dms_file TYPE dms_doc_file.
CONCATENATE ‘C:\Temp\’ draw-mrk_filep INTO targetf.
CONCATENATE ‘/p /h ‘ targetf INTO printcommand.
“P = Print, h = Hide Window
dms_file-fileno = 1.
MOVE targetf TO dms_file-filename.
dms_file-dappl = ‘PDF’.
dms_file-dttrg = draw-dttrg.
*save the pdf file locally
CALL FUNCTION ‘CV120_DOC_CHECKOUT_TO_CLIENT’
EXPORTING
ps_draw           = draw
ps_doc_file       = dms_file
.
IF sy-subrc = 0.
* print the pdf!
CALL METHOD cl_gui_frontend_services=>execute
EXPORTING
application = ‘AcroRd32.exe’
*application = ‘Acrobat.exe’
parameter = printcommand
minimized = ‘X’
operation = ”
EXCEPTIONS
*…
OTHERS = 10 .
ENDIF.
ELSE.
* file not found…
ENDIF.
ENDFUNCTION.
Share this post:
  • Print
  • Digg
  • StumbleUpon
  • del.icio.us
  • Facebook
  • Twitter
  • Google Buzz
  • LinkedIn
  • Tumblr
Leave the first comment

WordPress on secondary server of double system hosting (hosting Aruba win+linux)

Very very particular problem I had with the configuration of my company’s web site. The initial conditions are:

  • hosting with double os / double server on the same domain (in my case hosting windows + linux with Aruba, a popular Italian hosting company)
  • need to install wordpress on the secondary server (on linux, off course)

My hosting company give two subdomain ( win.domain.com and lnx.domain.com) to access your two machines, but in my case I have to use the win domain as default, as www.domain.com. When I installed wp on the lnx server I had all the links inconsistents due to those pre-conditions.

The solution is to simply modifiy a pair of entry in the Database:

  • change www to your linux subdomain in the table wp_options at the parameters “home” and “siteurl”
  • don’t touch the parameter “upload_path”

Have a nice day!

Share this post:
  • Print
  • Digg
  • StumbleUpon
  • del.icio.us
  • Facebook
  • Twitter
  • Google Buzz
  • LinkedIn
  • Tumblr
2 comments so far, add yours

Send email with Attachment from a SAP Unicode system – with func SO_DOCUMENT_SEND_API1

What a looong title :P

The Problem is that reusing code for sending email with attachment from a sap non-unicode to a sap unicode system the att file (in my case was a .pdf) results unreadable, using the function SO_NEW_DOCUMENT_ATT_SEND_API1 or SO_DOCUMENT_SEND_API1 .

A typical call of the function is:

CALL FUNCTION ‘SO_DOCUMENT_SEND_API1′
EXPORTING
DOCUMENT_DATA                   = DOCUMENT_DATA
PUT_IN_OUTBOX                      = PUT_IN_OUTBOX
SENDER_ADDRESS                   = INVOICE_SENDER
SENDER_ADDRESS_TYPE      = ADDR_TYPE
IMPORTING
SENT_TO_ALL             = SENT_TO_ALL
NEW_OBJECT_ID      = NEW_OBJECT_ID
SENDER_ID                  = SENDER_ID
TABLES
PACKING_LIST               = PACKING_LIST
OBJECT_HEADER         = OBJECT_HEADER
CONTENTS_BIN             = CONTENTS_BIN
CONTENTS_TXT             = CONTENTS_TXT
OBJECT_PARA                = OBJECT_PARA
OBJECT_PARB                = OBJECT_PARB
RECEIVERS                       = RECEIVERS
EXCEPTIONS…

And the PACKING_LIST-DOC_SIZE is usually valorized as:

PACKING_LIST-DOC_SIZE   = TAB_LINES * 255.

But the right code that solve the Problem is:

PACKING_LIST-DOC_SIZE   = TAB_LINES * 255 * 2.

Due to the change data representation. Helpful docs on Sap and unicode here.

Please feel free to comment :)

Share this post:
  • Print
  • Digg
  • StumbleUpon
  • del.icio.us
  • Facebook
  • Twitter
  • Google Buzz
  • LinkedIn
  • Tumblr
Leave the first comment

WPML and Category Order Problem

My first tip is about the use of WordPress with the great multilanguage plugin WPML.org …and the problem is: how to order the categories list in every language?
Wordpress needs a plugin to order the categories following your (or your clients’) whishes, and I use the well done Category Order, but it doesn’t work with WPML, unless you try to force the plugin to eat your manipulated querystrings. Here an example for two languages.

  • Reach the Category Order menu under Articles
  • Order the Categories (for the main language) and copy the url you have after the ordering
  • reopen the Category Order menu adding “&lang=en”  (for the english i.e.) to the url to have the capability to order the Categories in another language, order…and this will mess up the order in other languages :P Nevermind, copy the url you have after ordering
  • concatenate the list of Categories IDs of the two url you copy in point 2 and 3 in one of the two link and use it! Now both Categories (or more than two if you iterate) follows your will.
  • Often subcategories makes problem with this method, but you only have to open for editing one subcategory (use querystring id if they also disappear in the wp list of Cat) and simply save without make any changes.

Hope this will be helpful!

PS: soon I will contact the author of Category Order plugin to adapt it for the use with WPML!

Update: I only tried this workaround on wp 2.9.2, not yet on the new 3.0

Update 2: Thanx to d2f that confirms this workaround fits even WP 3.0!

Share this post:
  • Print
  • Digg
  • StumbleUpon
  • del.icio.us
  • Facebook
  • Twitter
  • Google Buzz
  • LinkedIn
  • Tumblr
10 comments so far, add yours