bonjour,
voila j'aimerais mettre sur mon fichier récapitulatif, sur la variable a(3) un hyperlien du fichier où son récuperer les infos.
Sub essai()
Dim fs, f, f1, fc, s
Dim a(14)
Application.ScreenUpdating = False
specdossier = ActiveWorkbook.Path
fic = ActiveWorkbook.Name
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(specdossier)
Set fc = f.Files
pos = 3
For Each f1 In fc
s = f1.Name
b = Right(s, 4)
If b = "xlsx" And s <> ActiveWorkbook.Name Then
Workbooks.Open Filename:=specdossier & "\" & s
Sheets("feuil1").Activate
a(1) = Cells(2, 2)
a(2) = Cells(1, 1)
a(3) = Cells(5, 3)
a(4) = Cells(5, 4)
a(5) = Cells(5, 6)
a(6) = Cells(9, 3)
a(7) = Cells(9, 4)
a(8) = Cells(9, 6).Value + Cells(9, 7).Value
a(9) = Cells(38, 3)
a(10) = Cells(38, 4)
a(11) = Cells(38, 6)
a(12) = Cells(45, 8)
a(13) = Cells(9, 9)
a(14) = Cells(9, 10)
ActiveWorkbook.Close
Windows(fic).Activate
Sheets("feuil1").Activate
For u = 1 To 14
Cells(pos, u) = a(u)
Next u
pos = pos + 1
End If
Next
End Sub
Merci.
voila j'aimerais mettre sur mon fichier récapitulatif, sur la variable a(3) un hyperlien du fichier où son récuperer les infos.
Sub essai()
Dim fs, f, f1, fc, s
Dim a(14)
Application.ScreenUpdating = False
specdossier = ActiveWorkbook.Path
fic = ActiveWorkbook.Name
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(specdossier)
Set fc = f.Files
pos = 3
For Each f1 In fc
s = f1.Name
b = Right(s, 4)
If b = "xlsx" And s <> ActiveWorkbook.Name Then
Workbooks.Open Filename:=specdossier & "\" & s
Sheets("feuil1").Activate
a(1) = Cells(2, 2)
a(2) = Cells(1, 1)
a(3) = Cells(5, 3)
a(4) = Cells(5, 4)
a(5) = Cells(5, 6)
a(6) = Cells(9, 3)
a(7) = Cells(9, 4)
a(8) = Cells(9, 6).Value + Cells(9, 7).Value
a(9) = Cells(38, 3)
a(10) = Cells(38, 4)
a(11) = Cells(38, 6)
a(12) = Cells(45, 8)
a(13) = Cells(9, 9)
a(14) = Cells(9, 10)
ActiveWorkbook.Close
Windows(fic).Activate
Sheets("feuil1").Activate
For u = 1 To 14
Cells(pos, u) = a(u)
Next u
pos = pos + 1
End If
Next
End Sub
Merci.