Macro Using Cell Data for .Log name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 18 Sep 2007 17:04 GMT

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" )

Reply to this Message

JE McGimpsey - 18 Sep 2007 17:18 GMT

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

> Im currently using the code below:
>
[quoted text clipped - 11 lines]
> logname be that cell's displayed text? ( Ie: Cell A6 = 051506 , Cell A8
> =A6&".Log" )

Reply to this Message

JW - 18 Sep 2007 17:22 GMT

>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:
>
[quoted text clipped - 11 lines]
> logname be that cell's displayed text? ( Ie: Cell A6 = 051506 , Cell A8
> =A6&".Log" )

Reply to this Message

JW - 18 Sep 2007 17:23 GMT

ACK! JE beat me to it. :-)

> >From Excel help:
> Initialize constants with literals, previously declared constants, or
[quoted text clipped - 26 lines]
> > logname be that cell's displayed text? ( Ie: Cell A6 = 051506 , Cell A8
> > =A6&".Log" )

Reply to this Message