problème filesearch excel 2007
Bonjour j'ai une macro qui fonctionne sous excel 2005 et pas 2007. J'ai lu que filesearch n'était plus implémenté sous 2007, mais quelqu'un pourrai t'il adapté mon code pour pouvoir utiliser cette macro dans les 2 versions.
merci
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
| Sub lance()
Dim i As Long
For i = 1 To Selection.Count
ligne = Selection.Cells(i, 1).Row
nom = "prix_" + ActiveCell.Text + ".xls"
With Application.FileSearch
.LookIn = ActiveWorkbook.Path
.Filename = nom
.MatchTextExactly = True
If .Execute() <> 0 Then
If .FoundFiles(1) = ActiveWorkbook.Path + "\" + nom Then
form = "='" + ActiveWorkbook.Path + "\[" + nom + "]prix_" + ActiveCell.Text + "'!$K$33"
Workbooks.Open Filename:=ActiveWorkbook.Path + "\" + nom
End If
Else
Workbooks.Open Filename:=ActiveWorkbook.Path + "\prix_type.xls"
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path + "\" + nom
Mid(nom, Len(nom) - 3, 4) = " "
ActiveSheet.Name = RTrim(nom)
ActiveSheet.Cells(4, 2).Formula = Right(nom, Len(nom) - 5)
ActiveWorkbook.Save
End If
End With
Next
End Sub |
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
| Sub imprime()
'
For i = 1 To Selection.Count
ligne = Selection.Cells(i, 1).Row
nom = "prix_" + ActiveCell.Text + ".xls"
With Application.FileSearch
.LookIn = ActiveWorkbook.Path
.Filename = nom
.MatchTextExactly = True
If .Execute() = 1 Then
If .FoundFiles(1) = ActiveWorkbook.Path + "\" + nom Then
form = "='" + ActiveWorkbook.Path + "\[" + nom + "]prix_" + ActiveCell.Text + "'!$K$33"
Workbooks.Open Filename:=ActiveWorkbook.Path + "\" + nom
ActiveWorkbook.ActiveSheet.PrintOut
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
End If
End If
End With
Next
End Sub |