Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using Dateserial to show data from Excel based on date

Status
Not open for further replies.
althea

althea

MIS
Oct 15, 2001
134
US
Hi,
I'm trying to show data (5 days at a time) from sheets in Excel (based on current date) on a web page. I have the code working, except for when the five days includes 2 different months. Like today, the data for tomorrow would be on a sheet called May instead of April. For some reason, my web page shows no data after today! Here is an example of the code, I would appreciate any help since I'm new at this! Here is the beginning of my code, I won't post all of it because it's 13 pages alltogether! Thanks!

<SCRIPT language=vbscript>
function getXls()
'get variables
mnth = MonthName(Month(Now), 1)
mnth1 = Monthname(Month(Dateserial(year(now), month(now), day(now)+1)))
mnth2 = Monthname(Month(Dateserial(year(now), month(now), day(now)+2)))
mnth3 = Monthname(Month(Dateserial(year(now), month(now), day(now)+3)))
mnth4 = Monthname(Month(Dateserial(year(now), month(now), day(now)+4)))


'Open Workbook
set excelObj = CreateObject(&quot;excel.application&quot;)
on error goto 0
excelObj.Workbooks.Open &quot;c:\test.xls&quot;, True

excelObj.ActiveSheet.Cells(1, 1).Activate
excelObj.ActiveCell.Offset(1, Day(Now)+2).Activate
form1.r113a.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r113b.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r113c.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r113d.value = excelObj.ActiveCell.value


excelObj.ActiveSheet.Cells(1, 1).Activate
excelObj.ActiveCell.Offset(5, Day(Now)+2).Activate
form1.r115a.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r115b.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r115c.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r115d.value = excelObj.ActiveCell.value

excelObj.ActiveSheet.Cells(1, 1).Activate
excelObj.ActiveCell.Offset(9, Day(Now)+2).Activate
form1.r117a.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r117b.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r117c.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r117d.value = excelObj.ActiveCell.value

excelObj.ActiveSheet.Cells(1, 1).Activate
excelObj.ActiveCell.Offset(13, Day(Now)+2).Activate
form1.r119a.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r119b.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r119c.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r119d.value = excelObj.ActiveCell.value

excelObj.ActiveSheet.Cells(1, 1).Activate
excelObj.ActiveCell.Offset(17, Day(Now)+2).Activate
form1.r121a.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r121b.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r121c.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r121d.value = excelObj.ActiveCell.value


excelObj.Worksheets(mnth1).Activate


excelObj.ActiveSheet.Cells(1, 1).Activate
excelObj.ActiveCell.Offset(1, Day(Now)+3).Activate
form1.r113a1.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r113b1.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r113c1.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r113d1.value = excelObj.ActiveCell.value

excelObj.ActiveSheet.Cells(1, 1).Activate
excelObj.ActiveCell.Offset(5, Day(Now)+3).Activate
form1.r115a1.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r115b1.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r115c1.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r115d1.value = excelObj.ActiveCell.value

excelObj.ActiveSheet.Cells(1, 1).Activate
excelObj.ActiveCell.Offset(9, Day(Now)+3).Activate
form1.r117a1.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r117b1.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r117c1.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r117d1.value = excelObj.ActiveCell.value

excelObj.ActiveSheet.Cells(1, 1).Activate
excelObj.ActiveCell.Offset(13, Day(Now)+3).Activate
form1.r119a1.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r119b1.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r119c1.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r119d1.value = excelObj.ActiveCell.value

excelObj.ActiveSheet.Cells(1, 1).Activate
excelObj.ActiveCell.Offset(17, Day(Now)+3).Activate
form1.r121a1.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r121b1.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r121c1.value = excelObj.ActiveCell.value
excelObj.ActiveCell.Offset(1, 0).Activate
form1.r121d1.value = excelObj.ActiveCell.value

'Close Workbook and Excel Object
excelObj.DisplayAlerts = False
excelObj.Workbooks.Close
set excelObj = nothing


end function
</SCRIPT>
<BODY onload=&quot;vbscript:getXls()&quot;>
<FORM name=form1>
<p align=&quot;right&quot;><b><font size=&quot;5&quot; color=&quot;#660066&quot; face=&quot;Arial&quot;>test-


<script language = vbscript>
document.write Dateserial(year(now), month(now), day(now)) & &quot; &quot; & weekdayname(Weekday(day(now)+2))
</script>
</font></b></td>
<td width=&quot;199&quot; bgcolor=&quot;#660066&quot; height=&quot;19&quot; style=&quot;border-left-style:none; border-right-style:solid; border-right-color:#660066; border-top-style:none; border-bottom-style:none&quot;>

<p align=&quot;left&quot;><font color=&quot;#FFFFFF&quot; face=&quot;Arial&quot; size=&quot;2&quot;><b>

