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