Are you writing about .enablecalculation or you writing about the
application.calculation status.
If you're writing about the first, then change the template--or change the
setting after you create the new worksheet.
If you're writing about the second, then this is an application setting--it's
picked up by the first workbook you open in that session. But you can always
change it to automatic.
Or am I just missing the point (it's happened before!).
Terry Holland wrote:
>
> I have a vb app that programmatically creates an Excel template based on data
> in a couple of other excel worksheets.
>
> The main function that creates this template is posted below. In this you
> will see that I have the following
>
> objWBROI - New workbook that I am creating3
> objWSCostCentres - Existing worksheet that I pull some data from to crate
> new workbook
> objWSTemplate - Second worksheet that I use to create new workbook
>
> The following loop is where i add copies of objWSTemplate to my new workbook
>
> For intCostCentre = 2 To intCostCentres
> SetBudgetFigures objWSCostCentres, objWSTemplate, intCostCentre
> objWSTemplate.Copy after:=objWBROI.Sheets(objWBROI.Sheets.Count)
> objWBROI.ActiveSheet.Name = objWSCostCentres.Cells(RI_COSTCENTREROW,
> 1 + intCostCentre)
> Next
>
> My problem is that, even though objWSTemplate has the Calculate Worksheet
> Automatically set, all copies of this sheet in the new workbook have this
> option set to Manual. How do I overcome this?
>
> Public Function CreateTemplate(intYear As Integer)
>
> Const RI_COSTCENTREROW = 3
>
> Dim objWBCostCentreBudgets As Excel.Workbook 'Workbook that contains
> cost centre budgets
> Dim objWSCostCentres As Excel.Worksheet 'Worksheet that contains
> list of cost centres and their budgets
> Dim objWSTemplate As Excel.Worksheet 'Worksheets that
> contains ROI template
>
> Dim objWBROI As Excel.Workbook 'New ROI workbook that
> is generated from list of cost centre budgets
>
> Dim intCostCentres As Integer
> Dim intCostCentre As Integer
>
> m_intYear = intYear
>
> m_udtProps.strTemplatePath = GetYearTemplateDirectory & "\" & DEF_ROI
> 'TEMPLATE
> m_udtProps.strBudgetLookupPath = GetYearTemplateDirectory & "\" &
> DEF_COSTCENTREBUDGETS
>
> Set objWBCostCentreBudgets = OpenCostCentreBudgets
>
> Set objWBROI = m_objExcelApp.Workbooks.Add(GetYearTemplateDirectory &
> "\" & DEF_ROITEMPLATE) 'new workbook for all cost-centre roi templates
>
> 'based on roi.xlt
>
> Set objWSCostCentres = objWBCostCentreBudgets.Sheets("CostCentres")
> 'List of cost centres
> Set objWSTemplate = objWBROI.Sheets("CostCentreTemplate")
> 'ROI Template
>
> m_objExcelApp.ScreenUpdating = False
>
> 'Get number of cost centres in list
> intCostCentres = objWSCostCentres.Cells(1,
> objWSCostCentres.Columns.Count).End(xlToLeft).Column - 1
>
> For intCostCentre = 2 To intCostCentres
> SetBudgetFigures objWSCostCentres, objWSTemplate, intCostCentre
> objWSTemplate.Copy after:=objWBROI.Sheets(objWBROI.Sheets.Count)
> objWBROI.ActiveSheet.Name = objWSCostCentres.Cells(RI_COSTCENTREROW,
> 1 + intCostCentre)
> Next
>
> 'Hide template sheets
> objWBROI.Worksheets(SHEET_CONTESTDATA).Visible = False
> objWBROI.Worksheets(SHEET_COSTCENTRETEMPLATE).Visible = False
>
> m_objExcelApp.ScreenUpdating = True
>
> 'objWBROI.SaveAs GetDirectoryName & "\" & DEF_ROI
> objWBROI.SaveAs GetYearTemplateDirectory & "\" & DEF_ROI
>
> 'Tidy up
> objWBROI.Close
> objWBCostCentreBudgets.Close
> Set objWSCostCentres = Nothing
> Set objWBCostCentreBudgets = Nothing
>
> End Function
--
Dave Peterson
|
Similar Threads
