coates
08-05-2010, 08:17 PM
Hi,
So i have quite a large script which is extracting data from a scheduling software program (OPP) 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.
I have also tried
BSDATE >=" & Date1 & "AND BSDATE <=" & Date2 & ")
to no avail.
I have also tried changing the format of the two excel cells to text which also didnt work.
If I step through my code, both Date1 and Date2 depict the correct date, i.e "05Aug10". This makes me believe the problem lies in the line starting with 'OPPAct.SetFilterTo' as after this the Act.Total is incorrect.
Any help would be greatly appreciated.
Thanks
So i have quite a large script which is extracting data from a scheduling software program (OPP) 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.
I have also tried
BSDATE >=" & Date1 & "AND BSDATE <=" & Date2 & ")
to no avail.
I have also tried changing the format of the two excel cells to text which also didnt work.
If I step through my code, both Date1 and Date2 depict the correct date, i.e "05Aug10". This makes me believe the problem lies in the line starting with 'OPPAct.SetFilterTo' as after this the Act.Total is incorrect.
Any help would be greatly appreciated.
Thanks