Changer la valeur d'une cellule dans un autre classeur

Le 21/06/2016 à 11:45

Bonjour,

J'essaye d'actualiser une base de données présente sur un fichier Excel (fichier2) en fonction d'une autre feuille Excel (fichier1).

--> Sur le fichier 1 un utilisateur rentre des références dans une colonne et lorsque il appui sur la macro ça va actualiser certaines informations sur la ligne correspondant aux références des produits venant d'être rentrés.

Je pense que le problème de mon code est que je n'arrive pas à trouver la bonne commande pour accéder à la valeur d'une cellule d'un autre classeur Excel (Classeur ouvert). Ainsi, lorsque j'execute mon code il me met "erreur d'execution "9" l'indice n'appartient pas à la selection. "

Pour accéder à la valeur d'une cellule d'un classeur spécifique je croyais que le commande était :

Workbooks("nomdufichier.xlsm").Sheets("nomfeuille").Cells(ligne, colonne)

Voici mon code :

Sub valider()
Dim i As Integer
Dim j As Integer
Dim zone As String

'initialisation de i
i = 12
'initialisation de j
'j = 2
Dim reference As Integer

'Tant que la cellule de coordonnées (i,1) (1ère colonne, ligne i) n'est pas vide :

While Cells(i, 2) <> ""
 reference_produit = Cells(i, 2)

     For j = 2 To 20

     If Workbooks("fichier2.xlsm").Sheets("feuille1").Cells(j, 1) = reference_produit Then
     Workbooks("fichier2.xlsm").Sheets("feuille1").Cells(j, 8) = Indisponible
      Workbooks("fichier2.xlsm").Sheets("feuille1").Cells(j, 11) = Workbooks("fichier1.xlsm").Sheets("feuille1").Cells(3, 2)

     Else
     MsgBox ("Un des produits n'est pas dans la base de données ")
       End If
   Next

 i = i + 1
  Wend
End Sub

Difficulté supplémentaire le fichier 1 est un nom de fichier préalablement rentré par l'utilisateur.

Si vous pouvez m'aider un grand merci.

Bonne journée.

Le 21/06/2016 à 15:02

Bonjour Elktrs, bonjour le forum,

L'erreur 9 est symptomatique d'un nom d'onglet mal orthographié dans le code. Je constate que tu utilises feuille1 dans le code alors que le nom par défaut est feuil1. L'erreur ne se trouve-t-elle pas là ?...

Pour le second problème peux-tu nous en dire plus sur le préalablement rentré par l'utilisateur. Comment ?

Sinon, quand je fais des aller/retour entre divers onglets (du même ou de plusieurs classeurs), j'utilise des variables pour me simplifier la tâche. Du style :

Sub Macro1()
Dim CL1 As Workbook
Dim O1 As Worksheet
Dim CL2 As Workbook
Dim O2 As Worksheet

Set CL1 = Workbooks("fichier1.xlsm")
Set O1 = CL1.Worksheets("Feuil1")
Set CL2 = Workbooks("fichier2.xlsm")
Set O2 = CL2.Worksheets("Feuil1")

O1.Range("A1").Copy O2.Range("B1") 'copie la cellule A1 de l'onglet O1 du classeur CL1 et la colle dans B1 de l
 onglet O2 du classeur CL2
End Sub
Le 21/06/2016 à 15:58

Merci pour ton aide mais ça ne marche toujours pas... Pour essayer de comprendre comment ça fonctionne j'ai fais le petit test suivant :

Je souhaite afficher la valeur d'une cellule d'une feuille et d'un classeur spécifique.

Sub valider()
Dim CL1 As Workbook
Dim O1 As Worksheet
'Le Fichier Excel est ouvert. Le fichier a pour nom test.xls et j'ai renommé la première feuille "test"
Set CL1 = "test.xls"
Set O1 = CL1.Worksheets("test")
'Après j'essaye d'afficher une case du classeur et de la feuille correspondante : 
affiche_moi = CL1.O1.Cells(4, 2)
'j'ai écris "test" dans cette case
MsgBox (affiche_moi)
'normalement je devrais avoir l'affichage de la valeur

J'obtient un message d'erreur "erreur de compilation : incompatibilité de type";

Sur mes Excel, j'ai vérifié le nom d'onglet et de fichier et je ne pense pas que ça soit ça. Un de mes onglets comporte des espaces : il se nomme "Base de données' est-ce que ça peut avoir une influence ?

Sinon pour expliquer très vite ce que j'ai fais avant le fichier 2 comportant la base de données permet la création du fichier 1 et demande à l'utilisateur via une inputbox le nom du fichier 1 (comment il veut le nommer) Ce nom est ensuite mis directement sur une cellule de l'Excel. (fichier1)

--> Je ne vois pas comment l'utiliser pour déclarer l'autre classeur tel que : Set CL1 = "valeurcellule.xls". avec valeurcellule le nom du fichier qui a été mis dans une cellule de l'excel (fichier 1) via ma précédente macro.

Merci beaucoup. J'espère avoir été à peu près claire

Bonne journée

Le 21/06/2016 à 16:10

Re,

Normal ! Tu as écrit :

Set CL1 = "test.xls"

au lieu de :

SET CL1 = Workbooks("text.xls")...

Workbooks renvoie la collection des classeur ouverts...

Re,

Normal ! Tu as écrit :

Set CL1 = "test.xls"

