PHP COM and Excel problem

Posted on 2005-04-27
Last Modified: 2011-04-14

I'm using PHP 5.0.3 and Windows 2003 Server.

I'm opening an excel spreadsheet through PHP using COM, and then saving it as a tab delimited text file.
The problem is, COM always saves the file as read only, and I need write access to the file after.

i'm passing in parameter - 4158 as it's the only parameter that converts the spreadsheet to tab delimited, but, I need a write version of that save option

here's the code:

$excel = new COM("Excel.application") or die ("excelreader: constructor");

$workbook = $excel ->application->Workbooks->Open($path);

$newFile = substr($path, 0, -3) .'txt'; //strip extension and add a text one

$wkb->SaveAs($newFile, -4158); //I don't know that -4158 is the right parameter, it may be the problem (ie, read only), but it's the only one I could find that would save the file as a tab delimited text file

//these calls don't work, the system hangs when it tries to close the workbook or quit the excel app
$excel ->Quit();

Thanks for you help

Question by:kbach
    LVL 20

    Expert Comment

    Ref: if you go into MS Excel, hit ALT-F11 to bring up the VBA.  Then go to the View Menu -> Object Browser
    lookup xlWorkbook, the SaveAs Method:

    Sub SaveAs([Filename], [FileFormat], [Password], [WriteResPassword], [ReadOnlyRecommended], [CreateBackup], [AccessMode As XlSaveAsAccessMode = xlNoChange], [ConflictResolution], [AddToMru], [TextCodepage], [TextVisualLayout], [Local])

    then look at the values for xlFileFormat..

    xlCurrentPlatformText = -4158

    try using
    xlTextMSDOS   instead (165) as it also looks to be Tab Delimited

    LVL 1

    Author Comment

    When I try the 165 parameter, COM throws an Exception saying it can't get the SaveAs parameter...

    I've tried playing around with a few other parameters (44 = HTML) and those file types save fine writable, allowing me to delete them later and free up the resources, so I'm sure it must be a problem with the file type I'm using.

    Another piece of info, is that I can't close the workbook object with file type (-4158) using workbook->Close("False");  The script times out on this line of code, and doesn't release the resource.  That's why I can't delete the file, as I'm getting a sharing violation even after I close all browser windows.

    When I save the file type as 44, or a few different file types, I can call workbook->Close("False") no problem, and I have write access to the file immediately.

    Incidentally, in the Object Browser of Excel, how can I see the long values of the file xlTextMSDOS.?..I can only see the constants...

    Thanks for your help,
    LVL 20

    Expert Comment

    in office 2000, the value is at the bottom beneath the scrolling area.
    e.g. const xlDBF4 =  11

    whenever I was working in COM code, I always tried it first as VBA and then translated the format
    e.g. Const xlTextMSDOS = 21 (&H15)

    which is ever so funny because I just now realized that i converted my hexadecimal wrong... 0x15 = 21... not 165.

    hope this helps.
    LVL 1

    Author Comment

    Ya, I tried 21, that works as far as saving the file in tab delimited format.  I still have the same problem in that it won't allow me to close the workbook, and I can't delete the file due to a sharing violation, even after the browser window has been closed and I've restarted IIS !  hmm....
    LVL 20

    Accepted Solution

    It's been a long time since I worked in VBA, but if I remember correctly,
    you should start every section of code by turning off the display of errors and user interaction...
    I just don't remember the 2 commands to use.  (turning these off will let you close excel and not end up with orphaned processes)

    why are you passing a string to the close function?

    Sub Close([SaveChanges], [Filename], [RouteWorkbook])

    Const xlDoNotSaveChanges = 2


    LVL 1

    Author Comment

    $wkb->Close(2) works like a charm!

    Now I can use -4158 as planned, and there are no orphaned processes, which is a major load-off...thanks a lot man!!

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Suggested Solutions

    A colleague recently asked me about how to give his client a small part of the web site that could be completely under the client's control.  Since I have done this sort of thing before to add emergency banners to a web site, I decided I would creat…
    These days socially coordinated efforts have turned into a critical requirement for enterprises.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now