Single quote in filename causing problems
Dave, you must be psychic <g
I didn't follow at all !
Regards,
Peter T
"Dave Peterson" wrote in message
...
This worked ok for me:
vTest = Application.Evaluate("'" _
& Replace(Application.ActiveWorkbook.Name, "'", "''") _
& "'!" & sSPEC_Name)
geoff_ness wrote:
I would like to be able to evaluate the value of the named constant
sSPEC_NAME in the active workbook by using application.evaluate, as
follows:
Dim vTest as Variant
vTest = Application.Evaluate("'" & Application.ActiveWorkbook.Name &
"'!" & sSPEC_NAME)
This works fine, except where ActiveWorkbook.Name contains a single
quote, which causes the string being evaluated to become nonsense. Can
anyone suggest an alternative approach?
The most obvious to me would be
vTest =
Application.Evaluate(Application.ActiveWorkbook.Na mes(sSPEC_NAME).RefersTo)
but the issue then is that this will raise a runtime error where there
is no constant named sSPEC_NAME in the active workbook. Ideally I
would be to escape the single quote somehow in the first approach - is
there a way to do this?
TIA
--
Dave Peterson
|