<script language = vbscript>
document.write Dateserial(year(now), month(now), day(now)+1) & &quot; &quot; & weekdayname(Weekday(day(now)+3))
</script>
</b></font></td>
<td width=&quot;178&quot; bgcolor=&quot;#660066&quot; height=&quot;19&quot; style=&quot;border-left-style:none; border-right-style:solid; border-right-color:#660066; border-top-style:none; border-bottom-style:none&quot;>

<p align=&quot;left&quot;><font color=&quot;#FFFFFF&quot; face=&quot;Arial&quot; size=&quot;2&quot;><b>

<script language = vbscript>
document.write Dateserial(year(now), month(now), day(now)+2) & &quot; &quot; & weekdayname(Weekday(day(now)+4))
</script>
</b></font></td>
<td width=&quot;178&quot; bgcolor=&quot;#660066&quot; height=&quot;19&quot; style=&quot;border-left-style:none; border-right-style:solid; border-right-color:#660066; border-top-style:none; border-bottom-style:none&quot;>

<p align=&quot;left&quot;><font color=&quot;#FFFFFF&quot; face=&quot;Arial&quot; size=&quot;2&quot;><b>

<script language = vbscript>
document.write Dateserial(year(now), month(now), day(now)+3) & &quot; &quot; & weekdayname(Weekday(day(now)+5))
</script>
</b></font></td>
<td width=&quot;178&quot; bgcolor=&quot;#660066&quot; height=&quot;19&quot; style=&quot;border-left-style:none; border-right-style:solid; border-right-color:#660066; border-top-style:none; border-bottom-style:none&quot;>

<p align=&quot;left&quot;><font color=&quot;#FFFFFF&quot; face=&quot;Arial&quot; size=&quot;2&quot;><b>

<script language = vbscript>
document.write Dateserial(year(now), month(now), day(now)+4) & &quot; &quot; & weekdayname(Weekday(day(now)+6))
</script>
</b></font></td>
</tr>
<tr>
<td width=&quot;128&quot; bgcolor=&quot;#FFFF00&quot; height=&quot;29&quot; style=&quot;border-style: none; border-width: medium&quot;>
<font face=&quot;Arial&quot; style=&quot;font-size: 9pt&quot;>Room 113</font></td> <td width=&quot;246&quot; bgcolor=&quot;#FFFF00&quot; height=&quot;29&quot; style=&quot;border-style: none; border-width: medium&quot; align=&quot;center&quot;>
<font face=&quot;Arial&quot; style=&quot;font-size: 9pt&quot;>a</font></td>
<td width=&quot;150&quot; style=&quot;border-left-style:none; border-right-style:solid; border-right-color:#660066; border-top-style:none; border-bottom-style:none&quot; bgcolor=&quot;#FFFF00&quot; height=&quot;29&quot;>
 </td>
<td width=&quot;178&quot; style=&quot;border-left-style:none; border-right-style:solid; border-right-color:#660066; border-top-style:none; border-bottom-style:none&quot; height=&quot;29&quot;> <font size=&quot;1&quot;> <INPUT name=r113a size=&quot;27&quot; style=&quot;border: 1px solid #FFFFFF;&quot;></font></td>
<td width=&quot;199&quot; style=&quot;border-left-style:none; border-right-style:solid; border-right-color:#660066; border-top-style:none; border-bottom-style:none&quot; height=&quot;29&quot;> <INPUT name=r113a1 size=&quot;27&quot; style=&quot;border: 1px solid #FFFFFF;&quot;></td>
<td width=&quot;178&quot; style=&quot;border-left-style:none; border-right-style:solid; border-right-color:#660066; border-top-style:none; border-bottom-style:none&quot; height=&quot;29&quot;> <INPUT name=r113a2 size=&quot;27&quot; style=&quot;border: 1px solid #FFFFFF;&quot;></td>
</tr>
</table>
</FORM></BODY></HTML>
 
Status
Not open for further replies.

Similar threads

Brianfree
  • Locked
  • Question
Excel Rows to Column 1
Replies
2
Views
336
Brianfree
Brianfree
shilpa2002
  • Locked
  • Question
how to copy data from two excel workbooks into third. merging data based on common field
Replies
3
Views
254
SkipVought
S
asel
  • Locked
  • Question
Copy cell data from excel and paste into portable software application
Replies
0
Views
174
asel
asel
B
  • Locked
  • Question
MsgBox on alternate days
Replies
5
Views
632
strongm
strongm
Alexvb6
  • Locked
  • Question
Deprecation of VBScript + Classic ASP: How to Ask Microsoft to cancel their planned craziness
Replies
0
Views
420
Alexvb6
Alexvb6

Part and Inventory Search

Sponsor

Back
Top