editing a value within another excel (or non-excel) script.

Hi There,

I have set up some scripting within excel to automate some report production, getting data from a call solution called CMS. It works great so long as the data I require is from yesterday. It kinda falls down when I need data from 3 or 4 days ago. I have around 15 scripts set up (each getting a specific piece of data) that all contain the following entry to specify the date its requesting data for:

Rep.SetProperty "Date","-1"

where -1 indicates the previous day.

I also run the following code to run through each of the scripts sequentially (means I only have one click or task to schedule rather than dozens).

Option Explicit 

Call ExecuteDirectory("DRIVE:\FOLDER 1\FOLDER 2\DESTINATION FOLDER") 

Function ExecuteDirectory(strPath2Folder) 
    Dim fso, f, fc, f1, strFiles, intFiles
    Dim WshShell
    Set WshShell = CreateObject("WScript.Shell")
    strFiles = "" 
    Set fso = CreateObject("Scripting.FileSystemObject") 
    If (fso.FolderExists(strPath2Folder)) Then 
        Set f = fso.GetFolder(strPath2Folder) 
        Set fc = f.Files 
        
For Each f1 in fc  
        Dim fileToRun
        fileToRun = strPath2Folder & "\" & f1.Name
        WshShell.Run Chr(34) & fileToRun & Chr(34), 1, true 
    Next
        Set f1 = Nothing 
        Set fc = Nothing 
        Set f = Nothing 
    End If
	End Function

What I would like would be a "pop-up" box open that will allow me to enter a value that replaces the "-1" in each of the scripts it then opens and runs. I could enter -3 or a specific date in the fomant "DD/MM/YYYY".


Is this possible?

your help is greatly appreciated.

Can you actually read/write from these scripts?

Maybe something like this...

    Dim d As String
    Do
        d = Application.InputBox("Enter a Date " & vbLf & _
                                 "or number of days previous as a negative value. ", _
                                 "Date or Days Previous", Type:=2)
        If d = "False" Then Exit Sub 'User canceled
        If IsDate(d) Then Exit Do
        If IsNumeric(d) Then
            If d < 0 Then Exit Do
        End If
        MsgBox "Enter a Date or Negitive number", vbExclamation, "Invalid Entry"
    Loop
    
    If IsDate(d) Then
        Rep.SetProperty CDate(d), "0"
    Else
        Rep.SetProperty "Date", d
    End If

Not sure if CDate(d) is an acceptable data type for the Rep. property

Is this in the script(s) you are running or in VBA?

Rep.SetProperty "Date","-1"

tandwfund

Are you sure you are in the right thread?

That was a question for the OP, Kramxel.

So can you read/write these scripts?

Apologies for the late reply - had a bout of sickness and just couldn't bring myself to sit in front of a computer, until a couple of days ago. A few days off and I managed to solve the problem when I got back. Having solved this problem I now have another that i'll post in a seperate thread.

Thank you and apologies all rolled into one.