PHP COM and Excel

**Richard** · Sep 25 '08, 07:45 PM

Re: PHP COM and Excel

"Sol" <samuel.sol@gma il.comwrote in message
news:d5336a70-c58e-472f-8346-11df31cae261@k1 3g2000hse.googl egroups.com...
I have to work with an excel file, read only on my machine. I set up
the COM class, and it is working all right, but I hit a wall now.

I need to search for a given string in the worksheet, using the Find
command in VBA, but I can't seem to make it work on php. Below you can
find a snip of the code:

$objExcel = new COM("excel.appl ication") or die("Falha ao inicializar
o excel");
$objExcel->Workbooks->Open("$arquivo ") or die("Não foi possível abrir
o arquivo");
$worksheet = $objExcel->Worksheets($i) ;
$worksheet->Cells(8, 2)->Find("What:=\" Observações\",
After:=ActiveCe ll, LookIn:=xlFormu las, LookAt:=xlPart,
SearchOrder:=xl ByRows, SearchDirection :=xlNext MatchCase:=Fals e,
SearchFormat:=F alse") or die("Not Again");

Anyone got any idea how to make it work?

=============== ==

Hi,
what exactly does "make it work" mean?
What is not working? Error messages?

Richard

**Tim Roberts** · Sep 27 '08, 05:45 AM

Re: PHP COM and Excel

Sol <samuel.sol@gma il.comwrote:

>I have to work with an excel file, read only on my machine. I set up
>the COM class, and it is working all right, but I hit a wall now.
>
>I need to search for a given string in the worksheet, using the Find
>command in VBA, but I can't seem to make it work on php. Below you can
>find a snip of the code:
>
>$objExcel = new COM("excel.appl ication") or die("Falha ao inicializar
>o excel");
>$objExcel->Workbooks->Open("$arquivo ") or die("Não foi possível abrir
>o arquivo");
>$worksheet = $objExcel->Worksheets($i) ;
>$worksheet->Cells(8, 2)->Find("What:=\" Observações\",
>After:=ActiveC ell, LookIn:=xlFormu las, LookAt:=xlPart,
>SearchOrder:=x lByRows, SearchDirection :=xlNext MatchCase:=Fals e,
>SearchFormat:= False")

You didn't really expect to pass parameters that way, did you? The :=
syntax is part of the Visual Basic language. It's not something that Excel
understands natively.

PHP doesn't support named parameters. You'll have to do this positionally.

define('xlFormu las', -4123 );
define('xlNext' , 1 );
define('xlPart' , 2 );
define('xlByRow s', 1 );
$worksheet->Cells(8,2)->Find( "Observaçõe s",
$worksheet->ActiveCell() , xlFormulas, xlPart, xlByRows );
--
Tim Roberts, timr@probo.com
Providenza & Boekelheide, Inc.

**Sol** · Sep 30 '08, 08:05 PM

Re: PHP COM and Excel

On 27 set, 02:41, Tim Roberts <t...@probo.com wrote:

Sol<samuel....@ gmail.comwrote:

I have to work with anexcelfile, read only on my machine. I set up
the COM class, and it is working all right, but I hit a wall now.

>

I need to search for a given string in the worksheet, using the Find
command in VBA, but I can't seem to make it work onphp. Below you can
find a snip of the code:

>

$objExcel = new COM("excel.appl ication") or die("Falha ao inicializar
oexcel");
$objExcel->Workbooks->Open("$arquivo ") or die("Não foi possível abrir
o arquivo");
$worksheet = $objExcel->Worksheets($i) ;
$worksheet->Cells(8, 2)->Find("What:=\" Observações\",
After:=ActiveCe ll, LookIn:=xlFormu las, LookAt:=xlPart,
SearchOrder:=xl ByRows, SearchDirection :=xlNext MatchCase:=Fals e,
SearchFormat:=F alse")

>
You didn't really expect to pass parameters that way, did you? The :=
syntax is part of the Visual Basic language. It's not something thatExcel
understands natively.
>
PHPdoesn't support named parameters. You'll have to do this positionally.
>
define('xlFormu las', -4123 );
define('xlNext' , 1 );
define('xlPart' , 2 );
define('xlByRow s', 1 );
$worksheet->Cells(8,2)->Find( "Observaçõe s",
$worksheet->ActiveCell() , xlFormulas, xlPart, xlByRows );
--
Tim Roberts, t...@probo.com
Providenza & Boekelheide, Inc.

Since I didn't know how exactly the COM extension worked, I was not
sure how it was. Thanks for the reply I will try that and get back to
you.

Just one question, why exactly this line? define('xlFormu las',
-4123 );

**Tim Roberts** · Oct 2 '08, 04:05 AM

Re: PHP COM and Excel

Sol <samuel.sol@gma il.comwrote:

>
>Since I didn't know how exactly the COM extension worked, I was not
>sure how it was. Thanks for the reply I will try that and get back to
>you.
>
>Just one question, why exactly this line?
define('xlFormu las', -4123 );

The PHP online manual would be quicker than this newsgroup. That creates a
PHP constant. After that runs, you can write:
$i = xlFormulas;
instead of this:
$i = -4123;
--
Tim Roberts, timr@probo.com
Providenza & Boekelheide, Inc.

**Sol** · Oct 2 '08, 05:35 PM

Re: PHP COM and Excel

On Oct 2, 1:04 am, Tim Roberts <t...@probo.com wrote:

Sol <samuel....@gma il.comwrote:
>

Since I didn't know how exactly the COM extension worked, I was not
sure how it was. Thanks for the reply I will try that and get back to
you.

>

Just one question, why exactly this line?
define('xlFormu las', -4123 );

>
The PHP online manual would be quicker than this newsgroup. That creates a
PHP constant. After that runs, you can write:
$i = xlFormulas;
instead of this:
$i = -4123;
--
Tim Roberts, t...@probo.com
Providenza & Boekelheide, Inc.

I know what defines does, my question was why define xlFormulas as
-4123 that's what I didn't get.

**Tim Roberts** · Oct 5 '08, 02:55 AM

Re: PHP COM and Excel

Sol <samuel.sol@gma il.comwrote:

>>

>Just one question, why exactly this line?
define('xlFormu las', -4123 );

>>
>The PHP online manual would be quicker than this newsgroup. That creates a
>PHP constant. After that runs, you can write:
> $i = xlFormulas;
>instead of this:
> $i = -4123;

>
>I know what defines does, my question was why define xlFormulas as
>-4123 that's what I didn't get.

Ah, my apologies. That happens to be the value of the "xlFormulas "
constant from Excel. You can find these values using Google, although I
got them using Python, just because I'm more comfortable there:

Python 2.4.4 (#71, Oct 18 2006, 08:34:43) [MSC v.1310 32 bit (Intel)] on
win32
Type "help", "copyright" , "credits" or "license" for more information.

>>import win32com.client
>>xl = win32com.client .Dispatch('Exce l.Application')
>>win32com.clie nt.constants.xl Formula

-4123

>>win32com.clie nt.constants.xl Part

2

>>>

--
Tim Roberts, timr@probo.com
Providenza & Boekelheide, Inc.

**Jim Carlock** · Oct 6 '08, 08:35 PM

Re: PHP COM and Excel

xlFormulas, xlValues, or xlComments

Those constants are used to tell what you want to find. Each cell
can have a formula, a value and a comment. So to distinguish the
different types of information to search through, you have those
constants.

For instance, if you'd like to search for something inside a cell,
perhaps the output of a formula, rather than the formula itself,
you'd use the xlValues constant.

If you'd like to search through the formulas and find a particular
variable, you'd use the xlFormulas constant to identify where to
search.

And because each cell can have a comment, you can search through
comments, rather than the values or the formulas.

Hope this helps and good luck. It seems limited to perhaps the MS
Excel Find function.

Find Method

http://msdn.microsoft.com/en-us/library/aa298218(office.10).aspx

--
Jim Carlock
You Have More Than Five Senses

http://www.associatedcontent.com/article/381163/more_than_five_senses.html