Problème avec nom d'un classeur en variable

Version imprimable

Bonjour le forum,

Je viens à nouveau vers vous car j'ai une petite incompréhension et j'aurais besoins d'un éclaircissement.
Dans un premier temps voici mes codes :

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
Public Commande As String
Public Datec As String
Public Nomcde As String
Public Cheminc As String
Public Nomc As String
Public yc As Integer
Public Nbc As String
Public dicoc As New Dictionary
Public Wb As String
 
Sub essai()
Wb = ActiveWorkbook.Name
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim Repertoire As FileDialog
 
For y = 1 To Cells(50, Cells.Columns.Count).End(xlToLeft).Column
If Not dicoc.Exists(Cells(50, y).Value) Then
dicoc.Add Cells(50, y).Value, Cells(50, y).Column - a
a = y
End If
Next y
 
NCom.Show
 
Set xlApp = CreateObject("Excel.Application")
    xlApp.SheetsInNewWorkbook = dicoc.Count
Set xlBook = xlApp.Workbooks.Add
    xlBook.SaveAs (Cheminc & "\- X - " & Commande & " - Truc.xls")
    xlApp.Visible = True
    Nomc = "- X - " & Commande & " - Truc.xls"
 
For yc = 1 To dicoc.Count
Nbc = dicoc("cellule " & yc)
Set xlSheet = xlBook.Worksheets(yc)
xlSheet.Name = "Cellule " & yc
Sheets(yc).Activate
Call Mise_en_page
Call Mise_en_paquet
Set xlSheet = Nothing
Next yc
 
End Sub

