Starting Excel (Macro) with PHP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
asyouwish
Forum Newbie
Posts: 6
Joined: Tue Nov 30, 2010 6:06 am

Starting Excel (Macro) with PHP

Post by asyouwish »

Hello,

Is it possible to start an excel via PHP? Even better if I could target a specific macro within an excel.

Basically what I want to do is to read data from a database and generate an excel report. If I access the excel directly via PHP or PHP -> .bat file or some other way doesn't matter as long as I can start the excel/macro.

I'd prefere not using any of the php excel classes available.

Any and all ideas are appreciated.

Br,
Isak
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Starting Excel (Macro) with PHP

Post by requinix »

Is PHP running on Windows?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Starting Excel (Macro) with PHP

Post by califdon »

If I understand what you're asking, all you need to do is send the appropriate header, followed by the data using commas and linefeeds as separators. The browser will open Excel or whatever application is the default spreadsheet application associated with the extension .xls on that computer, thus it would work on a Windows or Mac or Linux machine.

See: http://www.the-art-of-web.com/php/dataexport/
asyouwish
Forum Newbie
Posts: 6
Joined: Tue Nov 30, 2010 6:06 am

Re: Starting Excel (Macro) with PHP

Post by asyouwish »

tasairis wrote:Is PHP running on Windows?
Yes, it's running on windows 7, wampserver 2.0
asyouwish
Forum Newbie
Posts: 6
Joined: Tue Nov 30, 2010 6:06 am

Re: Starting Excel (Macro) with PHP

Post by asyouwish »

califdon wrote:If I understand what you're asking, all you need to do is send the appropriate header, followed by the data using commas and linefeeds as separators. The browser will open Excel or whatever application is the default spreadsheet application associated with the extension .xls on that computer, thus it would work on a Windows or Mac or Linux machine.

See: http://www.the-art-of-web.com/php/dataexport/

Thank you but not quite, if I understand what you're aiming for. I would like to open a specific excel, or run a script (VBA or VBS, I know very little about these) that creates an excel on the server that the user can click on to download. The script will have to create graphs.

Br,
Isak
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Starting Excel (Macro) with PHP

Post by califdon »

So you're not trying to create a file to be sent to a browser? PHP is essentially a web technology. If you are not creating something that is requested by a browser, you may be using the wrong language; although PHP is very flexible and can read and write local files, I wouldn't recommend it for something not associated with a web browser, especially not manipulation of a Microsoft Office product.

