Date Problem

[marshyrob]

Hello

Im am trying to add some code to my script which basically determines what the day is whenever the script is run, and calculates the date it was on the sunday before.

Basically our backups run sunday night and get put into a folder called the date (yyyymmdd) 20060705. My script checks that folder for the backup files, but i have to manually put in the date via a msg box for it to know what date/folder to look for.

I just want to be able to run it anyday in the week and it know what folder to look in based on sundays date without me having to type it in every time.

I hope this makes sense, any help would be appreciated. Please bear in mind that im very new to VBscript but you guys have helped me before so this is the place to ask such a question.

Thanks in advance

Rob

 

[PHV]

A starting point:
date-weekday(date)+1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[marshyrob]

Great PHV

Im not too sure where to place that in my script so ive pasted the first part of the script, can you help with placement?

Here is the script that works:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

strDir = "H:\IT\Service Delivery\Production Environment Management\Networks and Telephony\Backups\"

Input = InputBox("Enter the Date Required 'yyyymmdd'")
strDate = Input

Set objWorkbook = objExcel.Workbooks.Open (strDir & "Backup Report.xls", 1)
Set objWorksheet = objExcel.Worksheets("Cisco")
objWorksheet.Activate

DeviceCount = 8
strDevice = objExcel.Worksheets("Cisco").Cells(DeviceCount,3).Value

Do While strDevice <> ""

strMake = objExcel.Worksheets("Cisco").Cells(DeviceCount,5).Value
strFile = strDir & strMake & "\" & strDate & "\" & strDevice & ".txt"

If objFSO.FileExists(strDir & strMake & "\" & strDate & "\" & strDevice & ".txt") Then

Set objFILE = objFSO.GetFile(strFile)
strModifyDate = objFILE.DateLastModified
strLapseTime = DateDiff("d", strModifyDate, Now)

Else
strLapseTime = ""
End If

If strLapseTime = "" Then
CellColour = 3
ElseIf strLapseTime <7 Then
CellColour = 4
ElseIf strLapseTime <14 Then
CellColour = 44
End If


objExcel.Cells(DeviceCount,4).Interior.ColorIndex = CellColour

DeviceCount = DeviceCount + 1
strDevice = objExcel.Worksheets("Cisco").Cells(DeviceCount,3).Value

Loop

Like i said im very new to all this so the script is very basic!

Thanks

Rob

 

[mrmovie]

does PHV's code give you the same output as you type in your window

Input = InputBox("Enter the Date Required 'yyyymmdd'")
strDate = Input

? if so, then jsut replace

strDate = Input

with

strDate = date-weekday(date)+1

 

[marshyrob]

Hi Mrmovie

I did as you said but it doesnt seem to work, the script still runs but shows me that there are no backups there so that tells me that it hasnt looked in the right place as the files are there.

How do i test what "date-weekday(date)+1" gives me?

Rob

 

[mrmovie]

Wscript.Echo date-weekday(date)+1

or

Msgbox date-weekday(date)+1

 

[mrmovie]

if it isnt giving you date in the right format then you will need to manip it

you could use something like

strTemp = date-weekday(date)+1
strDate = Year(strTemp) & Month(strTemp) & ...

but i am sure there is a DateFormat command

 

[marshyrob]

Thanks mrmovie that nearly quite what i need just need to mangle the date so that it use yyyymmdd at the moment its dd/mm/yyyy

Cheers for all your help guys, ill try and figure the rest out.

Rob

 

[PHV]

strTemp = date-weekday(date)+1
strDate = Year(strTemp) & Right("0" & Month(strTemp),2) & Right("0" & Day(strTemp),2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886