Suivi de (dans le même module) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sub Mise_en_page()
 
    Workbooks(Nomc).Sheets(yc).Columns("A:M").ColumnWidth = 12
    Workbooks(Nomc).Sheets(yc).Columns("F:F").ColumnWidth = 2.29
    Workbooks(Nomc).Sheets(yc).Range("E1:H2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .MergeCells = True
        .Font.Size = 16
    End With
 
    Workbooks(Nomc).Sheets(yc).Range("B6").FormulaR1C1 = "CDE :": Workbooks(Nomc).Sheets(yc).Range("B6").Font.Size = 14: Workbooks(Nomc).Sheets(yc).Range("C6").Value = Commande
    Workbooks(Nomc).Sheets(yc).Range("B7").FormulaR1C1 = "Chantier :": Workbooks(Nomc).Sheets(yc).Range("B7").Font.Size = 14: Workbooks(Nomc).Sheets(yc).Range("C7").Value = Nomcde
    Workbooks(Nomc).Sheets(yc).Range("B10").FormulaR1C1 = "Sortie :": Workbooks(Nomc).Sheets(yc).Range("B10").Font.Bold = True: Workbooks(Nomc).Sheets(yc).Range("B10").Font.Size = 14
    Workbooks(Nomc).Sheets(yc).Range("D10").FormulaR1C1 = "Nb colis :": Workbooks(Nomc).Sheets(yc).Range("E10").FormulaR1C1 = Nbc
    Workbooks(Nomc).Sheets(yc).Range("E11").FormulaR1C1 = "Rq :": Workbooks(Nomc).Sheets(yc).Range("E11").Font.Bold = True
    Workbooks(Nomc).Sheets(yc).Range("F11").FormulaR1C1 = "1 latte ": Workbooks(Nomc).Sheets(yc).Range("F11").Font.Bold = True
    Workbooks(Nomc).Sheets(yc).Range("E1").FormulaR1C1 = "Schéma"
 
End Sub

Comme on peut le voir j'utilise une variable (Nomc) pour sauvegarder le nom du nouveau classeur créé. Or quand je l'a réutilise dans la macro Mise_en_page il me dit : "Erreur 9 : l'indice n'appartient pas à la sélection" :weird:

Donc si quelqu'un à une petite idée merci à lui :mrgreen:

20/05/2016, 12h10
joe.levrai

Bonjour,

NomC a une portée "procédure", elle n'ira pas plus loin que la procédure où elle est déclarée.

Si les deux procédures sont dans le même module, tu peux étendre sa portée au module complet, en la déclarant tout en haut du module (avant les procédures)

Si les deux procédures sont dans des modules différents, tu dois passer la variable en publique (déclaration tout en haut d'un module standard en remplaçant Dim par Public)

Autre méthode (au vu de ta procédure mise en page, c'est celle que je conseille), ajouter un paramètre à ta procédure, qui attend de recevoir une chaine de caractère. Et quand tu appelles la procédure, tu lui passes NomC en paramètre
20/05/2016, 12h13
Al__22

Merci de ta réponse joe mais mon Nomc est bien en variable Public ;)
20/05/2016, 12h23
joe.levrai

Effectivement, j'ai mélangé tes deux blocs de déclaration (Public et ceux de la procédure).

Du coup, en me penchant plus en détail, l'ai l'impression que tu crées une nouvelle instance Excel afin de créer un classeur et de l'enregistrer.

Dans ta procédure principale, étant donné que tout est rattaché à l'instance Excel (xlApp) créeen tu n'as aucune difficulté à y accéder.
Mais quand tu passes sur l'autre procédure, tu travailles sur un Workbook présent dans l'instance hôte du classeur contenant les macros, et pas dans la seconde instance. Qui expliquerait qu'aucun classeur de la collection des classeurs de l'instance n'est celui que tu cherches (car il est en dans une autre instance)

Regarde si ça ne serait pas la piste à étudier ?
20/05/2016, 12h46
Marc-L

Bonjour, bonjour !

Et dire le classeur serait inutile dans la procédure Mise_en_page pour peu de comprendre et respecter le modèle objet d'Excel !

Il suffit de passer en paramètre de cette procédure un objet Worksheet du genre Sub Mise_en_page(Ws As Worksheet) …

Et pour appeler cette procédure ce serait Mise_en_page Activesheet car la feuille est déjà activée dans le code …

Au passage un bon code n'utilise pas ces affreux ralentisseurs d'Activate et de Select,
ni une seconde instance d'Excel via CreateObject, quel gâchis !

Si la feuille n'est pas activée mais son classeur actif : Mise_en_page Worksheets(yc) …

Et si le classeur n'est pas actif : Mise_en_page xlBook.Worksheets(yc) …

_________________________________________________________________________________________________________
Je suis Paris, Charlie, Bruxelles, …
20/05/2016, 14h45
Al__22

Citation:

Regarde si ça ne serait pas la piste à étudier ?

En effet je n'y avais pas pensé, je vais regarder

Citation:

Il suffit de passer en paramètre de cette procédure un objet Worksheet du genre Sub Mise_en_page(Ws As Worksheet)

Et pour appeler cette procédure ce serait Mise_en_page Activesheet car la feuille est déjà activée dans le code …

Je t'avoue Marc-L que là j'ai pas tout compris

Citation:

Au passage un bon code n'utilise pas ces affreux ralentisseurs d'Activate et de Select,
ni une seconde instance d'Excel via CreateObject, quel gâchis !

Pour ce qui est des activate et select c'est normal c'est avec l'enregistreur que j'ai eu ce bout de code et à part changé les noms de cellules etc je ne l'ai pas remanier :mouarf:
Par contre si il y a pas besoin de seconde instance je suppose que créer un nouveau classeur s'écrit ainsi :

Code:

Workbooks.Add
20/05/2016, 14h57
MacKay77

Citation:

Je t'avoue Marc-L que là j'ai pas tout compris

Dans Mise_en_page(), tu utilises toujours la même feuille. Tu peux donc la mettre en paramètre de ta fonction de mise en forme, comme ça il n'y aura théoriquement plus besoin de préciser le classeur et la feuille dans ta fonction de mise en forme.
20/05/2016, 17h00
Al__22

Bon c'est bon en tout cas j'ai réussi mon coup avec workbooks.add merci a vous :mrgreen:
20/05/2016, 17h40
joe.levrai
Du coup c'était bien un problème d'instance :mouarf:

Penche-toi quand même sur la proposition de Marc-L, qui te propose d'ajouter dans tes procédures de mise en forme un argument :
Code:

1 2 Sub MaProcedure(LaFeuille As Worksheet) LaFeuille.Range("A1") '.... blabla voilà comment tu utilises ta feuille
que tu peux appeler génériquement comme ceci
Code:

1 2 3 4 Dim Feuille as Worksheet Set Feuille = Thisworkbook.Worksheets("Feuil1") Call MaProcedure(Feuille)
qui permet de réaliser des boucles aisément :
Code:

1 2 3 For Each Feuille in Thisworkbook.Worksheets Call MaProcedure(Feuille) Next Feuille