Fonction AddFromString sur un classeur créé en vba

Bonjour à tous,

Je possède la procédure suivante :

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

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 :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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 :

Code:

---

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

Combien de classeurs ouverts quand tu lances ta macro d'insertion de code?

Je soupçonne la ligne 4

A priori 1 seul puisque dans la procédure précédente de ferme le classeur de données :

Code:

---

1 2	Workbooks(nomf).Close Kill chemf

---

Change ta ligne 4 et regarde la msgbox ce que donne

Code:

---

1 2 3

cFeuil = ThisWorkbook.Worksheets("Modèle").Cells(i, 1).Value Set Ws = ThisWorkbook.Worksheets(cFeuil) msgbox Ws.CodeName

---

Ca me renvoi le numéro de la feuille "Feuil24" qui correspond au nom de la feuille contenu dans la variable cFeuil, toujours le même message d'erreur par contre :/.

Je ne vois pas l'erreur

Sinon, au lieu d'insérer le même code dans toutes tes feuilles, utiliser plutôt l'évènement SheetBeforeDoubleClick

Code:

---

1 2 3 4 5 6 7

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)   If Sh.name<>"Modèle" And Target.address="$A$1" then Cancel=true Worksheets("Modèle").Activate end if End Sub

---

Bonjour,

moi non plus je ne vois pas la logique (car il y en a forcément une) qui aboutisse à cette erreur.
si elle n'arrive que la première fois, on peut effectivement se poser la question d'un VbProjet en dualité entre le classeur d'origine et le résultat du SaveCopyAs .... comme on peut le voir quand on réalise pas mal d'ouverture et fermetures de classeurs dans une procédure (il y a persistance "visuelle" des Vbprojects des classeurs)
car le fait d'arrêter la procédure qui plante remet les pendules à l'heure sur pas mal de choses ... comme l'exemple des variables globales qui perdent leurs valeurs en cas d'arrêt sauvage d'une procédure.


pour continuer à investiguer, pourrais-tu montrer comment s'enchainent ces deux procédures ? Est-ce qu'une procédure maître commande ces deux (sous-)procédure ? Sinon, leur lancement est effectué comment, et quel est l'écart de temps entre les deux traitements ?

Je lance les procédures à l'aide de bouton de contrôle dans une feuille. Les sub sont dans des modules séparés, et j'ai attendu quelques dizaines de secondes entre chaque sans changement notoire :/

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 :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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 -_-.

En désespoir de cause je joins les deux fichiers pour test, il faudra juste renseigner un chemin local dans la sub copie_données évidemment ;).