How to duplicate a workbook without formulas?

Topics: Developer Forum
Oct 6, 2010 at 9:44 PM

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 9:52 PM
Edited Oct 6, 2010 at 9: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?

Oct 7, 2010 at 2:55 PM

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.

Oct 7, 2010 at 4:04 PM

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
                            Kill (NewReportPath)
                            ActiveWorkbook.SaveAs Filename:= _
                            NewReportPath, _
                            FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
                            ReadOnlyRecommended:=False, CreateBackup:=False
                            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                            :=False, Transpose:=False
                            Application.CutCopyMode = False
                            For Each Sheet In ActiveWorkbook.Worksheets
                                Sheet.Protect Password:="******", Scenarios:=True, UserInterfaceOnly:=True
                            Exit For
                    End If

                    For Each w In Application.Workbooks ' Saving each open file

                    Next w
                    Application.ScreenUpdating = True