This is almost a reminder for me. When I need it I alsways forget the name of the utility 
So here it is… BLATavailable at http://sourceforge.net/project/showfiles.php?group_id=81910
If the url is not working anymore, just need to google blat and you’ll find thousand of link for it.
Did I say it? It’s free.
Ciao
You need to use this script in a button control.
You also need to register the Microsoft Scripting runTime dll from Tools -> Reference in your VBA Editor.
Code:
| Private Sub CommandButton1_Click() | |
| ‘Partie Filtre | |
| Selection.AutoFilter Field:=4, Criteria1:= »CDCAM » | |
| Selection.AutoFilter Field:=15, Criteria1:= »<>DEL », Operator:=xlAnd, _ | |
| Criteria2:= »<>del » | |
| ‘Range(« A1:N1000″).Select | |
| ‘Selection.Copy | |
| ‘Partie export au fichier | |
| ‘Dim fso | |
|
|
‘Set fso = CreateObject(« Scripting.FileSystemObject ») |
|
|
Dim fso As New Scripting.FileSystemObject |
|
|
Dim ts ‘TextStream |
|
|
Set ts = fso.CreateTextFile(« DriveLetter:Full Pathoutput.txt », True, True) |
|
|
Dim col As Integer |
|
|
col = 14 ‘La denriere colonne a prendre |
|
|
Dim row As Integer |
|
|
row = 500 ‘La dernier rangée a prendre. Pour sauver du temps je ne fait pas de check sur la derniere rangee occuppé. |
|
|
Dim i As Integer |
|
|
Dim j As Integer |
|
|
‘ Delim = IIf(.obCharacter, .tbDelimiter, Chr(9)) |
|
|
For i = 1 To row |
|
|
For j = 1 To col |
|
|
If j = col Then |
|
|
ts.Write CStr(Cells(i, j)) |
|
|
Else |
|
|
ts.Write CStr(Cells(i, j)) & Chr(9) ‘TAB |
|
|
End If |
|
|
Next |
|
|
ts.Write (vbCrLf) |
|
|
Next |
|
|
ts.Close |
|
|
End Sub |
Ciao.
Again, there is a problem and many solutions available.
The main issue here is that you cannot insert a formula into the Filter Section of a View from a List. You can’t use standard functions like [Today] etc.
As if it was a reminder, I won’t describe all the steps you need to create a List, DocLib and views or even the fields.
When you need to work with a dynamic date in a Filter, the easiest solution is almost always to add dynamic fields to the List. Then, you can use those fileds values to create Filters without formulas.
A commun example is for a Document Library, let’s say you have a views that contains couple hundreds documents. You want to implement some kind of Archives system throughout your Views.
You add a field of the type “Calculated“, you give it a name that make sense like “ArchiveDate ».
You want to archive documents older than 6 months, so you fill in the Formula: [Today]+182, Data type returned: Date and Time, Format: Date Only.
Uncheck “Add to default view ».
Now, if you go back to “View Properties” on a document from that DocLib, you should see the New Date value.
From there, you only need to play with your Views. Filter to Display Only Documents Where “A Date Field (Like Last Updated or Modified)” Is Greather Than or Equal to [Today].
On the other side, You create a View to display archived document using the same View except you put “Is Less Than” in the condition.
Of course, you can create more dynamic fields to use more complex Filters.
Ciao Ciao.