Macro excel fusionner plusieurs fichiers à partir du nom d onglet

[sisi75]

Bonjour,

Nouvelle sur le site que je consulte régulièrement, me voici confrontée à un problème qui pourra vous sembler simple mais qui me rend folle depuis quelques jours.
J'ai effectué des recherches sur le Web mais je ne trouve pas le bout de macro qui pourrait correspondre à mon désire.

Pourtant la question est posée régulièrement mais il ne semble pas qu'elle soit formulée comme ce que je souhaite faire.

Ma question est donc :

Comment puis-je créer un fichier synthèse de plusieurs fichiers XLS à partir d'un nom d'onglet.

Je m'explique.

Je possède plusieurs fichiers Excel sauvegardés dans un même répertoire, contenant plusieurs onglets dont certains sont même cachés. Chaque fichier à la même structure et possède tous un onglet BASE GLOBALE.
Je désire avoir une macro qui irait récupérer les données se trouvant dans l'onglet BASE GLOBALE de tous mes fichiers XLS pour les intégrer dans un nouveau classeur les uns à la suite des autres.
La structure de mon fichier Synthèse devra conserver la structure de mon onglet Base, il est donc inutile d'importer les entêtes qui sont sur les 2 premières lignes. Si possible faire l'importation en laissant les fichiers sources fermés.

Please Help me, je n'en vois pas le bout.

Merci à tous et bonne journée.

 

[sisi75]

Re : Macro excel fusionner plusieurs fichiers à partir du nom d onglet

Re-Bonjour,

Je poursuis mes recherches en espérant arriver au résultat attendu et je suis tombée sur un fichier réalisé par Job75 qui se rapproche de ce que je désire mais que je ne parviens pas à adapter.

https://www.excel-downloads.com/thr...-feuilles-dans-une-seule-indirect-etc.174426/

Pouvoir récupérer les onglets Base globale de différents fichiers sans les ouvrir et obtenir la synthèse totale dans un nouveau fichier comme dans la matrice Job75.
Sinon trouver le moyen de copier tous mes onglets Base globale dans un fichier unique et exécuter le code de Job75 pour avoir un onglet consolidation. Cela me parait plus complexe car les onglets ont le même nom il faudrait donc les renommer pour pouvoir les intégrer dans le nouveau fichier !

Merci pour votre aide.

 

[job75]

Re : Macro excel fusionner plusieurs fichiers à partir du nom d onglet

Bonsoir sisi75,

Mettez tous les fichiers, synthèse et fichiers sources, dans un même dossier (répertoire).

Copiez le code suivant dans le VBA du classeur de synthèse, en adaptant le CodeName de la feuille de restitution :

Sub CopierFichiers()
Dim t, chemin$, W As Worksheet, feuil$, ligdeb&, lig&
Dim fichier$, F As Worksheet, dercel As Range, h&
t = Timer 'mesure facultative
'---initialisation---
chemin = ThisWorkbook.Path & "\"
Set W = Feuil1 'CodeName de la feuille de restitution
feuil = "BASE GLOBALE" 'nom à adapter
ligdeb = 3 '1ère ligne à copier
lig = ligdeb '1ère ligne restituée
Application.ScreenUpdating = False
Application.DisplayAlerts = False 'si un fichier est déjà ouvert
W.Rows(ligdeb & ":" & W.Rows.Count).Delete 'RAZ
'---copie des fichiers---
fichier = Dir(chemin & "*.xls*") '1er fichier du dossier
While fichier <> ""
  If fichier <> ThisWorkbook.Name Then
    Set F = Workbooks.Open(chemin & fichier).Sheets(feuil) 'ouverture du fichier
    Set dercel = F.Cells.Find("*", , xlValues, , xlByRows, xlPrevious)
    h = 0
    If Not dercel Is Nothing Then
      If dercel.Row >= ligdeb Then
        h = dercel.Row - ligdeb + 1
        F.Rows(ligdeb).Resize(h).Copy W.Cells(lig, 1)
      End If
    End If
    F.Parent.Close False 'fermeture du fichier
    lig = lig + h '+ 1 'si l'on veut espacer  par une ligne vide
  End If
  fichier = Dir 'fichier suivant du dossier
Wend
W.Activate
Application.ScreenUpdating = True
MsgBox "Durée " & Format(Timer - t, "0.00 \s") 'mesure facultative
End Sub

Le plus simple est en effet d'ouvrir les fichiers, de les copier et de les refermer.

Bonne fin de soirée et A+

 

[job75]

Re : Macro excel fusionner plusieurs fichiers à partir du nom d onglet

RE,

S'il y a des formules et qu'on ne veut copier que les valeurs :

Sub CopierFichiers()
Dim t, chemin$, W As Worksheet, feuil$, ligdeb&, lig&
Dim fichier$, F As Worksheet, dercel As Range, h&
t = Timer 'mesure facultative
'---initialisation---
chemin = ThisWorkbook.Path & "\"
Set W = Feuil1 'CodeName de la feuille de restitution
feuil = "BASE GLOBALE" 'nom à adapter
ligdeb = 3 '1ère ligne à copier
lig = ligdeb '1ère ligne restituée
Application.ScreenUpdating = False
Application.DisplayAlerts = False 'si un fichier est déjà ouvert
W.Rows(ligdeb & ":" & W.Rows.Count).Delete 'RAZ
'---copie des fichiers---
fichier = Dir(chemin & "*.xls*") '1er fichier du dossier
While fichier <> ""
  If fichier <> ThisWorkbook.Name Then
    Set F = Workbooks.Open(chemin & fichier).Sheets(feuil) 'ouverture du fichier
    Set dercel = F.Cells.Find("*", , xlValues, , xlByRows, xlPrevious)
    h = 0
    If Not dercel Is Nothing Then
      If dercel.Row >= ligdeb Then
        h = dercel.Row - ligdeb + 1
        F.Rows(ligdeb).Resize(h).Copy
        W.Cells(lig, 1).PasteSpecial xlPasteValues
      End If
    End If
    F.Parent.Close False 'fermeture du fichier
    lig = lig + h '+ 1 'si l'on veut espacer d'une ligne
  End If
  fichier = Dir 'fichier suivant du dossier
