Im using a template that has all the formulas, after that i have an script that create a new file with all the data that i want.
Is there a way that this new file appear without formulas but with the data already calculated? or create a copy of this one created withour the formulas?
If yes, how can i do it? cuz i cant send the workbooks to our clients with formulas.
Oct 6, 2010 at 10:52 PM
Edited Oct 6, 2010 at 10:54 PM
I have absolutely no idea how you'd do that in Excel itself. If I were to try and do it in PHPExcel, I'd create a new workbook, then iterate through every cell of your template workbook reading the calculated value and formatting, then setting those in the
appropriate cell your new workbook and saving that.
Why can't you send workbooks that contain formulas to your clients?
Hahaha.... im with you on this... im new in this company, and the guy before me he used to program the excel reports with macros, but the big problem is when we run the reports, these reports eat the Database Server, imagine like 60 reports using
ODBC, some reports last more than one minute running, but i found out this library and its really nice, im working in each case one by one. Reports that run with macros that last 30 seconds more or less, i can run them in 1 or 2 seconds, and the
database doesnt feel them running.
Right now im sorting little things, and this is one of the case, the guy before me, he use to run every worksheet, cell by cell and made a copy of the data to a new file without the formulas. thats not really nice!!....
But the same question that you write here, i have done that too?? why cant we send reports with the formulas? and they say that its part of the politics of data security.
this is a copy of one of the code used here to create a copy of one workbook without formulas.... maybe you undertand better than me.
'Saving a copy without formulas
Sheets(Array(1, 2, 3, 4, 6, 7, 8)).Copy
For Each Window In Windows
If UCase(ActiveWindow.Caption) <> "Windtelecom.xls" Then
WindownName = ActiveWindow.Caption
If fs.fileexists(NewReportPath) Then
ActiveWorkbook.SaveAs Filename:= _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Application.CutCopyMode = False
For Each Sheet In ActiveWorkbook.Worksheets
Sheet.Protect Password:="******", Scenarios:=True, UserInterfaceOnly:=True
For Each w In Application.Workbooks ' Saving each open file
Application.ScreenUpdating = True