Pb avec code VBA : demande du nom du fichier source pour chaque opération
Bonjour,
Débutante en VBA, je me permets de poster ce message car j'ai un problème avec le code que je viens de créer :
ce code permet de sélectionner et d'ouvrir un fichier de données, d'extraire les moyennes de différents paramètres puis de les coller dans mon fichier de destination à un endroit précis.
Mon souci c'est que pour chaque valeur à copier, il me redemande de sélectionner le fichier source ( j'ai de nouveau la boite de dialogue explorateur qui s'ouvre.
Comme je n'ai pas de boucle, je ne comprends pas pourquoi ?
Comment faire pour que le nom du fichier ne soit demandé qu'une fois ?
En vous remerciant par avance pour votre aide.
Voici le code :
Sub MAJ_Moy_an_perf()
'
' MAJ_Moy_an_perf Macro
' actualisation de la moyenne annuelle avec les données de la semaine
Dim wbSource, wbFichierUsager As Workbook
Dim strFileName As String
Dim intChoice As Integer 'Déclarer les variables de base
Set wbFichierUsager = ThisWorkbook
' On va appeler une application de MS Office afin de chercher et d’ouvrir le bon fichier
'Avec la commande qui suit, on indique que nous ne voulons qu’un seul fichier
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'On affiche l’écran de dialogue de MS Office
'On récupère le nom complet du fichier
strFileName = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
Workbooks.Open strFileName
Set wbSource = ActiveWorkbook
'Ici, on insère le code qui applique les changements voulus
wbFichierUsager.Sheets("perf").Activate
Range("A12:B13").Select
ActiveCell.FormulaR1C1 = _
"='[wbSource]données'!R21C4"
Range("R3").Select
ActiveCell.FormulaR1C1 = _
"='[wbSource]données'!R21C49"
Range("F17").Select
ActiveCell.FormulaR1C1 = _
"='[wbSource]données'!R21C9"
Range("L17").Select
ActiveCell.FormulaR1C1 = _
"='[wbSource]données'!R21C14"
Range("R17").Select
ActiveCell.FormulaR1C1 = _
"='[wbSource]données'!R21C19"
Range("R18").Select
ActiveCell.FormulaR1C1 = ""
Range("F32").Select
ActiveCell.FormulaR1C1 = _
"='[wbSource]données'!R21C24"
Range("L32").Select
ActiveCell.FormulaR1C1 = _
"='[wbSource]données'!R21C29"
Range("R32").Select
ActiveCell.FormulaR1C1 = _
"='[wbSource]données'!R21C44"
Range("R33").Select
wbSource.Close SaveChanges:=False 'On ferme le fichier sans le sauver
'Sinon, on arrête tout en notifiant l’usager
End Sub
Bonjour Yuwa,
Ton fichier est toujours le même ?
Si oui, tu peux fixer le chemin de ton fichier directement plutôt que d'ouvrir à chaque fois ton fichier.
Deplus tu n'es normalement pas obliger d'ouvrir le fichier distant pour en récupérer les données
https://forum.excel-pratique.com/viewtopic.php?t=34353
il y a plein d'autres sujet avec des exemples, cela devrait te simplifier la vie
Bonjour,
Merci pour votre réponse rapide.
Malheureusement, le fichier ne sera pas toujours le même. C'est pour faire un suivi hebdomadaire. Chaque semaine, je vais avoir un fichier avec la même structure duquel je veux importer quelques valeurs dans un autre fichier.
Je vais regarder les topic que vous m'avez indiqué et je reviens vous dire si j'ai trouvé mon bonheur.
Cordialement,
Du coup je penses que ma solution ne conviendra pas si le classeur change à chaque fois.
Petite question, tes classeurs sources hebdomadaires on tous la même structure interne je suppose mais leurs noms ont ils la même syntaxe ?
Ex: Nomduclasseurhebdo+N°semaine
car on peut fixer facilement le nom du classeur en vba et saisir le numero d'incrementation dans une txtbox qui ira récupérer les valeurs dans le bon classeur.
Si tu as possibilité de fournir un exemple de fichier pour que cela nous parle un peu plus.
Bonjour,
Oui le nom des classeur aura la même syntaxe : "suivi annuel 2018_moisjour" , le jour étant le mardi de la semaine concernée.
Merci du tuyau concernant la possibilité de travailler avec des classeurs fermés. J'ai regardé le code conseillé ( cf fin du message).
Si j'ai bien compris, dans celui-ci, le classeur base de donnés n'est pas variable. Par conséquent, si je change de classeur, je devrais modifier le code de la macro à chaque fois.
Il faudrait donc que j'inclus dans le code une invite qui me permette de sélectionner le classeur souhaité. Est ce qu'il y a une fonction VBA qui permet à l'opérateur d'attribuer une valeur à une variable en cours de code ?
Sinon j'indiquerais dans une cellule le chemin et le code lira cette cellule pour attribuer lavaleur de la variable fichier .
Cordialement,
Ps le code en question
Sub RequeteClasseurFerme_Excel2007()
Dim Cn As ADODB.Connection
Dim Fichier As String
Dim NomFeuille As String, texte_SQL As String
Dim Rst As ADODB.Recordset
'Définit le classeur fermé servant de base de données
Fichier = "C:\Documents and Settings\mimi\dossier\NomClasseur.xlsx"
'Nom de la feuille dans le classeur fermé
NomFeuille = "Feuil1"[/u]
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
'-----------------
'
'... la requête ...
'
'--- Fermeture connexion ---
Cn.Close
Set Cn = Nothing
End Sub
PS : quand je compile mon code incluant le bout de code issu du tuto de silkroad, j'ai un message d'erreur concernant la déclaration de variable Cn As ADODB.Connection
VBA me dit que "erreur de compilation : type défini par l'utilisateur non défini"
Si tu peux nous mettre un petit fichier exemple je regarderai ca pendant le week end
Zut je viens de voir votre message et je ne suis plus au bureau. Je les posterai lundi . C'est très gentil
Cordialement
Bonjour ,
Je mets deux fichier en exemple de mon problème.
Le fichier suivi annuel est le fichier qui intégre toutes les données annuelles, dont une nouvelle version est édité chaque semaine mis à jour avec les données de la dernière semaine. c'est le fichier source.
le fichier semaine présente les données de la semaine, comparé aux données annuelles et mensuelles, et c'est lui qui doit appeler avec la macro évoquée dans ce topic les moyennes annuelles de différents paramètres recalculées chaque semaine par l'autre fichier
Dans un second temps, je souhaiterais créer une macro qui appelle la moyenne mensuelle du mois en cours, en partant du code de la macro appellant les moyennes annuelles.
Cordialement,
bonjour,
j'ai télécharger les fichiers, je regarde cela, j'espères qu'il n' y as pas d'urgence car mon niveau en vba est encore fragile, je suis autodidacte ( et n'ai aucune notion de programmation), donc je risque de revenir vers vous régulièrement...
Dernier point le fichier annuel sera donc renommé toute les semaines avec le format "aaaammjj"?
Parcontre le fichier performance et réactifs lui ne bouge jammais?
et j'ai pas bien compris le second point, mais il sera toujour temps de le clarifier lorsque le premier sera fini
Bonjour,
C'est déjà très gentil, et non ce n'est pas urgentissime.
Au niveau des fichiers, les deux changent de nom chaque semaine :
le performance semaine-modele sera renommé chaque semaine performance semaine aaaammjj. il contiendra les données de la semaine uniquement , et appelant de l'autre fichier les moyennes annuelles et mensuelles de différents paramètres du fichier performance et réactif suivi annuel 2018mmjj.
J'aurais donc chaque semaine deux nouveaux fichiers un suivi annuel réactualisé et un fichier performance semaine.
En effet, je dois recalculer chaque semaine la moyenne annuelle et mensuelle en intégrant les données de la semaine écoulé, pour vérifier que les moyennes annuelles et mensuelles des différents paramètres respectent toujours les limites annuelles qui nous sont imposées, qui sont plus restrictives que les journalières ( sur l'année, on ne doit pas avoir par exemple plus de 1 mg/L de PT alors que en journalier la limite est 1,5 mg/L)
Cordialement,
Rebonjour,
A force de chercher- on trouve.
Je pense que j'ai trouvé pourquoi la macro me redemandait le fichier source à chaque ligne de code. il semblerait que vba n'arrive pas à faire le lien entre
"[wbSource]données'!R21C49" et wbSource=activeworkbook
donc le code me redommandait à chaque ligne de redéfinir wbsource.
Si ça peut servir voici le code qui fonctionne, pour mettre à jour dans un fichier de suivi hebdomadaire les moyennes annuelles et mensuelles recalculée chaque semaine avec les nouvelles données dans un autre fichier
Sub MAJ_Moy_an_perf()
'
' MAJ_Moy_an_perf Macro
' actualisation de la moyenne annuelle avec les données de la semaine
Dim wbFichierUsager As Workbook
Dim strFileName As String
Dim fichier As String
Dim Mois As Integer
Dim ligne As Integer
Dim i As Integer
Dim CS As Workbook
Set wbFichierUsager = ThisWorkbook
' choix du mois
Mois = Application.InputBox("mois", Type:=1)
'import du chemin d'accès dans le classeur
With Application.FileDialog(msoFileDialogFilePicker)
.Show
On Error Resume Next
fichier = .SelectedItems.Item(1)
On Error GoTo 0
End With
If fichier <> "" Then Range("w4").Value = fichier
'
'Sélection du classeur source à partir du chemin précédement insérer dans le classeur
Workbooks.Open fichier
Set CS = ActiveWorkbook 'définit le classeur source CS
Set OS = CS.Worksheets("données") 'définit l'onglet source OS
'Mise à jour moyenne annuelle
wbFichierUsager.Sheets("perf").Activate
Range("A12:B13").Value = OS.Range("D21").Value 'volume
Range("R3").Value = OS.Range("AW21").Value 'pt
Range("F17").Value = OS.Range("I21").Value 'mes
Range("L17").Value = OS.Range("N21").Value 'dco
Range("R17").Value = OS.Range("S21").Value 'dbo5
Range("F32").Value = OS.Range("X21").Value 'nh4
Range("L32").Value = OS.Range("AC21").Value 'ntk
Range("R32").Value = OS.Range("AR21").Value 'ngl
'Mise à jour moyenne mensuelle
If Mois = 1 Then
Set OS = CS.Worksheets("janvier") 'définit l'onglet source OS
Range("A9:B10").Value = OS.Range("D41").Value 'volume
Range("P3").Value = OS.Range("AW41").Value 'pt
Range("P17").Value = OS.Range("S41").Value 'dbo
Range("J17").Value = OS.Range("N41").Value 'dco
Range("D17").Value = OS.Range("I21").Value 'mes
Range("P32").Value = OS.Range("AR41").Value 'ngl
Range("J32").Value = OS.Range("AC41").Value 'ntk
Range("D32").Value = OS.Range("x41").Value 'nh4
ElseIf Mois = 2 Then
Set OS = CS.Worksheets("Février") 'définit l'onglet source OS
Range("A9:B10").Value = OS.Range("D41").Value 'volume
Range("P3").Value = OS.Range("AW41").Value 'pt
Range("P17").Value = OS.Range("S41").Value 'dbo
Range("J17").Value = OS.Range("N41").Value 'dco
Range("D17").Value = OS.Range("I21").Value 'mes
Range("P32").Value = OS.Range("AR41").Value 'ngl
Range("J32").Value = OS.Range("AC41").Value 'ntk
Range("D32").Value = OS.Range("x41").Value 'nh4
ElseIf Mois = 3 Then
Set OS = CS.Worksheets("Mars") 'définit l'onglet source OS
Range("A9:B10").Value = OS.Range("D41").Value 'volume
Range("P3").Value = OS.Range("AW41").Value 'pt
Range("P17").Value = OS.Range("S41").Value 'dbo
Range("J17").Value = OS.Range("N41").Value 'dco
Range("D17").Value = OS.Range("I21").Value 'mes
Range("P32").Value = OS.Range("AR41").Value 'ngl
Range("J32").Value = OS.Range("AC41").Value 'ntk
Range("D32").Value = OS.Range("x41").Value 'nh4
ElseIf Mois = 4 Then
Set OS = CS.Worksheets("Avril") 'définit l'onglet source OS
Range("A9:B10").Value = OS.Range("D41").Value 'volume
Range("P3").Value = OS.Range("AW41").Value 'pt
Range("P17").Value = OS.Range("S41").Value 'dbo
Range("J17").Value = OS.Range("N41").Value 'dco
Range("D17").Value = OS.Range("I21").Value 'mes
Range("P32").Value = OS.Range("AR41").Value 'ngl
Range("J32").Value = OS.Range("AC41").Value 'ntk
Range("D32").Value = OS.Range("x41").Value 'nh4
ElseIf Mois = 5 Then
Set OS = CS.Worksheets("Mai") 'définit l'onglet source OS
Range("A9:B10").Value = OS.Range("D41").Value 'volume
Range("P3").Value = OS.Range("AW41").Value 'pt
Range("P17").Value = OS.Range("S41").Value 'dbo
Range("J17").Value = OS.Range("N41").Value 'dco
Range("D17").Value = OS.Range("I21").Value 'mes
Range("P32").Value = OS.Range("AR41").Value 'ngl
Range("J32").Value = OS.Range("AC41").Value 'ntk
Range("D32").Value = OS.Range("x41").Value 'nh4
ElseIf Mois = 6 Then
Set OS = CS.Worksheets("juin") 'définit l'onglet source OS
Range("A9:B10").Value = OS.Range("D41").Value 'volume
Range("P3").Value = OS.Range("AW41").Value 'pt
Range("P17").Value = OS.Range("S41").Value 'dbo
Range("J17").Value = OS.Range("N41").Value 'dco
Range("D17").Value = OS.Range("I21").Value 'mes
Range("P32").Value = OS.Range("AR41").Value 'ngl
Range("J32").Value = OS.Range("AC41").Value 'ntk
Range("D32").Value = OS.Range("x41").Value 'nh4
ElseIf Mois = 7 Then
Set OS = CS.Worksheets("juillet") 'définit l'onglet source OS
Range("A9:B10").Value = OS.Range("D41").Value 'volume
Range("P3").Value = OS.Range("AW41").Value 'pt
Range("P17").Value = OS.Range("S41").Value 'dbo
Range("J17").Value = OS.Range("N41").Value 'dco
Range("D17").Value = OS.Range("I21").Value 'mes
Range("P32").Value = OS.Range("AR41").Value 'ngl
Range("J32").Value = OS.Range("AC41").Value 'ntk
Range("D32").Value = OS.Range("x41").Value 'nh4
ElseIf Mois = 8 Then
Set OS = CS.Worksheets("aout") 'définit l'onglet source OS
Range("A9:B10").Value = OS.Range("D41").Value 'volume
Range("P3").Value = OS.Range("AW41").Value 'pt
Range("P17").Value = OS.Range("S41").Value 'dbo
Range("J17").Value = OS.Range("N41").Value 'dco
Range("D17").Value = OS.Range("I21").Value 'mes
Range("P32").Value = OS.Range("AR41").Value 'ngl
Range("J32").Value = OS.Range("AC41").Value 'ntk
Range("D32").Value = OS.Range("x41").Value 'nh4
ElseIf Mois = 9 Then
Set OS = CS.Worksheets("septembre") 'définit l'onglet source OS
Range("A9:B10").Value = OS.Range("D41").Value 'volume
Range("P3").Value = OS.Range("AW41").Value 'pt
Range("P17").Value = OS.Range("S41").Value 'dbo
Range("J17").Value = OS.Range("N41").Value 'dco
Range("D17").Value = OS.Range("I21").Value 'mes
Range("P32").Value = OS.Range("AR41").Value 'ngl
Range("J32").Value = OS.Range("AC41").Value 'ntk
Range("D32").Value = OS.Range("x41").Value 'nh4
ElseIf Mois = 10 Then
Set OS = CS.Worksheets("octobre") 'définit l'onglet source OS
Range("A9:B10").Value = OS.Range("D41").Value 'volume
Range("P3").Value = OS.Range("AW41").Value 'pt
Range("P17").Value = OS.Range("S41").Value 'dbo
Range("J17").Value = OS.Range("N41").Value 'dco
Range("D17").Value = OS.Range("I21").Value 'mes
Range("P32").Value = OS.Range("AR41").Value 'ngl
Range("J32").Value = OS.Range("AC41").Value 'ntk
Range("D32").Value = OS.Range("x41").Value 'nh4
ElseIf Mois = 11 Then
Set OS = CS.Worksheets("novembre") 'définit l'onglet source OS
Range("A9:B10").Value = OS.Range("D41").Value 'volume
Range("P3").Value = OS.Range("AW41").Value 'pt
Range("P17").Value = OS.Range("S41").Value 'dbo
Range("J17").Value = OS.Range("N41").Value 'dco
Range("D17").Value = OS.Range("I21").Value 'mes
Range("P32").Value = OS.Range("AR41").Value 'ngl
Range("J32").Value = OS.Range("AC41").Value 'ntk
Range("D32").Value = OS.Range("x41").Value 'nh4
ElseIf Mois = 12 Then
Set OS = CS.Worksheets("décembre") 'définit l'onglet source OS
Range("A9:B10").Value = OS.Range("D41").Value 'volume
Range("P3").Value = OS.Range("AW41").Value 'pt
Range("P17").Value = OS.Range("S41").Value 'dbo
Range("J17").Value = OS.Range("N41").Value 'dco
Range("D17").Value = OS.Range("I21").Value 'mes
Range("P32").Value = OS.Range("AR41").Value 'ngl
Range("J32").Value = OS.Range("AC41").Value 'ntk
Range("D32").Value = OS.Range("x41").Value 'nh4
Else
Exit Sub
End If
End Sub
Merci eldlede pour tes conseils
Bonjour
personnellement je remplacerais toute la partie du code traitant de la Mise à jour moyenne mensuelle
car celui (ce qui evite de répéter 12 fois le même code : range(.....
If Mois = 1 Then
Set OS = CS.Worksheets("janvier") 'définit l'onglet source OS
ElseIf Mois = 2 Then Set OS = CS.Worksheets("Février") 'définit l'onglet source Os
ElseIf Mois = 3 Then Set OS = CS.Worksheets("Mars") 'définit l'onglet source OS
ElseIf Mois = 4 Then Set OS = CS.Worksheets("Avril") 'définit l'onglet source OS
ElseIf Mois = 5 Then Set OS = CS.Worksheets("Mai") 'définit l'onglet source OS
ElseIf Mois = 6 Then Set OS = CS.Worksheets("juin") 'définit l'onglet source OS
ElseIf Mois = 7 Then Set OS = CS.Worksheets("juillet") 'définit l'onglet source OS
ElseIf Mois = 8 Then Set OS = CS.Worksheets("aout") 'définit l'onglet source OS
ElseIf Mois = 9 Then Set OS = CS.Worksheets("septembre") 'définit l'onglet source OS
ElseIf Mois = 10 Then Set OS = CS.Worksheets("octobre") 'définit l'onglet source OS
ElseIf Mois = 11 Then Set OS = CS.Worksheets("novembre") 'définit l'onglet source OS
ElseIf Mois = 12 Then Set OS = CS.Worksheets("décembre") 'définit l'onglet source OS
Else: Exit Sub
End If
Range("A9:B10").Value = OS.Range("D41").Value 'volume
Range("P3").Value = OS.Range("AW41").Value 'pt
Range("P17").Value = OS.Range("S41").Value 'dbo
Range("J17").Value = OS.Range("N41").Value 'dco
Range("D17").Value = OS.Range("I21").Value 'mes
Range("P32").Value = OS.Range("AR41").Value 'ngl
Range("J32").Value = OS.Range("AC41").Value 'ntk
Range("D32").Value = OS.Range("x41").Value 'nh4
ou mieux encore (enfin je trouve....)
Select Case mois
Case 1: Set OS = CS.Worksheets("janvier") 'définit l'onglet source OS
Case 2: Set OS = CS.Worksheets("Février") 'définit l'onglet source Os
Case 3: Set OS = CS.Worksheets("Mars") 'définit l'onglet source OS
Case 4: Set OS = CS.Worksheets("Avril") 'définit l'onglet source OS
Case 5: Set OS = CS.Worksheets("Mai") 'définit l'onglet source OS
Case 6: Set OS = CS.Worksheets("juin") 'définit l'onglet source OS
Case 7: Set OS = CS.Worksheets("juillet") 'définit l'onglet source OS
Case 8: Set OS = CS.Worksheets("aout") 'définit l'onglet source OS
Case 9: Set OS = CS.Worksheets("septembre") 'définit l'onglet source OS
Case 10: Set OS = CS.Worksheets("octobre") 'définit l'onglet source OS
Case 11: Set OS = CS.Worksheets("novembre") 'définit l'onglet source OS
Case 12: Set OS = CS.Worksheets("décembre") 'définit l'onglet source OS
Case Else: Exit Sub
End Select
Range("A9:B10").Value = OS.Range("D41").Value 'volume
Range("P3").Value = OS.Range("AW41").Value 'pt
Range("P17").Value = OS.Range("S41").Value 'dbo
Range("J17").Value = OS.Range("N41").Value 'dco
Range("D17").Value = OS.Range("I21").Value 'mes
Range("P32").Value = OS.Range("AR41").Value 'ngl
Range("J32").Value = OS.Range("AC41").Value 'ntk
Range("D32").Value = OS.Range("x41").Value 'nh4
Fred
bien vu , merci de l'astuce
n'oubli pas si le problème est résolu :
Fred