How can the date be automated?
 

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

How can the date be automated?
 

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

How can the date be automated?
 

"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?
You are absolutely 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

That's great. Thanks again Dave.

Tom

How can the date be automated?
 

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

How can the date be automated?
 

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

How can the date be automated?
 

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.

"Dave Peterson" wrote in message
...
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

How can the date be automated?
 

Glad it worked for you.



Tom wrote:

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.

"Dave Peterson" wrote in message
...
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

--

Dave Peterson