VBA-Enregistrer des données d'un formulaire vers un ensemble d'autres classeurs

- - Dernière réponse : michel_m
Messages postés
15977
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
19 novembre 2019
- 27 oct. 2019 à 07:54
Bonjour,
J’ai créé un classeur excel"macst.xlsm" avec un onglet "source" qui ne sert qu'à contenir un formulaire pour saisir des données affectées à 10 variables.
Je souhaite enregistrer directement ces données dans des classeurs autres que celui qui contient mon userform (autre que ce qui est actif?).
Mes fichiers de destination sont une cinquantaine de classeurs Excel différents situés dans un même répertoire (dossier de fichiers .xlsx) avec un grand nombre de worksheet mais tous conçus exactement de la même manière.

Pour désigner un classeur de destination spécifique, dans mon classeur "macst" sur mon onglet "source" j'ai inscrit en B4 le nom du classeur destination et en B5 le nom de l'onglet/worksheet de destination. Dans ma macro mon code est :

Dim monclasseur As Workbook
contenuB4 = Range("B4").Value
Set monclasseur = Workbooks.Open(Filename:="\\C:\Users\Channel\Documents\06. PRODUCTION & OPTIMISATION\06. VBA test\" & contenuB4 & ".xlsx")

Ce qui fonctionne et m'ouvre le classeur de destination choisi

Dim mafeuille As Worksheet
contenuB5 = Range("B5").Value 'this workbook
mafeuille = monclasseur.Worksheets("contenuB5")
mafeuille.Activate

Ce qui ne fonctionne pas et ne m'ouvre pas l'onglet correspondant ... sachant que mes onglets ont des noms numériques

Quelqu'un pourrait il m'aider à trouver l'erreur dans mon code svp ? Merci !
Afficher la suite 

5 réponses

Meilleure réponse
Messages postés
262
Date d'inscription
jeudi 18 juillet 2019
Statut
Membre
Dernière intervention
19 novembre 2019
11
1
Merci
Bonjour,

Essaie comme ceci (attention pas de quote autour de contenuB5)

contenuB5 = Range("B5").Value 'this workbook
set mafeuille = monclasseur.Worksheets(contenuB5)
monclasseur.Worksheets(contenuB5).Activate

Cordialement

Dire « Merci » 1

Heureux de vous avoir aidé ! Vous nous appréciez ? Donnez votre avis sur nous ! Evaluez CommentCaMarche

CCM 6022 internautes nous ont dit merci ce mois-ci

Commenter la réponse de ALS35
0
Merci
Bonjour,

J'ai essayé votre code et ça se bloque sur la phrase :
Set mafeuille = monclasseur.Worksheets(contenuB5) 

message : L'indice n'appartient pas à la sélection erreur d'exécution 9

Je me suis aperçue que dans mon "espion de variables" la variable contenuB5 n'était pas déclarée donc je l'ai déclarée comme String et maintenant elle affiche une valeur nulle c'est à dire qui s'affiche avec deux apostrophes""

Mon premier problème est que ma variable contenuB5 ne prend pas la valeur que je lui attribue avec le texte inscrit dans ma cellule B5. Pourtant la phrase
contenuB5 = Range("B5").Value
est exactement la même qu'avec contenuB4
contenuB4 = Range("B4").Value
qui est aussi un String. Cela m'ouvre le bon classeur lorsque je modifie le texte inscrit en B4 par contre cela n'active en aucun cas une feuille de calcul spécifique.

Mon deuxième problème : la variable mafeuille a comme valeur "nothing" et ne s'active pas (à mon avis cela découle de mon problème ci dessus avec "contenuB5")
ALS35
Messages postés
262
Date d'inscription
jeudi 18 juillet 2019
Statut
Membre
Dernière intervention
19 novembre 2019
11 -
Bonjour,

Et bien poste ici soit ton code complet soit encore mieux ton fichier contenant la macro et celui dans lequel tu veux ouvrir la feuille (épurés de données confidentielles si nécessaire)

Cordialement
Public Sub bvalid_Click() 'lors d'un clic sur le bouton ajouter dans base

'0)déclaration variables
Dim dhab As Date 'date
Dim refpo As Integer 'référence
Dim cx As String ' C ou EX
Dim qbt As Integer 'quantité b
Dim qdm As Integer 'quantité d
Dim qmg As Integer 'quantité m
Dim dget As String 'degré %
Dim sbx As Integer 'solde
Dim info As String 'informations

'1)on définit les variables à partir du contenu renseigné sur le Userform
dhab = ufdedhab.tbdhab.Value
refpo = ufdedhab.tbrefpo.Value
cx = ufdedhab.tbcx.Value
qbt = ufdedhab.tbqbt.Value
qdm = ufdedhab.tbqdm.Value
qmg = ufdedhab.tbqmg.Value
dget = ufdedhab.tbdget.Value
sbx = ufdedhab.tbsbx.Value
info = ufdedhab.tbinfo.Value

'2)on ouvre le classeur de destination et la feuille de destination
Dim monclasseur As Workbook
Dim contenuB4 As String
contenuB4 = Range("B4").Value
Set monclasseur = Workbooks.Open(Filename:="\\C:\Users\Channel\Documents\06. PRODUCTION & OPTIMISATION\06. VBA test\" & contenuB4 & ".xlsx")
Application.ScreenUpdating = False

Dim mafeuille As Worksheet
Dim contenuB5 As String
contenuB5 = Range("B5").Value 'ne marche pas car la variable ne se déclare pas
Set mafeuille = monclasseur.Worksheets(contenuB5).Value 'ne marche pas
monclasseur.Worksheets(contenuB5).Activate 'ne marche pas

'mafeuille.Name = monclasseur.Worksheets(contenuB5).Value 'ne marche pas
'mafeuille.Activate 'ne marche pas
'Worksheets("9900").Activate marche mais ne permet pas de changer le nom de l'onglet sans coder
' Worksheets("ongletactif").Activatemarche mais note les infos dans le classeur contenant la macro

'3)on inscrit les données dans les cellules correspondantes
Range("A8").Value = dhab
Range("B8").Value = refpo
Range("C8").Value = cx
Range("D8").Value = qbt
Range("F8").Value = qdm
Range("H8").Value = qmg
Range("J8").Value = dget
Range("K8").Value = sbx
Range("L8").Value = info

End Sub
ALS35
Messages postés
262
Date d'inscription
jeudi 18 juillet 2019
Statut
Membre
Dernière intervention
19 novembre 2019
11 -
Bonjour,

Le problème vient du fait que quand tu fait Set monclasseur = Workbooks.Open ..., tu changes de classeur actif et de feuille active, donc l'instruction contenuB5 = Range("B5").Value ne porte pas sur ta feuille initiale.
Déplace cette instruction et les déclarations associées avant Set monclasseur = Workbooks.Open et cela devrait aller mieux

Cordialement
Commenter la réponse de chanelausie
0
Merci
Merci beaucoup ça marche ! Désolé je n'y avais pas du tout pensé ...

J'ai une autre question : je ne souhaite pas entrer les données sur la ligne 8 comme ci-dessus dans mon étape numéro 3, mais je souhaite saisir mes données dans la dernière ligne libre de ma feuille active.
</gras>

Je dois utiliser la fonction offset :
 Range("A1").End(xlDown).Offset(1,0) 

Soit, je commence par sélectionner la dernière ligne vide en colonne A.

Pour rappel la première partie de mon code mis à jour :

 Public Sub bvalid

0)déclaration variables
Dim dhab As Date 'date habillage
Dim refpo As Integer 'référence PO
Dim cx As String ' CRD ou EXPORT
Dim qbt As Integer 'quantité bouteilles habillées
Dim qdm As Integer 'quantité demis habillées
Dim qmg As Integer 'quantité magnums habillés
Dim dget As String 'degré étiquette %
Dim sbx As Integer 'solde box
Dim info As String 'informations

'1) On définit les variables à partir du contenu renseigné sur le Userform
dhab = ufdedhab.tbdhab.Value
refpo = ufdedhab.tbrefpo.Value
cx = ufdedhab.tbcx.Value
qbt = ufdedhab.tbqbt.Value
qdm = ufdedhab.tbqdm.Value
qmg = ufdedhab.tbqmg.Value
dget = ufdedhab.tbdget.Value
sbx = ufdedhab.tbsbx.Value
info = ufdedhab.tbinfo.Value

'2) On ouvre le classeur de destination et la feuille de destination
Dim monclasseur As Workbook 'de destination
Dim mafeuille As Worksheet 'de destination

Dim contenuB4 As String
contenuB4 = Range("B4").Value

Dim contenuB5 As String
contenuB5 = Range("B5").Value
Set monclasseur = Workbooks.Open(Filename:="\\C:\Users\Channel\Documents\06. PRODUCTION & OPTIMISATION\06. VBA test\" & contenuB4 & ".xlsx")
'Application.ScreenUpdating = False
Set mafeuille = monclasseur.Worksheets(contenuB5)

monclasseur.Worksheets(contenuB5).Activate

'3)Démarche pour inscrire les données lignes après lignes dans le classeur de destination


Voici la suite de mon idée de code, qui ne fonctionne évidemment pas :

Dim lastline As Long
lastline = Range("A1").End(xlDown).Offset(1, 0).Select
Range("A1" & lastline).Value = dhab


merci ...
ALS35
Messages postés
262
Date d'inscription
jeudi 18 juillet 2019
Statut
Membre
Dernière intervention
19 novembre 2019
11 -
Bonsoir,

Essaie plutôt comme ceci :

Dim lastline As Long
lastline = Range("A1").End(xlDown).Row +1
Range("A" & lastline).Value = dhab
Range("B"& lastline)).Value = refpo
etc ...

Mais si tes colonnes n'ont pas toutes la même hauteur, cela va poser problème, à toi de dire ce que tu veux dans ce cas.

Cordialement
Chanelausie
Messages postés
2
Date d'inscription
vendredi 25 octobre 2019
Statut
Membre
Dernière intervention
26 octobre 2019
-
Ok cela marche, ça c’est top en revanche cela ne mets à jour que la ligne 4 ...
Lorsque je veux faire une nouvelle entrée cela met à jour la ligne 4 et ne descend pas à la ligne 5 par exemple
Ma feuille est peut être mal construite mais il semblerait que quelque soit la mise en forme cela ne modifie que la ligne 4.
Peut être je peux résoudre ce problème en ne commençant qu’à inscrire les données à partir de la ligne 8 et ainsi de suite mais je ne sais pas comment faire.
ALS35
Messages postés
262
Date d'inscription
jeudi 18 juillet 2019
Statut
Membre
Dernière intervention
19 novembre 2019
11 -
Bonjour,

J'ai l'impression que tu as des cellules fusionnées qui nous embêtent au début de ta feuille.
Alors essaie en partant de la ligne 5
Dim lastline As Long
lastline = Range("A5").End(xlDown).Row +1
Range("A" & lastline).Value = dhab
...

Cordialement
Commenter la réponse de chanelausie
0
Merci
J'ai testé une sorte de boucle d'après des essais sur d'autres forum mais cela ne marche pas. Et je ne comprends pas ce qui est écrit donc ne peux l'adapter à mon code. Cela me semble aussi assez restrictif ...


Dim lastline As Long, count_entries As Integer
Dim TxtRng As String
TxtRng = “A8:A40”
count_entries = Application.WorksheetFunction.CountIf(range(TxtRng), "<>")
If count_entries = 1 Then
lastline = Sheets("mafeuille").range("A8").Row
Else
lastline = Sheets("mafeuille").range(TxtRng).End(xlDown).Offset(1, 0).Row
End If
Sheets("mafeuille").range("A" & lastline).Value = "dhab"


Je suis un peu confuse, je ne comprends pas le raisonnement du offset et des boucles utilisées.
Doit-on nécessairement utiliser un
count row 
avant le
xlDown
?

Le soucis est donc également mes autres variables que je pensais mettre dans une variable
lastline 
différente pour chaque colonne :
Range("A" & lastlineA).Value = dhab
Range("B" & lastlineB).Value = refpo
Range("C" & lastlineC).Value = cx
Range("D" & lastlineD).Value = qbt
Range("E" & lastlineE).Value = qdm
Range("F" & lastlineF).Value = qmg
Range("G" & lastlineG).Value = dget
Range("H" & lastlineH).Value = sbx
Range("I" & lastlineI).Value = info


Je ne peux pas car si ça se trouve la dernière ligne vide de la colonne B n'est pas la même que celle de la colonne A. Or toutes mes données de formulaire doivent figurer sur la même ligne puisqu'elle sont corrélées.

Je souhaite donc en somme :
'Sélectionner la dernière cellule de texte +1 (donc 1ère cellule vide) de ma colonne A
'Affecter à cette ligne une variable appelée "lastline" = "dernière ligne"
'Entrer dans ma cellule (A & dernière ligne) ma donnée de variable "dhab" (qui est une date)
'Décaler à droite en B & lastline pour y inscrire refpo
'Décaler encore à droite en C & lastline pour y inscrire ma donnée "cx"
'et ainsi de suite... jusqu'en I avec la donnée info

'Puis sauvegarder mafeuille.monclasseur et le fermer


Merci
Commenter la réponse de chanelausie
Messages postés
15977
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
19 novembre 2019
2824
0
Merci
Bonjour,

de passage...

Principe de base pour restituer des valeurs sur une ligne déterminée:
Option Explicit
'--------------
Sub demo()
Dim Liste
Liste = Array(12, 14, "aaa", 10)
Range("A1").Resize(1, 4) = Liste
End Sub



Chanelausie
Messages postés
2
Date d'inscription
vendredi 25 octobre 2019
Statut
Membre
Dernière intervention
26 octobre 2019
-
Bonjour je ne pense pas qu’un array va fonctionner car j’ai des données en colonnes E G I


J’ai essayé avec Range.Find de manière à résoudre ce problème de décalage des lignes
michel_m
Messages postés
15977
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
19 novembre 2019
2824 -
MERCI d'avoir essayé de t'aider!!!!

lire d'urgence
http://www.commentcamarche.net/contents/129-charte-d-utilisation-de-commentcamarche-net-respect-d-autrui#politesse

Revoir aussi un topo sur les Arrays...
Commenter la réponse de michel_m