I work in an engineering firm, and there is a nice lady who keeps track of the shop drawings that come in and go out for all the various projects that are ongoing. Her process, every couple days, is to open EACH of (3) excel files for our different disciplines for EACH project to make sure that there aren't any outstanding - this winds up being over 100 excel files. She has a number of tricks to make this easier on her, but ultimately, several people log them in, so she winds up opening them all just to make sure she isn't missing anything. She does it this way because that's the way she's always done it and I'd like to help make her life easier.
I have it working when I specify a single excel file - See FilePath below. But when I try to put ComObjGet(FullFilePath) in my parsing loop, moving the closing bracket below the message box to the end, I get an 'Error: 0x800401E4 - Invalid syntax'.
I'm open to suggestions to make my code better, but ultimately, I really am just looking for help to get ComObjGet working inside my loop - I think I can handle it from there!
This is not the entirety of my code - I've got an autoexecute section, a GUI, and a function (dateparse) that are not included for clarity. Let me know if they would be helpful.
Code: Select all
FilePath := "U:\scripts\Mech Log.xls" ; example path
FullFilePath = "U:\scripts\Mech Log.xls", "U:\scripts\Elec Log.xls", "U:\scripts\Plumb Log.xls"
Loop, Parse, FullFilePath, `, ;I'm trying to enclose all the code below within this loop, updating all instances of FilePath with FullFIlePath.
{
Msgbox, %A_LoopField%
}
oWorkbook := ComObjGet(FilePath)
X := 6
While X < 50
{
If % oWorkbook.Sheets(1).Range("D" . X).Value != "" ;Is there a date in the Submitted column
{
If % oWorkbook.Sheets(1).Range("F" . X).Value = "" ;Is there also a date in the Returned Column
{
SubmittalName := % oWorkbook.Sheets(1).Range("A" . X).Value
DateIn := % oWorkbook.Sheets(1).Range("D" . X).Value
DateIn := DateParse(DateIn)
DaysOld := % DateNow - DateIn
LV_Add("", SubmittalName, FilePath, DaysOld) ;Creates a list of folders that match.
}
}
X := X + 1
}