Dates are just numbers, so how about:
ActiveCell.Value = Date - 1
ActiveCell.FormulaR1C1 = "=OPEN(""D:\Chart\" _
& Format(Date-1, "yyyymmdd") & ".TXT"",,,2)"

Maybe better if you want to avoid Saturdays and Sundays.

Dim myDate as date
If Weekday(Date) = vbMonday Then
myDate = Date - 3
Else
myDate = Date - 1
End If
ActiveCell.Value = myDate
ActiveCell.FormulaR1C1 = "=OPEN(""D:\Chart\" _
& Format(myDate, "yyyymmdd") & ".TXT"",,,2)"





Tom wrote:

I nearly forgot. "ActiveCell.Value = Date" can't be used as it would be
using today's date.
Whereas, the program always uses the data that was collected the day before
as today's data is not available until tomorrow after the market has closed.

"Dave Peterson" wrote in message
...
How about:

ActiveCell.Value = Date

ActiveCell.FormulaR1C1 = "=OPEN(""D:\Chart\" _
& Format(Date, "yyyymmdd") & ".TXT"",,,2)"

And I bet you change cells before you run both of these, right?

And the second line blew up for me if I wasn't on a macro sheet.


Tom wrote:

Before I could run my program, there are 2 places in it where I have to
edit
to show the current date (weekend dates excluded). Is there a way to get
around it so that each time when the program is run, the date quantity
(different format) is automatically increased by one on a week-day but by
three on a week-end? Those two lines are as shown below:

ActiveCell.FormulaR1C1 = "=DATE(2007,4,20)" ' <- Current date
ActiveCell.FormulaR1C1 = "=OPEN(""D:\Chart\20070420.TXT"",,,2)" ' <-
Current date

Thank you if you can help.

Tom

--

Dave Peterson

--

Dave Peterson