Where will your data be coming from? My guess is that you need a program or script that gathers the data you need, then open Excel and do whatever else it is that you need to do. Perhaps Microsoft Access, which can manipulate other MS Office applications with its VBA scripting language (this is what Microsoft calls "Office Automation," about which there's a lot of information on the web).
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Starting Excel (Macro) with PHP

Post by requinix »

As long as you're talking about running the macro on a XLS file on the server, COM can probably do it. Three downsides:
1. It doesn't work on Unix systems.
2. It requires that Office (at least Excel) is installed on the server.
3. It can be hard to research the right code.

Tip: Open up Excel and record a macro. Once recording, do whatever you want automated (in your case, run a macro). Then stop recording. Open up the script editor (whatever it's called) and find the code for the macro. That can be very nearly copied and pasted into your PHP (requires changing the syntax and such).
asyouwish
Forum Newbie
Posts: 6
Joined: Tue Nov 30, 2010 6:06 am

Re: Starting Excel (Macro) with PHP

Post by asyouwish »

tasairis wrote:As long as you're talking about running the macro on a XLS file on the server, COM can probably do it. Three downsides:
1. It doesn't work on Unix systems.
2. It requires that Office (at least Excel) is installed on the server.
3. It can be hard to research the right code.

Tip: Open up Excel and record a macro. Once recording, do whatever you want automated (in your case, run a macro). Then stop recording. Open up the script editor (whatever it's called) and find the code for the macro. That can be very nearly copied and pasted into your PHP (requires changing the syntax and such).


Hi,

Thank you.

I had a look and it looks promising. I found some code that I tried to run but I get this error all the time, from all of the codes:

Fatal error: Uncaught exception 'com_exception' with message 'Error [0x8002000e] Ogiltigt antal parametrar. ' in C:\wamp\www\Kiwi\batch\phpCreateExcel_1.php:31 Stack trace: #0 C:\wamp\www\Kiwi\batch\phpCreateExcel_1.php(31): variant->Save('test1.xls') #1 {main} thrown in C:\wamp\www\Kiwi\batch\phpCreateExcel_1.php on line 31

'Ogiltigt antal parametrar', text in red is swedish and basically means 'wrong number of parameters.

When I look at VBA code to save it seems like I should use 'SaveAs' but then I get:

Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Microsoft Office Excel<br/><b>Description:</b> Kunde inte hitta SaveAs egenskapen för Workbook klassen' in C:\wamp\www\Kiwi\batch\phpCreateExcel_1.php:31 Stack trace: #0 C:\wamp\www\Kiwi\batch\phpCreateExcel_1.php(31): variant->SaveAs('test1.xls') #1 {main} thrown in C:\wamp\www\Kiwi\batch\phpCreateExcel_1.php on line 31

'Kunde inte hitta SaveAs egenskapen för Workbook klassen', text in red means something like 'Could not find SaveAs property for Workbook class'

This is the code:

<?php
// set up excel
$excel = new COM("excel.application") or die("Unable to instantiate excel");
// run Excel silently, since we don’t want dialog boxes popping up in the background
$excel->DisplayAlerts = false;
// open up Excel file and select the first sheet, which contains the inputs
$excel->Workbooks->Add();
$book = $excel->ActiveWorkbook;
$sheets = $book->Sheets;
$sheet = $book->Worksheets(1);

$cell = $sheet->Cells(1, 1);
$cell->Activate;
$cell->value = "myValue";

/*
$input = array("kalle", "Anka", "And", "Duck");

// input stuff into excel spreadsheet
for ($i = 0; $i < count($input); $i++) {
$cell = $sheet->Cells(1, $i + 1);
$cell->Activate;
$cell->value = $input[$i];
}
*/

// run macro
//$excel->Run("runModel");

// save spreadsheet
$book->SaveAs("test1.xls");

// quit Excel and clean up
$book->Close(false);
unset($sheets);
$excel->Workbooks->Close();
unset($book);
$excel->Quit;
unset($excel);

?>
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Starting Excel (Macro) with PHP

Post by mikosiko »

One alternative that you can also consider is do everything directly in Excel, meaning:
- Connect to the DB and select the data. (using MYSQL ODBC driver.. or other depending of your DB Engine).
- Implement whatever macro/graphs/pivot/vba in Excel... no php involved.

Limitations/constraints:
- You have to install the MYSQL ODBC driver in your clients
- You must install the Excel Spreadsheet for each client or in a shared folder

depending of your situation maybe is something that you can evaluate.
asyouwish
Forum Newbie
Posts: 6
Joined: Tue Nov 30, 2010 6:06 am

Re: Starting Excel (Macro) with PHP

Post by asyouwish »

mikosiko wrote:One alternative that you can also consider is do everything directly in Excel, meaning:
- Connect to the DB and select the data. (using MYSQL ODBC driver.. or other depending of your DB Engine).
- Implement whatever macro/graphs/pivot/vba in Excel... no php involved.

Limitations/constraints:
- You have to install the MYSQL ODBC driver in your clients
- You must install the Excel Spreadsheet for each client or in a shared folder

depending of your situation maybe is something that you can evaluate.


Hi,

Yes, thought about that but due to the limitations and constraints you listed plus the almost certain problem with security settings on scripts etc I think it'll be a problem. Is it really this hard to kickstart an excel on the server? Using the _open event should work if it's not possible to start a specific script. Any ideas on VBA or VBS? Some stand alone script that could be started via PHP och a batch file?

Thanks for the input. Incase I don't get my script idea going I'll probably just have to go for this one...or I should be a little bit more positive and say, UNTIL :)

Br,
Isak
Post Reply