Hi,
So i have quite a large script which is extracting data from a scheduling software program into excel. I than run a filter on the activities, to show only the activities with are occuring in the next 5 weeks.
I have been able to get the script to run perfectly when typing in the start date and the date 5 weeks from then into the script. However, as I have to run this macro often, I want to be able to change the two respective dates in a excel cell, than run the macro.
An extract of my script is:
Date1 = Worksheets("5 Week Outlook").Cells(2, 13).Value
Date2 = Worksheets("5 Week Outlook").Cells(2, 14).Value
Set OPProject = OPP.Projects("CURRENT ISSUE")
showdialog = True
OPProject.TimeAnalyze showdialog
Set OPPView = OPProject.views
OPP.ApplyFilter ("Five_Week_Look_Ahead")
Set OPPAct = OPProject.activities
OPPAct.SetFilterTo "Five_Week_Look_Ahead", "(ASDATE NOT_EMPTY and AFDATE IS_EMPTY) or (BSDATE >= 'Date1' AND BSDATE <= 'Date2') or (ESDATE >= 'Date1' AND ESDATE <= 'Date2')"
ActTotal = OPPAct.Count
Rather than using Date1 and Date2, if I simply type in my filter BSDATE >= '05Aug10', the filter runs fine and the 'ActTotal' is around 400, which is what I want.
However, if i use the Date1 and Date2, which is taking the dates from the excel cells, my 'ActTotal' increases to 2407, which is all of the activities. therefore it is not filtering anything.
Any help would be greatly appreciated.
Thanks
So i have quite a large script which is extracting data from a scheduling software program into excel. I than run a filter on the activities, to show only the activities with are occuring in the next 5 weeks.
I have been able to get the script to run perfectly when typing in the start date and the date 5 weeks from then into the script. However, as I have to run this macro often, I want to be able to change the two respective dates in a excel cell, than run the macro.
An extract of my script is:
Date1 = Worksheets("5 Week Outlook").Cells(2, 13).Value
Date2 = Worksheets("5 Week Outlook").Cells(2, 14).Value
Set OPProject = OPP.Projects("CURRENT ISSUE")
showdialog = True
OPProject.TimeAnalyze showdialog
Set OPPView = OPProject.views
OPP.ApplyFilter ("Five_Week_Look_Ahead")
Set OPPAct = OPProject.activities
OPPAct.SetFilterTo "Five_Week_Look_Ahead", "(ASDATE NOT_EMPTY and AFDATE IS_EMPTY) or (BSDATE >= 'Date1' AND BSDATE <= 'Date2') or (ESDATE >= 'Date1' AND ESDATE <= 'Date2')"
ActTotal = OPPAct.Count
Rather than using Date1 and Date2, if I simply type in my filter BSDATE >= '05Aug10', the filter runs fine and the 'ActTotal' is around 400, which is what I want.
However, if i use the Date1 and Date2, which is taking the dates from the excel cells, my 'ActTotal' increases to 2407, which is all of the activities. therefore it is not filtering anything.
Any help would be greatly appreciated.
Thanks