Insérer une formule dans un autre classeur [Résolu]

[YDevY]

Bonjour,

j'exécute une macro dans un classeur A. Cette macro doit insérer une formule dans une cellule d'un classeur B.
La formule a insérer fait référence à une valeur contenue dans une cellule du classeur A.

Comment faire ?

Voici le code :

' le classeur A s'appelle "date_ref.xls"
' le classeur dans lequel il faut insérer la formule se nomme ici "sem10.xls"

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
45
46
47
48
49
50
51
52
53
54
55
56
57
Sub MAJDate()
 
    ' compteur de nombre de semaine
    Dim x As Integer
 
    ' Pour chaque fichier
    For x = 10 To 10
 
        ' Ouverture des classeurs excel à mettre àjour
        Dim NumSem As String
        If x < 10 Then
            NumSem = "0" & x
        Else
            NumSem = x
        End If
 
        Dim NomFich As String
        NomFich = "C:\mon_chemin\sem" & NumSem & ".xls"
 
        Dim appExcel As Excel.Application 'Application Excel
        Dim wbExcel As Excel.Workbook 'Classeur Excel
        Dim wsExcel As Excel.Worksheet 'Feuille Excel
 
        'Ouverture de l'application
        Set appExcel = CreateObject("Excel.Application")
        'Ouverture d'un fichier Excel
        Set wbExcel = appExcel.Workbooks.Open(NomFich, 3, False)
        'wsExcel correspond à la première feuille du fichier
        Set wsExcel = wbExcel.Worksheets(1)
 
        MsgBox wsExcel.Cells(1, 2).Value
 
        Dim MaFormule As String
        Dim MonCoef As Integer
 
        MonCoef = 7 * (x - 1)
 
        MaFormule = "=[date_ref.xls]Feuil1!$A$1 + " & MonCoef
 
        ' pour debug
        ' ActiveCell.Formula = MaFormule
 
        wsExcel.Activate
        wsExcel.Cells(1, 2).Formula = MaFormule
        MsgBox wsExcel.Cells(1, 2).Value
 
        wbExcel.Close 'Fermeture du classeur Excel
        appExcel.Quit 'Fermeture de l'application Excel
 
        'Désallocation mémoire
        Set wsExcel = Nothing
        Set wbExcel = Nothing
        Set appExcel = Nothing
 
    Next x
 
End Sub

Merci de votre aide

Y

[ZebreLoup]

Je pense que ça marcherait mieux si tu ouvrais les fichiers dans le même Excel plutôt que d'utiliser une autre application à chaque fois.
Si tu dois obligatoirement faire comme ça, il faut à mon avis utiliser le chemin complet du fichier source dans ta formule.

[YDevY]

Bonjour et merci de votre réponse,

Concrètement pour "optimiser" mon code (ouvrir les fichiers dans le même excel) il faudrait que je déplace l'instruction :

Code :

1
2
 
Set appExcel = CreateObject("Excel.Application")

avant mon itération, c'est cela ?

Pendant que j'y suis, une fois que j'ai fait la modification de cellule dans mon classeur B, comment puis je le fermer en enregistrant la modification sans qu'il y ait la boîte de dialogue me demande de fournir un nom pour enregistrer mon fichier ?

Cordialeme,t

[ZebreLoup]

En fait, ce que tu fais dans ton code, c'est que tu ouvres un nouvelle instance d'Excel pour chaque fichier.

Tu peux tester sans code : quand tu ouvre un fichier à partir d'Excel ou en double cliquant dessus dans l'explorateur de fichier, il est ouvert dans le même Excel, par contre, tu peux relancer une nouvelle application Excel (via le menu démarrer par exemple ou un raccourci) et là tu ne retrouves pas les fichiers déjà ouverts, il n'y a aucun lien entre les différents fichier.
Dans le premier cas, si dans Classeur1, tu choisis une cellule, que tu fais "=" et que tu cliques dans une cellule de Classeur2, ça va marcher.
Dans le deuxième cas, ce sera pour lui comme si tu cliquais sur ton navigateur préféré, ton logiciel de messagerie... Il ne va pas reconnaître que tu lui indiques une cellules d'une feuille Excel.

Mon idée était donc de travailler dans la même instance d'Excel. Pour cela, il ne faut pas déclarer ton appExcel :

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
45
46
47
48
49
50
51
52
Sub MAJDate()
 
    ' compteur de nombre de semaine
    Dim x As Integer
 
    ' Pour chaque fichier
    For x = 10 To 10
 
        ' Ouverture des classeurs excel à mettre àjour
        Dim NumSem As String
        If x < 10 Then
            NumSem = "0" & x
        Else
            NumSem = x
        End If
 
        Dim NomFich As String
        NomFich = "C:\mon_chemin\sem" & NumSem & ".xls"
 
        Dim wbExcel As Excel.Workbook 'Classeur Excel
        Dim wsExcel As Excel.Worksheet 'Feuille Excel
 
        'Ouverture d'un fichier Excel
        Set wbExcel = Workbooks.Open(NomFich, 3, False)
        'wsExcel correspond à la première feuille du fichier
        Set wsExcel = wbExcel.Worksheets(1)
 
        MsgBox wsExcel.Cells(1, 2).Value
 
        Dim MaFormule As String
        Dim MonCoef As Integer
 
        MonCoef = 7 * (x - 1)
 
        MaFormule = "=[date_ref.xls]Feuil1!$A$1 + " & MonCoef
 
        ' pour debug
        ' ActiveCell.Formula = MaFormule
 
        wsExcel.Activate
        wsExcel.Cells(1, 2).Formula = MaFormule
        MsgBox wsExcel.Cells(1, 2).Value
 
        wbExcel.Close 'Fermeture du classeur Excel
 
        'Désallocation mémoire
        Set wsExcel = Nothing
        Set wbExcel = Nothing
 
    Next x
 
End Sub

Pour ta question subsidiaire, regarde dans les options de la méthode Close de l'objet Workbook, tu y trouveras ton bonheur.

[YDevY]

Merci cela fonctionne parfaitement