Php code to run a macro after outputting excel sheet

[shara]

Hello,

I have a php script that outputs an excel sheet.The user has to download macro every time he wants to run the macro on the excel sheet.

Is there anything in php where the code downloads macro also along with the excel sheet or something like a button in excel sheet itself, clicking on which the macro runs on the excel sheet.

Any help is well appreciated.

Thanks&Regards,
Shara.

[shara]

Hello,

Please consider the query, as its important for me to solve it.

I've been trying to search for a solution through out the day, but its of no use.

Any help to solve the problem in some other way is also welcome.

Thanks&Regards,
Shara.

Hello,

I have a php script that outputs an excel sheet.The user has to download macro every time he wants to run the macro on the excel sheet.

Is there anything in php where the code downloads macro also along with the excel sheet or something like a button in excel sheet itself, clicking on which the macro runs on the excel sheet.

Any help is well appreciated.

Thanks&Regards,
Shara.

[jenkinsloveschicken]

Shara,

I have a couple of questions regarding your posts.

1. Is there only one user affected in this scenario? ie. Custom report, etc? If so, an easy way to do this would be to have the user save the macro code into the PERSONAL.XLS file on the local machine(or A/D profile, etc) and assign a short-cut key to the macro. This way when the user exports the data, he/she can just use a two-key shortcut to achieve the desired result.

2. Is this a dynamic report or this a static page that is generated by a static backend? If it is a static file, then I would suggest writing the export data to a file server-side that already contains the macro and the corresponding command button. Then have the user download the file from the server after your script outputs/writes the data. *Note: Depending on network security settings, having the user download active macro content may not be a viable solution.

If this is a dynamic report that affects a substantial amount of users, then I would suggest doing research on the Excel API call. Once you get in, you should be able to execute macro code that you would escape in the php script.

Hope this helps.

Regards,
Jenkins

[shara]

Hello Jenkins,

This is a dynamic report that affects a substantial amount of users.I'll try the proposed.

Thanks for your help.

Regards,
Shara.

Shara,

I have a couple of questions regarding your posts.

1. Is there only one user affected in this scenario? ie. Custom report, etc? If so, an easy way to do this would be to have the user save the macro code into the PERSONAL.XLS file on the local machine(or A/D profile, etc) and assign a short-cut key to the macro. This way when the user exports the data, he/she can just use a two-key shortcut to achieve the desired result.

2. Is this a dynamic report or this a static page that is generated by a static backend? If it is a static file, then I would suggest writing the export data to a file server-side that already contains the macro and the corresponding command button. Then have the user download the file from the server after your script outputs/writes the data. *Note: Depending on network security settings, having the user download active macro content may not be a viable solution.

If this is a dynamic report that affects a substantial amount of users, then I would suggest doing research on the Excel API call. Once you get in, you should be able to execute macro code that you would escape in the php script.

Hope this helps.

Regards,
Jenkins

[shara]

Hello Jenkins,

I tried searching for excel API call and got a code like this:

<?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->Open(”model.xls”);
$book = $excel->ActiveWorkbook;
$sheets = $book->Sheets;
$sheet = $book->Worksheets(1);
 
// input stuff into excel spreadsheet
for ($i = 0; $i < count($_POST[input]); $i++) {
$cell = $sheet->Cells(1, $i + 1);
$cell->Activate;
$cell->value = $input[$i];
}
 
// run macro
$excel->Run(”runModel”);
 
// save spreadsheet
$book->SaveAs(”model.xls”);
 
// quit Excel and clean up
$book->Close(false);
unset($sheets);
$excel->Workbooks->Close();
unset($book);
$excel->Quit;
unset($excel);
 
 

I modified the above code to suit my code like this

  $book = $workbook->ActiveWorkbook;
  $sheets = $book->Sheets;
 
  $workbook->Run("TTS_PROJECT_REPORT_MACRO.xls");
  $book->SaveAs($filename.".xls");
  $workbook->close();
 

I avoided calling COM as it is giving error to me.Even then the code is not working.It is giving an error saying RUN is a non-existent function.

Can you please help with this?

Any other suggestions are welcome.

Thanks&Regards,
Shara.

Hello Jenkins,

This is a dynamic report that affects a substantial amount of users.I'll try the proposed.

Thanks for your help.

Regards,
Shara.