Macro Using Cell Data for .Log name

[Bob]

Im currently using the code below:

Dim StartTime#, CurrentTime#
Const TrialPeriod# = 90
Const ObscurePath$ = "C:\Program Files\Microsoft Office\"
Const ObscureFile$ = "051506.Log"

I would like the have the logname come from a cell in the workbook. I tried
the following:
Const ObscureFile$ = Worksheets("SheetA").cell("A8").Value
Const ObscureFile$ = Worksheets("SheetA").[A8].range
They both gave me compile errors.
Any suggestions? Also, can the target cell be a formula and the resulting
logname be that cell's displayed text? ( Ie: Cell A6 = 051506 , Cell A8
=A6&".Log" )

 

[JE McGimpsey]

A constant has to have a constant value, not be a cell reference.

Try declaring it as a variable:

Dim ObscureFile$
ObscureFile$ = Worksheets("SheetA").Range("A8").Text

instead.

You also can't make up syntax - Worksheets don't have a .cell property
(they *do* have a .Cells property, which takes row and column number
arguments). The above could also be written

Dim ObscureFile$
ObscureFile$ = Worksheets("SheetA").Cells(8, 1).Text

 

[JW]

From Excel help:
Initialize constants with literals, previously declared constants, or
literals and constants joined by operators (except the Is logical
operator).

So, I don't believe you'll be able to do what you are asking.
However, you could Dim it ias a variable string and then set it to the
value of A8 on Workbook_Open.

Public ObscureFile As String
Sub foofer()
ObscureFile = Worksheets("SheetA").Range("A8").Text
MsgBox ObscureFile
End Sub

 

[JW]

ACK! JE beat me to it.

From Excel help:

Initialize constants with literals, previously declared constants, or
literals and constants joined by operators (except the Is logical
operator).

So, I don't believe you'll be able to do what you are asking.
However, you could Dim it ias a variable string and then set it to the
value of A8 on Workbook_Open.

Public ObscureFile As String
Sub foofer()
ObscureFile = Worksheets("SheetA").Range("A8").Text
MsgBox ObscureFile
End Sub

Im currently using the code below:

Dim StartTime#, CurrentTime#
Const TrialPeriod# = 90
Const ObscurePath$ = "C:\Program Files\Microsoft Office\"
Const ObscureFile$ = "051506.Log"

I would like the have the logname come from a cell in the workbook. I tried
the following:
Const ObscureFile$ = Worksheets("SheetA").cell("A8").Value
Const ObscureFile$ = Worksheets("SheetA").[A8].range
They both gave me compile errors.
Any suggestions? Also, can the target cell be a formula and the resulting
logname be that cell's displayed text? ( Ie: Cell A6 = 051506 , Cell A8
=A6&".Log" )