générer nouveaux classeurs à partir de feuilles

V

Virginie

Guest
Bonjour les spécialistes,

Voilà quelques temps que je n'étais pas passé vous voir, et je constate que votre forum est toujours des plus actifs.
Je voudrai écrire une macro qui génére à partir d'un classeur X autant de nouveaux classeurs que de feuilles contenues dans ce classeur X. (chaque nouveau classeur contiendrait une feuille du classeur X) et laisser à l'utilisateur choisir l'emplacement où il désire stocker ces nouveaux classeurs.

j'ai essayé de composer avec ces bouts de code que j'ai trouvé sur ce forum, mais ils 'bug' à Sheets(sh).Select. Et je ne parviens pas à trouver comment laisser le choix du repertoire de destination à l'utilisateur, dans ce code le repertoire est en 'dur'.


Sub séparation()

' Macro enregistrée le 18/07/2005 par CB
'
Dim sh As Worksheet
Dim lenom As String
Dim lenom2 As String

For Each sh In Sheets
Sheets(sh).Select
Sheets(sh).Copy
lenom = sh.Name
lenom2 = lenom & '.xls'

ChDir 'C:'
ActiveWorkbook.SaveAs Filename:= _
'C:lenom2', FileFormat:= _
xlNormal, Password:='', WriteResPassword:='', ReadOnlyRecommended:=False _
, CreateBackup:=False

Next sh

End Sub

Merci pour votre patience, je vous embrasse par avance pour l'aide que vous pourrez m'apporter.

Virginie
 

Yan L

XLDnaute Nouveau
Virginie,

Désolé, il est un peu tard pour moi pour te répondre entièrement.
Mais pour ce qui est du choix du répertoire de sauvegarde, jette un oeil sur la fonction

Application.GetSaveAsFilename()

Elle te permet de récupérer un chemin depuis une boîte de dialogue!
Bon tests et à bientôt!

Yan
 

Dan

XLDnaute Barbatruc
Bonsoir,

Avec Hervé, on a regardé ton code.

remplace le tien par celui-ci :

Code:
Sub séparation()
Dim sh As Worksheet
Dim lenom As String
Dim lenom2 As String
Dim nomfichier As Workbook

For Each sh In Sheets
sh.Select
sh.Copy
lenom = sh.Name
lenom2 = lenom & '.xls'

ChDir 'C:'
ActiveWorkbook.SaveAs Filename:= _
'C:' & lenom2, FileFormat:= _
xlNormal, Password:='', WriteResPassword:='', ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.Close
Next sh
End Sub

Après C: mets aussi un backslash.

;)

Message édité par: Dan, à: 04/10/2005 23:19
 

KIM

XLDnaute Accro
Bonjour Dan, Hervé, Virginie et le forum,

J'ai testé ta macro pour la recopie des feuilles dans des nouveaux classeurs. Je bute sur un problème de liaison et de formules car quand j'ouvre les nouveaux classeurs, excel me dit qu'ils comportent des liaisons et me demande si je souhaite les mettre à jour. Comment faire pour générer ces nouveaux classeurs sans formules, seulement les valeurs, et sans liaison en gardant toujours le meme format de présentation?
Merci d'avance
Amicalement
KIM
 

Dan

XLDnaute Barbatruc
Bonjour,

En attendant Virgine -> message pour Kim : le mieux est d'utiliser la fonction copier - collage spécial - valeurs après avoir créer ton nouveau classeur. Là évidemment cela peut devenir compliqué suivant le structure du nouveau fichier. ce qu'il faut connaitre c'est :

- le nbre de feuilles contenues dans ton classeur ?
- as-tu attribué des noms à des formules ?
- les liaisons sont-elles relatives à une seule feuille ou non

Pense aussi aux classeurs modèles (avec extension .xlt) qui permettent de contourner pas mal de choses et de partir chaque fois sur un fichier nouveau.

;)
 

KIM

