| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automated calculations? What's the best way? | Excel Discussion (Misc queries) | |||
| Automated printing | Excel Discussion (Misc queries) | |||
| Excel 2002 automated annual date change | Excel Discussion (Misc queries) | |||
| Run automated macros | Excel Discussion (Misc queries) | |||
| Automated Linking | Excel Discussion (Misc queries) | |||
Linear Mode
