Filter Fails to Work - Incorrect Format?

[coates]

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

[Bob Phillips]

Maybe trys



Date1 = Format(Worksheets("5 Week Outlook").Cells(2, 13).Value, "ddmmmyy")
Date2 = Format(Worksheets("5 Week Outlook").Cells(2, 14).Value, "ddmmmyy")

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

[coates]

Maybe trys



Date1 = Format(Worksheets("5 Week Outlook").Cells(2, 13).Value, "ddmmmyy")
Date2 = Format(Worksheets("5 Week Outlook").Cells(2, 14).Value, "ddmmmyy")

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


No sorry, it didnt work. When i step through my code it appears to display the date1 and date2 in the correct format, but it still does not work in my filter. The only little difference I can think of is that the above format displays the dates as "09Aug10" where as when I simply type the date into the filter line, I type it as '09Aug10'.

Does the single ' imply it is a string?

[Bob Phillips]

Without seeing the file, which I accept is probably not possible, it is difficult to see the problem.

[coates]

Yeah unfortunately, I cannot release the whole file. I have a feeling my problem may lie with the interaction between Excel and my scheduling software program, so I think I will approach the issue with the support service.
Thankyou very much for your time anyway.