asked on

PHP COM and Excel problem

Hello,

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
$wkb->Close("False");
$excel ->Quit();

Thanks for you help

virmaior

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

$wkb->saveAs($newFile,165);

kbach

ASKER

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 formats...ie xlTextMSDOS.?..I can only see the constants...

Thanks for your help,
Scott.

virmaior

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.

kbach

ASKER

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....

ASKER CERTIFIED SOLUTION

virmaior

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kbach

ASKER

$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!!