[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

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

0
kbach
Asked:
kbach
  • 3
  • 3
1 Solution
 
virmaiorCommented:
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);
0
 
kbachAuthor Commented:
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.
0
 
virmaiorCommented:
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
kbachAuthor Commented:
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....
0
 
virmaiorCommented:
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

maybe:

$wkb->Close(2);
$excel->Quit();
0
 
kbachAuthor Commented:
$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!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now