How can the date be automated?

T

Tom

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
 
D

Dave Peterson

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.
 
T

Tom

Dave Peterson said:
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?
Click to expand...
You are absolutely right.
And the second line blew up for me if I wasn't on a macro sheet.
Click to expand...

That's great. Thanks again Dave.

Tom
 
T

Tom

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.
 
D

Dave Peterson

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

Tom

Once again you simply navigate your way around the problem and come up with
a neat solution. Only a person with good mastery of the subject can do that.
Congratulations, and many thanks, Dave.
 
D

Dave Peterson

Glad it worked for you.


Once again you simply navigate your way around the problem and come up with
a neat solution. Only a person with good mastery of the subject can do that.
Congratulations, and many thanks, Dave.
Click to expand...
 
Top