T
todtown
I have a VB app that controls an Excel template (opens it, runs code
against it, closes it).
I have a statement in the code that uses Evaluate with an Excel
formula that contains named ranges. Normally, the Excel template is
opened by the VB code, the workbook is assigned to an object variable
and I go from there. If the workbook is already open, I simply use
GetObject to assign the open workbook to the object variable, then
proceed as usual.
If the code opens the workbook, my formula works just fine:
CurrentColumn = DataSheet.Range(Evaluate("=ADDRESS(MATCH(""Total
Surveys Sent"",Customer_Satisfaction,
0)+ROW(Customer_Satisfaction)-1,MATCH(""" & CurrentMonth &
""",Data_Month,0)+COLUMN(Data_Month)-1)")).Column
If I just grab the existing workbook, the formula fails. I've
discovered from some testing that it's the named ranges that are
tripping up. Is there something about named ranges I need to know
about to make these formulas work with workbooks that are already
open?
tod
against it, closes it).
I have a statement in the code that uses Evaluate with an Excel
formula that contains named ranges. Normally, the Excel template is
opened by the VB code, the workbook is assigned to an object variable
and I go from there. If the workbook is already open, I simply use
GetObject to assign the open workbook to the object variable, then
proceed as usual.
If the code opens the workbook, my formula works just fine:
CurrentColumn = DataSheet.Range(Evaluate("=ADDRESS(MATCH(""Total
Surveys Sent"",Customer_Satisfaction,
0)+ROW(Customer_Satisfaction)-1,MATCH(""" & CurrentMonth &
""",Data_Month,0)+COLUMN(Data_Month)-1)")).Column
If I just grab the existing workbook, the formula fails. I've
discovered from some testing that it's the named ranges that are
tripping up. Is there something about named ranges I need to know
about to make these formulas work with workbooks that are already
open?
tod