Read cell contents from a closed file?

[debauch]

Hello,

I am trying to run a VB script. Currently, there is a hardcoded date in there, that pulls data ( -1 which represents yesterday). I want to have a file, where the file will have say -3 in it, without having to change the hardcoded date anymore.


dayPath = "I:\TimeRun\Extract\Data\Scripts\Days.xls" _
& Sheets("RunDate").Range("a2")


dayPath used to just be "-1" , but I want to be able to update the days file to what ever date I want, and then run the script. The vba I wrote to try and do that though is not working. Is it even possible to pull a cell's contents if the file is closed? Most of these scripts run when people are not in the office.

[Bob Phillips]

Why not just open the workbook in the script, it makes life simpler.

[mdmackillop]

If you really have to, check out this kb item (http://www.vbaexpress.com/kb/getarticle.php?kb_id=454)

[debauch]

Sorry - was on vacation.

Ok, the script that is running is based on VB, but is a script with an .acvsauto extention.

This is how I tried to modify it.

Dim myDate, yDate, yearDate, monthDate, dayDate, monthVal, dayVal, ySkill, iDate
Dim FSO, FileName
FileName = "I:\Scripts\Days.xls" & Sheet1.[A2]
Set FSO = CreateObject("Scripting.FileSystemObject")

iDate = FileName
yDate = DateAdd("d", iDate, Date)
dayVal = Day(yDate)
monthVal = Month(yDate)
yearDate = Right(DatePart("yyyy", yDate), 2) This is how it was hardcoded previously, but would like that part to be dynamic.
iDate = "-1"
These scripts run over night using '-1' but I want to be able to run a whole week easily , at any point . I want it to point to an excel file , where I can just put -5 in the excel , and based on the scripts (there are several) it will just read the excel file.

Not sure if this makes any sense?