Bon et bien voilà, j'avance un peu.
j'ai modifié quelques trucs dans la macro initial. Cependant je rencontre un problème lors de l'opération "cut_off","CopyConfocalite", "CopyResolutionAxiale", "CopyPuissanceéchantillons"
Dim classeurSortie As Workbook
Dim importFolder
Sub ImporterDonnees()
Set classeurSortie = ActiveWorkbook
importFolder = classeurSortie.Worksheets("Home").Range("D17").Value
Dim result As Long
result = MsgBox("Importer les donnees depuis '" & importFolder & "' ?", vbYesNo, "Confirmation import")
' Clic sur Oui
If (result = 6) Then
Call ImportData
End If
End Sub
Sub ImportData()
Dim objFSO, objDossier, objFichier, objResultat
Dim classeurEntree As Workbook
Dim txtMsg As String
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objDossier = objFSO.GetFolder(importFolder)
txtMsg = "Fichiers importes:" & Chr(10)
' Desactivation du rafraichissement (gain de performances)
Application.ScreenUpdating = False
If (objDossier.Files.Count > 0) Then
For Each objFichier In objDossier.Files
If (InStr(1, objFichier.Name, ".xls", 1) > 0) Then
Set classeurEntree = Workbooks.Open(objFichier.Path)
Call ImporterClasseur(classeurEntree)
txtMsg = txtMsg & " - " & objFichier.Name & Chr(10)
End If
Next
Else
txtMsg = txtMsg & "Aucun"
End If
' Reactivation du rafraichissement
Application.ScreenUpdating = True
' Selection du 1er onglet
classeurSortie.Worksheets("Home").Activate
MsgBox txtMsg, vbInformation, "Import termine"
End Sub
Sub ImporterClasseur(classeurEntree As Workbook)
' LinReseauX
Call CopyLinReseauX(classeurEntree, 1)
Call CopyLinReseauX(classeurEntree, 2)
Call CopyLinReseauX(classeurEntree, 3)
Call CopyLinReseauX(classeurEntree, 4)
' Cut-off
Call CopyCutOffs(classeurEntree)
' Exactitude
Call CopyExactitudes(classeurEntree)
' Resolution Axiale
Call CopyResolutionAxiales(classeurEntree)
' Confocalite
Call CopyConfocalites(classeurEntree)
' Puissance échantillon
Call CopyPuissanceéchantillon(classeurEntree)
' Ecriture du log
Set destination = classeurSortie.Worksheets("Home")
Dim rowNumber
rowNumber = destination.Range("C" & destination.Rows.Count).End(xlUp).Row + 1
destination.Range("C" & rowNumber) = Now
destination.Range("D" & rowNumber) = classeurEntree.Path & "" & classeurEntree.Name
classeurEntree.Close
End Sub
Sub CopyLinReseauX(classeurEntree As Workbook, i As Integer)
Dim source As Worksheet, destination As Worksheet
Set source = classeurEntree.Worksheets("LinReseau" & i)
Set destination = classeurSortie.Worksheets("LinReseau" & i)
Dim beginSrc, beginDest, nbRows
beginSrc = 8
beginDest = destination.Range("B" & source.Rows.Count).End(xlUp).Row + 1
nbRows = source.Range("D" & source.Rows.Count).End(xlUp).Row - beginSrc
Dim j As Integer
For j = 0 To nbRows
' (nm)
destination.Range("B" & beginDest + j) = source.Range("D" & beginSrc + j)
' (cm-1)
destination.Range("C" & beginDest + j) = source.Range("E" & beginSrc + j)
' Import date
destination.Range("D" & beginDest + j) = Now
' Source file
destination.Range("E" & beginDest + j) = classeurEntree.Path & "" & classeurEntree.Name
Next j
End Sub
Sub CopyCutOffs(classeurEntree As Workbook)
' 532
Call CopyCutOff(classeurEntree, "Laser1", "A")
' 638
Call CopyCutOff(classeurEntree, "Laser2", "B")
' 785
Call CopyCutOff(classeurEntree, "Laser3", "C")
End Sub
Sub CopyCutOff(classeurEntree As Workbook, laser As String, destinationColumn As String)
Dim rowNumber As Range
Dim source As Worksheet, destination As Worksheet
Set source = classeurEntree.Worksheets("Cut_Off")
Set destination = classeurSortie.Worksheets("Cut_Off")
' Numero de ligne du lambda correspondant
Set rowNumber = source.Range("B:B").Cells.Find(What:=laser)
destination.Range(destinationColumn & destination.Range(destinationColumn & source.Rows.Count).End(xlUp).Row + 1) = source.Range("C" & rowNumber.Row).Value
' Import date
destination.Range("D" & destination.Range(destinationColumn & source.Rows.Count).End(xlUp).Row) = Now
' Source file
destination.Range("E" & destination.Range(destinationColumn & source.Rows.Count).End(xlUp).Row) = classeurEntree.Path & "" & classeurEntree.Name
End Sub
Sub CopyResolutionAxiale(classeurEntree As Workbook, laser As String, destinationColumn As String)
Dim rowNumber As Range
Dim source As Worksheet, destination As Worksheet
Set source = classeurEntree.Worksheets("Confocalite")
Set destination = classeurSortie.Worksheets("Resolution_Axiale")
' Numero de ligne du lambda correspondant
Set rowNumber = source.Range("B:B").Cells.Find(What:=laser)
destination.Range(destinationColumn & destination.Range(destinationColumn & source.Rows.Count).End(xlUp).Row + 1) = source.Range("E" & rowNumber.Row + 5).Value
' Import date
destination.Range("D" & destination.Range(destinationColumn & source.Rows.Count).End(xlUp).Row) = Now
' Source file
destination.Range("E" & destination.Range(destinationColumn & source.Rows.Count).End(xlUp).Row) = classeurEntree.Path & "" & classeurEntree.Name
End Sub
Sub CopyConfocalite(classeurEntree As Workbook, laser As String, destinationColumn As String)
Dim rowNumber As Range
Dim source As Worksheet, destination As Worksheet
Set source = classeurEntree.Worksheets("Confocalite")
Set destination = classeurSortie.Worksheets("confocalite")
' Numero de ligne du lambda correspondant
Set rowNumber = source.Range("B:B").Cells.Find(What:=laser)
destination.Range(destinationColumn & destination.Range(destinationColumn & source.Rows.Count).End(xlUp).Row + 1) = source.Range("E" & rowNumber.Row + 3).Value
' Import date
destination.Range("D" & destination.Range(destinationColumn & source.Rows.Count).End(xlUp).Row) = Now
' Source file
destination.Range("E" & destination.Range(destinationColumn & source.Rows.Count).End(xlUp).Row) = classeurEntree.Path & "" & classeurEntree.Name
End Sub
Sub CopyExactitudes(classeurEntree As Workbook)
' 600T
Call CopyExactitude(classeurEntree, "A", "I28")
' 1200T
Call CopyExactitude(classeurEntree, "B", "I48")
' 1800T
Call CopyExactitude(classeurEntree, "C", "I68")
' 2400T
Call CopyExactitude(classeurEntree, "D", "I88")
End Sub
Sub CopyExactitude(classeurEntree As Workbook, destinationColumn As String, srcRange As String)
Dim source As Worksheet, destination As Worksheet
Set source = classeurEntree.Worksheets("Exactitude")
Set destination = classeurSortie.Worksheets("Exactitude")
destination.Range(destinationColumn & destination.Range(destinationColumn & destination.Rows.Count).End(xlUp).Row + 1) = source.Range(srcRange).Value
' Import date
destination.Range("E" & destination.Range(destinationColumn & source.Rows.Count).End(xlUp).Row) = Now
' Source file
destination.Range("F" & destination.Range(destinationColumn & source.Rows.Count).End(xlUp).Row) = classeurEntree.Path & "" & classeurEntree.Name
End Sub
Sub CopyPuissanceéchantillons(classeurEntree As Workbook, laser As String, destinationColumn As String)
Dim rowNumber As Range
Dim source As Worksheet, destination As Worksheet
Set source = classeurEntree.Worksheets("data_objectifs")
Set destination = classeurSortie.Worksheets("Puissance échantillon")
' Numero de ligne du lambda correspondant
Set rowNumber = source.Range("I:I").Cells.Find(What:=laser)
destination.Range(destinationColumn & destination.Range(destinationColumn & source.Rows.Count).End(xlUp).Row + 1) = source.Range("I" & rowNumber.Row + 12).Value
destination.Range(destinationColumn & destination.Range(destinationColumn & source.Rows.Count).End(xlUp).Row + 1) = source.Range("I" & rowNumber.Row + 17).Value
destination.Range(destinationColumn & destination.Range(destinationColumn & source.Rows.Count).End(xlUp).Row + 1) = source.Range("I" & rowNumber.Row + 22).Value
' Import date
destination.Range("D" & destination.Range(destinationColumn & source.Rows.Count).End(xlUp).Row) = Now
' Source file
destination.Range("E" & destination.Range(destinationColumn & source.Rows.Count).End(xlUp).Row) = classeurEntree.Path & "" & classeurEntree.Name
End Sub
Sub CopyResolutionAxiales(classeurEntree As Workbook)
'785
Call CopyResolutionAxiale(classeurEntree, "Laser1", "C")
' 638
Call CopyResolutionAxiale(classeurEntree, "Laser2", "B")
' 532
Call CopyResolutionAxiale(classeurEntree, "Laser3", "A")
End Sub
Sub CopyConfocalites(classeurEntree As Workbook)
' 785
Call CopyConfocalite(classeurEntree, "Laser1", "C")
' 638
Call CopyConfocalite(classeurEntree, "Laser2", "B")
' 532
Call CopyConfocalite(classeurEntree, "Laser3", "A")
End Sub
Sub CopyPuissanceéchantillon(classeurEntree As Workbook)
' 785
Call CopyPuissanceéchantillons(classeurEntree, "Laser1", "C")
' 638
Call CopyPuissanceéchantillons(classeurEntree, "Laser2", "B")
' 532
Call CopyPuissanceéchantillons(classeurEntree, "Laser3", "A")
End Sub
La copie de ces différents éléments s'effectue sans encombre, sauf pour la date et la source. Il me copie la date et la source dans l'entête du tableau (en ligne 1 et non en ligne 2). Je n'arrive pas a comprendre pourquoi.