Calculate Sheet Option
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(GetYearTemplateDirecto ry & "\" & 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).Colu mn - 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).Visi ble = 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 |
Calculate Sheet Option
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(GetYearTemplateDirecto ry & "\" & 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).Colu mn - 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).Visi ble = 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 |
Calculate Sheet Option
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. This is axactly what I needed to change - thanks |
All times are GMT +1. The time now is 04:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com