XLDnaute Accro
Re,
Merci Dan,
Pour repondre à tes questions,
- le nombre des feuilles du classeur origine est aléatoire, c-à-d à chaque mise-à-jour de la feuille Source, il peut y avoir plus de feuilles que la dernière maj.
- Je n'ai pas attribué des noms à mes formules qui sont en général de SOMMPROD.
- Les liaisons sont relatives à 3 feuilles.
En effet c'est plus compliqué meme manuellement et je recherche une solution par macro pour avoir une copie exacte de la feuille sans formules ni liaisons.
Merci d'avance
 
V

Virginie

Guest
Bonjour Dan et Yan, salut Kim

Super les garçons, ça marche très bien, par contre je ne suis pas parvenue à laisser la main à l'utilisateur pour le choix du repertoire de destination.
Kim, je suis intéressée par la réponse qui te sera rendue ceci me concerne également.

Dans tous les cas une grosse bise à Dan et Yan pour leur contribution.

à bientôt j'espère

Virginie
 

Dan

XLDnaute Barbatruc
Bonjour,

Virginie:
Ce que tu veux c'est avoir la possibilité du répertoire, cela revient à afficher la 'enregistrer sous'.
Dans ce cas, la macro évolue comme suit :

Supprime l'instruction 'ActiveWorkbook.SaveAs Filename.....create back-up=False' et remplace la par celle ci-après :

Code:
Application.GetSaveAsFilename

Kim:
Ce sont les 3 feuilles seules que tu veux placer dans un autre classeur ou toutes les feuilles du classeur source ?
Note bien que la macro restera dans le classeur source. Est-ce correct pour toi ?

;)
 

KIM

XLDnaute Accro
Re-Bonjour,
Dan:
C'est comme la demande initiale de Virginie. Un classeur initial avec 30 feuilles minimum ( nombre de feuilles non fixe). J'ai testé ta macro pour générer des classeurs à partir des feuilles de ce classeur initial.
bien sûr la macro reste dans le classeur initial.
ça marche, mais
Le problème c'est que les classeurs générés possèdent une liaison vers le classeur initial et les formules sont conservées dans ces memes classeurs. Je recherche le moyen de générer ces classeurs sans liaison, sans formules et sans macros (propres pour les distribuer) seulement avec les valeurs des formules et en conservant la meme presentation et mise en forme que dans le classeur initial.
Pour etre plus precis, la génération des classeurs concerne toutes les feuilles du classeur initial sauf 4 feuilles nommées MENU, REF 1, REF 2, et DATA
Merci d'avance pour l'aide
Amicalement
KIM
 
V

Virginie

Guest
Bonjour Dan,

merci mille fois pour ton aide, si je n'ai qu'une feuille à exporter ça marche super, mais quand j'en ai 30 je voudrai que 'le chemin' soit retenu une fois pour toutes, en clair, selectionner une seule fois le chemin et que toutes les feuilles se traduisent en autant de classeurs dans le chemin une seule fois indiqué.

Encore merci pour ta patience.

Virginie
 
V

Virginie

Guest
A force de trifouiller, j'ai trouvé la solution suivante :

Déclarer

Dim Thepath As String

et porter les modifications qui suivent :

Thepath = ThisWorkbook.Path
ChDir Thepath
ActiveWorkbook.SaveAs Filename:= _
'C:' & lenom2, FileFormat:= _
xlNormal, Password:='', WriteResPassword:='', ReadOnlyRecommended:=False _
, CreateBackup:=False


Les classeurs sont générés dans le repertoire du classeur actif.

Merci pour tout la panthère rose !

Virginie
 

KIM

XLDnaute Accro
Bonjour Virginie, Dan et le forum,
Virginie,
Pour sauvegarder plusieurs feuilles dans le repertoire courant,
j'ai créé un repertoire dans le repertoire courant appelé 'LFEUIL' et toutes les feuilles sont sauvegardées dans ce repertoire. Cela permet une meilleur lisibilité des feuilles créées surtout si dans le repertoire courant il y a plusieurs fichiers excel ou autres.

