XL 2013 VBA - problème avec la fonction AddFromString

[sardaucar]

Bonjour à tous,

Je possède la procédure suivante :

Sub copie_données()

Dim chemf As String
Dim nomf As String

'Sélection du fichier contenant  les données
chemf = Application.GetOpenFilename("Fichiers Excel, *.xlsx", , "Sélectionnez le fichier de données", "Importer")

Application.ScreenUpdating = False

'Ouverture du fichier et récupération des variables
Workbooks.Open (chemf)
nomf = ActiveWorkbook.Name
pos = InStr(ActiveWorkbook.Name, ".xls")
chem = Left(nomf, pos - 1)

'Copie des données dans le classeur "Suivi base horaire" et fermeture/suppression du fichier de données
Workbooks("Suivi base horaire.xlsm").Sheets("Données").Range("A1:Z5000") = Workbooks(nomf).Sheets("sheet1").Range("A1:Z5000").Value
Workbooks(nomf).Close
Kill chemf

'Sauvegarde du classeur à la place du fichier de données, avec le même nom que ce dernier
ActiveWorkbook.SaveAs Filename:="M:\Administration\Secrétariat RH\LOGICIELS RH\CHARLES\KELIO\Suivi base horaire\2017\" & chem & ".xlsm"
Application.ScreenUpdating = True

ActiveSheet.Range("D8").Font.ColorIndex = 10

End Sub

Elle me permet en substance de récupérer des données d'un fichier dans un classeur modèle, puis d'enregistrer ce classeur modèle à la place du fichier de données. Ainsi je conserve intact mon classeur modèle, et j'ai une copie des données puisque la manipulation doit être effectuée chaque semaine.

Cela fonctionne très bien. Cependant, j'ai ensuite le code suivant :

    Dim Ws As Worksheet
  
    For i = 1 To Nbresp
        cFeuil = Worksheets("Modèle").Cells(i, 1).Value
      
            Set Ws = ThisWorkbook.Worksheets(cFeuil)
            With ThisWorkbook
                With .VBProject.VBComponents(Ws.CodeName).CodeModule 'Copie l'onglet sans les macro de la feuille
                    .AddFromString "Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)" & vbLf & _
                                   "If Not Application.Intersect(Target, Range(""A1"")) Is Nothing Then" & vbLf & _
                                   "Worksheets(""Modèle"").Activate" & vbLf & _
                                   "End If" & vbLf & _
                                   "End Sub"
                End With
            End With
    Next

Qui me permet d'insérer du code vba dans chaque page du classeur (une vingtaine au total). Ces pages sont créées en vba juste avant dans la même procédure.

Ce code me renvoi une erreur d'exécution 9 : Cet indice n'appartient pas à la sélection sur cette ligne :

With .VBProject.VBComponents(Ws.CodeName).CodeModule 'Copie l'onglet sans les macro de la feuille

Pourtant en posant un espion sur mes variables, elles contiennent les bonnes données.

De plus, lorsque je ferme le classeur nouvellement créé grâce au vba puis que je l'ouvre à nouveau, à ce moment là la procédure s'exécute correctement.

Erratum : La première fois que je lance la procédure elle plante, mais si je la relance ensuite elle fonctionne. :/

C'est comme si le fait d'enregistrer sous en vba et de lancer la seconde procédure directement après provoquait un conflit dans mes vba projects.

Mais là je sèche un peu pour comprendre pourquoi.

Merci d'avance pour toute aide sur la question,

Sardaucar

 

[Bougla972]

Bonjour Sardaucar,

Quelles sont les données figurant sur la 1ère colonne de l'onglet 'Modèle' ?

 

[sardaucar]

Bonjour Bougla,

Ce sont des nom prénoms, donc avec espace, accents et caractère spéciaux (-,é,è)

 

[Bougla972]

Re,

Ce sont ces prénoms qui sont repris pour identifier les noms des onglets ?

 

[sardaucar]

Oui tout à fait, chaque onglet est créé suivant cette liste, qui peut évoluer. Je reprends ensuite chaque onglet créé ainsi pour lui insérer le code voulu, bien que dans la formule je combine le nom de l'onglet (cfeuil), et le code (Ws.Codename).

 

[Bougla972]

Est-ce cette formule coïncide avec le nom de l'onglet nouvellement crée ?

 

[sardaucar]

Oui pour le nom de l'onglet correspondant, j'ai bien le même numéro de CodeName.

 

[Bougla972]

Peux-tu fournir un exemple de ton fichier ? Car je ne vois pas si ce qui peut planté sans cas concret....

Quand je teste ce que je t'ai fourni pour la création d'onglet avec code, cela fonctionne bien. Il faudrait voir comment tu utilise ce code.

 

[sardaucar]

Voici deux fichiers pour les tests, j'ai du les anonymiser, et dans la sub "copie données" il faudra renseigner votre chemin local .
La manipulation que je fais après que la sub soit entrée en echec est de supprimer les feuilles à l'aide de cette sub de la feuille données :

Sub Suppr_feuil()

Dim i As Integer
Dim cFeuil As String

Application.DisplayAlerts = False
For i = 1 To 23
    cFeuil = Worksheets("Modèle").Cells(i, 1).Value
    On Error Resume Next
    Sheets(cFeuil).Delete
Next
Application.DisplayAlerts = True

End Sub

Puis je relance le code à l'aide d'un bouton et là ça fonctionne -_-.

 

[Bougla972]

Sardaucar,

Certains caractères spéciaux ne sont pas acceptés pour l'intitulé des onglets (ex: '\').
Ceci étant, lorsque je teste ton fichier 'Suivi base horaire.xlsm':

La procédure 'copie_données' fonctionne correctement

La procédure 'Mise_en_page' créer les onglet 'a' et 'b' avec le code que tu souhaites insérer dans ces onglets...

Je n'ai pas de bug pour cela.

 

[sardaucar]

Alors ce doit être lié à mon environnement car j'ai toujours cette erreur... :/ Excel me troll x)