au lieu de :

SET CL1 = Workbooks("text.xls")...

Workbooks renvoie la collection des classeur ouverts...

Pour la suite une fois que tu as définis les onglets, si la cellule qui contient e nom du classeur (disons C2) est dans l'onglet O2, tu peux définir le classeur avec :

Set CL2 = Workbooks(O2.range("C2").Value)
Le 21/06/2016 à 16:17

Ah oui je n'ai pas fais attention. Avec la modif toujours l'erreur d'execution 9 : l'indice n'appartient pas à la sélection

Sub valider()

Dim CL1 As Workbook
Dim O1 As Worksheet

Set CL1 = Workbooks("test.xls")
Set O1 = CL1.Worksheets("test")

affiche_moi = CL1.O1.Cells(4, 2)
MsgBox (affiche_moi)
End Sub
Le 21/06/2016 à 16:29

Re,

Quand tu as défini O1 avec

Set O1=CL1.Sheets("test")

par la suite, pour l'appeler tu n'as plus besoin (et ne surtout dois plus) utiliser CL1.O1 (qui va planter) mais seulement O1

ton code devient :

Sub valider()
Dim CL1 As Workbook
Dim O1 As Worksheet

Set CL1 = Workbooks("test.xls")
Set O1 = CL1.Worksheets("test")
MsgBox O1.Cells(4, 2).Value
End Sub
Le 21/06/2016 à 16:50

En copiant collant ton code, j'ai toujours la même erreur : erreur d'execution 9 : l'indice n'appartient pas à la selection

--> Quelques précisions : le fichier test.xls est ouvert. L'onglet 1 a été renommé test.

Je réalise la macro à partir d'un autre fichier Excel. (Souhaitant ainsi afficher la valeur d'une cellule d'un autre classeur.)

Les fichiers excels doivent-ils être dans le même répertoire ? C'est peut-être une histoire de chemin... Il n'arrive pas à trouver le fichier Excel test.xls ?

Sub valider()

Dim CL1 As Workbook
Dim O1 As Worksheet

Set CL1 = Workbooks("test.xls")
Set O1 = CL1.Worksheets("test")

affiche_moi = CL1.O1.Cells(4, 2)
MsgBox (affiche_moi)
End Sub

Encore merci

Le 21/06/2016 à 17:05

Re,

Pourquoi tu me renvoies le mauvais code et pas le dernier que je t'ai fournis ?

Si les fichiers sont ouverts, cela n'a aucune importance qu'il ne soient pas placés dans le même dossier.

Le code que je t'ai fournis fonctionne en voici la preuve :

1. Ouvre les deux classeurs en pièce jointe

2. active le classeur 2

3. lance la macro Macro1 (de ce classeur 2)

195classeur1.xlsm (8.66 Ko)
192classeur2.xlsm (14.05 Ko)
Le 21/06/2016 à 17:12

J'ai essayé ton code avec tes fichiers et ça marche et j'ai réussi à adapter mon code

Par contre, j'ai rencontré deux problèmes :

  • Lorsque une feuille à un nom composé de plusieurs mots par exemple "feuille de référence" ça ne marche pas alors que si la feuille s'appelle seulement "reference" ça marche.
  • La commande suivante ne marche pas (Lorsque le nom du fichier est présent sur une cellule du fichier Excel)
Set CL2 = Workbooks(O2.range("C2").Value)
Set O2 = CL2.Worksheets("Feuil1")

Car on définit CL2 avec O2 et inversement.

J'ai trouvé la solution de mettre Set CL2 = ActiveWorkbook car le bouton de la macro est sur la deuxième feuille et ça fonctionne.

Si vous avez une autre idée pour modifier la commande précédente je suis preneur.

Bonne journée

Le 04/09/2019 à 15:30

Bonjour forum,

Je rebondi sur ce post, car c'est ce qu'il me faudrait a un petit détail près, comment serait le code avec un classeur non ouvert ?

Le 04/09/2019 à 18:28

En faite j'ai trouvé ce bout de code qui fonctionne parfaitement pour changer la valeur d'une cellule d'un classeur fermé a un emplacement précis.

Pour que la macro fonctionne il faut activer les références dans VBA:

Microsoft ActiveX Data Objects 2.0 Library

Microsoft ADO Ext. 2.5 for DDL and Security

Sub Changer_valeur()
    Dim Cn As ADODB.Connection
    Dim Cd As ADODB.Command
    Dim Rst As ADODB.Recordset

    Fichier = "C:\Users\nom utilisateur\Desktop\Nouveau Dossier\Test.xls" 'changer par votre nom de chemin et de classeur
    Set Cn = New ADODB.Connection
    Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & Fichier & ";" & _
        "Extended Properties=""Excel 8.0;HDR=No;"";"

    Set Cd = New ADODB.Command
    Cd.ActiveConnection = Cn
    Cd.CommandText = "SELECT * FROM [Feuil1$A1:A1]" ' ce rapporte au nom de la Feuil1 du classeur Test.xls et de la cellule A1

    Set Rst = New ADODB.Recordset
    Rst.Open Cd, , adOpenKeyset, adLockOptimistic
    Rst(0).Value = "3" ' la valeur a changer
    Rst.Update

    Cn.Close
    Set Cn = Nothing
    Set Cd = Nothing
    Set Rst = Nothing

End Sub
Rechercher des sujets similaires à "changer valeur classeur"