Ci-dessous la macro modifiée et testée

Public MyPath

Sub séparation()
Dim sh As Worksheet
Dim lenom As String
Dim lenom2 As String
Dim nomfichier As Workbook
MyPath = ActiveWorkbook.Path

For Each sh In Sheets
sh.Select
sh.Copy
lenom = sh.Name
lenom2 = lenom & '.xls'

ActiveWorkbook.SaveAs MyPath & Application.PathSeparator & 'LFEUIL' & Application.PathSeparator & lenom2

ActiveWorkbook.Close

Next sh
End Sub


Il reste le probleme de générer des nouveaux classeurs propres, sans liaison, sans formules
Merci d'avance Dan ou quelqu'un d'autres sur le forum .

Amicalement
KIM
 

Dan

XLDnaute Barbatruc
Re,


encore une petite précision,

Les feuilles avec les formules restent dans le classeur original ou non ?

Les feuilles sont souvée toutes dans le même classeur ou une par classeur ?

En quelque sorte, je présume que les feuilles sont justes copiées sans les fomules vers un classeur archives ou plusieurs à créer.

Les feuilles DATA, MENU, REF1 REF2 ne sont jamais archivées. elles restent dans le classeur original.

Merci

;)
 

KIM

XLDnaute Accro
Re,

Merci d'avance Dan
En effet:
Les feuilles avec les formules restent dans le classeur original ou non ?
OUI, le classeur original reste identique, rien ne change


Les feuilles sont souvée toutes dans le même classeur ou une par classeur ?

Je souhaite avoir les 2 possibilités très utile l'une et l'autre pour moi
1- Les feuilles sont sauvées une à une selon le nom de chaque feuille
2- les feuilles sont sauvées toutes dans un meme classeur archive qui possèdera le nom du classeur initial avec la date (nomclasseur_anmmjj.xls)


En quelque sorte, je présume que les feuilles sont justes copiées sans les fomules vers un classeur archives ou plusieurs à créer.
OUI, sans les formules, sans les macros seulement les données

Les feuilles DATA, MENU, REF1 REF2 ne sont jamais archivées. elles restent dans le classeur original.
OUI

Merci beaucoup
Amicalement
KIM
 

Dan

XLDnaute Barbatruc
Re Kim,

Merci de tes infos toujours aussi précises.

Je te propose deux macros.

1. Macro de sauvegarde feuilles
Code:
Sub Archive()
'Macro archivage par DAN pour KIM sur XLD - 06/10/05
'Post [B]Lien supprimé[/B]
Dim W As Worksheet
Application.ScreenUpdating = False
ThisWorkbook.Sheets.Copy 'création d'un nouveau classeur
Application.DisplayAlerts = False
On Error Resume Next
For Each W In Sheets
    W.Activate
    If W.Name = 'DATA' Or W.Name = 'MENU' Or W.Name = 'REF1' Or W.Name = 'REF2' Then
    W.Delete 'supprimer les feuilles
    Else: formatarchive 'appel la macro formatarchive
    End If
Next W
Application.ScreenUpdating = True
End Sub
2. Macro pour éliminer les formules
Code:
Sub formatarchive()
'Macro archivage par DAN pour KIM sur XLD - 06/10/05
'Post [B]Lien supprimé[/B]
Dim W As Worksheet
On Error Resume Next
W.Activate 'active la feuille
With ActiveSheet.UsedRange
    .Select
    .Copy 'copie la sélection utilisée dans la feuille
Selection.PasteSpecial Paste:=xlValues 'collage des valeurs
End With
End Sub

Les deux macros sont à placer dans un module VBA et ne peuvent fonctionner l'une sans l'autre.

Je n'ai pas ajouté d'instruction pour la sauvegarde. Il te suffit de rajouter les instructions de la macro de Virginie à la suite de l'instruction 'Next W' et ce, dans la macro Archive.

Espérant que cela te conviendra.

;)

Message édité par: Dan, à: 06/10/2005 21:19
 

Discussions similaires