Wend
Application.Goto W.[A1], True
Application.ScreenUpdating = True
MsgBox "Durée " & Format(Timer - t, "0.00 \s") 'mesure facultative
End Sub

Bonne nuit et A+

 

[job75]

Re : Macro excel fusionner plusieurs fichiers à partir du nom d onglet

Bonjour sisi75, le forum,

Dans la feuille de restitution on peut ajouter une colonne où sera porté le nom de chaque fichier :

Sub CopierFichiers()
Dim t, chemin$, W As Worksheet, feuil$, ligdeb&, lig&
Dim dercol%, fichier$, F As Worksheet, dercel As Range, h&
t = Timer 'mesure facultative
'---initialisation---
chemin = ThisWorkbook.Path & "\"
Set W = Feuil1 'CodeName de la feuille de restitution
feuil = "BASE GLOBALE" 'nom à adapter
ligdeb = 3 '1ère ligne à copier
lig = ligdeb '1ère ligne restituée
Application.ScreenUpdating = False
Application.DisplayAlerts = False 'si un fichier est déjà ouvert
W.Rows(ligdeb & ":" & W.Rows.Count).Delete 'RAZ
dercol = W.UsedRange.Columns.Count + 1 'pour les noms des fichiers
'---copie des fichiers---
fichier = Dir(chemin & "*.xls*") '1er fichier du dossier
While fichier <> ""
  If fichier <> ThisWorkbook.Name Then
    Set F = Workbooks.Open(chemin & fichier).Sheets(feuil) 'ouverture du fichier
    Set dercel = F.Cells.Find("*", , xlValues, , xlByRows, xlPrevious)
    h = 1
    If Not dercel Is Nothing Then
      If dercel.Row >= ligdeb Then
        h = dercel.Row - ligdeb + 1
        F.Rows(ligdeb).Resize(h).Copy
        W.Cells(lig, 1).PasteSpecial xlPasteValues 'valeurs
        'W.Cells(lig, 1).PasteSpecial xlPasteFormats 'formats
      End If
    End If
    W.Cells(lig, dercol).Resize(h) = fichier
    F.Parent.Close False 'fermeture du fichier
    lig = lig + h
  End If
  fichier = Dir 'fichier suivant du dossier
Wend
Application.Goto W.[A1], True
Application.ScreenUpdating = True
MsgBox "Durée " & Format(Timer - t, "0.00 \s") 'mesure facultative
End Sub

Bonne journée et A+

 

[sisi75]

Re : Macro excel fusionner plusieurs fichiers à partir du nom d onglet

Bonjour le forum, Bonjour Job75,

Tu es mon sauveur.
Merci beaucoup. Cela correspond tout à fait à ce que je souhaitais obtenir comme résultat.

Mille mercis à vous tous pour l'aide que vous nous apportez et le temps que vous nous consacrez.

PS : J'ai testé les 3 propositions que tu as faite et j'ai choisi le second code, car l'idée de reprendre le nom de la feuille dans une colonne supplémentaire aurait pu être intéressante mais lorsque je teste cette configuration le nom de la feuille apparait en colonne U or dans ce fichier j'ai des informations jusqu'à la colonne X.

Bonne journée.

Sisi75

Bonne journée.

 

[job75]

Re : Macro excel fusionner plusieurs fichiers à partir du nom d onglet

Re,

l'idée de reprendre le nom de la feuille dans une colonne supplémentaire aurait pu être intéressante mais lorsque je teste cette configuration le nom de la feuille apparait en colonne U or dans ce fichier j'ai des informations jusqu'à la colonne X.

Bah faut pas que ça vous bloque pour si peu, vous n'avez qu'à définir la variable dercol par dercol = 25

A+

 

[sisi75]

Re : Macro excel fusionner plusieurs fichiers à partir du nom d onglet

Merci beaucoup Job 75, tout fonctionne parfaitement.

Puis-je profiter de ce post pour vous posez une question qui peut paraitre étrange ?

Est-il possible de faire une formule qui dirait :

- Si montant de la cellule P3 est positif alors l'additionner avec le résultat de la cellule Q3 (qui contient déjà une formule addition simple)
- Si montant de la cellule P3 est négatif alors le soustraire avec le résultat de la cellule Q3

Exemple :
En P3 j'ai -20
En Q3 j'ai V3+W3 soit 10+15
Je voudrais donc obtenir en Q3 10+15-20

Si P3 est positif et égal à 20
Alors en Q3 je souhaite avoir 10+15+20

Je ne sais pas si c'est faisable mais à tout hasard.

Excellente journée à tous.

 

[job75]

Re : Macro excel fusionner plusieurs fichiers à partir du nom d onglet

Re,

Vous pouvez par exemple entrer en Q3 la nouvelle formule =V3+W3+P3

A+

 

[sisi75]

Re : Macro excel fusionner plusieurs fichiers à partir du nom d onglet

Re,

C'était si bête que ça Et moi qui cherchait un truc tordu.

Merci beaucoup et très bonne journée à toi ainsi qu'à tous les forumeurs.