[VBA] Bouclage impossible sur une requête ?

alongra · Janvier 22, 2006, 5:07

Bonjour tout le monde !

Voilà j’ai créé sous Access un formulaire qui lance une requête dans un fichier Excel. Le fichier Excel reçoit donc les données provenant de la requête. J’ai mis ce code du lancement de la requête dans une boucle FOR car la requête doit s’éxcuter 72 fois, dans 72 fichier Excel différent. Voici le code du lancement de ma requête :

    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=MS Access Database;DBQ=D:\Listing France 2006.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
        , Destination:=Range("A6"))
        .CommandText = Array( _
        "SELECT ReqTest.CodePointDeVente, ReqTest.NomClient, ReqTest.CP, ReqTest.AdresseClient" & Chr(13) & "" & Chr(10) & "FROM `D:\Listing France 2006`.ReqTest ReqTest" _
        )
        .Name = "Lancer la requête à partir de MS Access Database"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

Le premier tour de boucle marche nickel, mais au second j’ai le message “La méthode ‘Range’ de l’objet ‘_Global’ à échoué” En faite l’erreur vient de la ligne :

Destination:=Range("A6")

Ca fait 2 jours que je suis sur ce problème je ne vois pas du tout comment faire.

Si quelqu’un à une idée je suis preneur car là je vois vraiment pas comment faire pour que ça marche.

Un grand merci d’avance !!

deltree · Janvier 23, 2006, 10:37

Ce serait pas “:=” au lieu de “=”? ou l’inverse (command =).
Le passage d’arguments nommés, je suis pas fan, et les “With” non plus, dans ta commande, on ne sait plus “With quoi”

Edit: autant pour moi, j’ai confondu la fin d’appel de fonction avec le début des instructions, décidémment, c’est pas mon truc!
Essaie quand même d’utiliser un variable plutôt que with, des fois qu’active sheet ne soit pas celle que tu croit, et essaye aussi de prendre le range dans une variable avant l’appel, de le tracer puis de faire ton appel à “add”. Histoire de bien isoler le bug.

alongra · Janvier 23, 2006, 10:15

Salut deltree et merci beacoup d’avoir répondu !!!!

Alors, en faite le code que tu vois je l’ai généré en faisant une macro sous excel que j’ai copier, puis coller dans ma boucle pour de mon formulaire.

J’ai allégé ce code afin de trouver le bug, voici mon nouveau code :

alongra · Janvier 23, 2006, 10:21

oups… j’ai cliqué sur le mauvais bouton (le boulet koi !!), donc je disais :

   
cellule = "A6"
sqlstring = "SELECT Clients.CodePointDeVente, Clients.NomClient, Clients.CP, Clients.AdresseClient FROM Clients"
   connstring = "ODBC;DSN=MS Access Database"
   
   With ActiveSheet.QueryTables.Add(Connection:=connstring, _
    Destination:=ActiveSheet.Range(cellule), SQL:=sqlstring)
    .Refresh
    End With

quand je met

ActiveSheet.Range(cellule)

J’ai “Variable Objet ou variable de bloc With non définie” et sans le ActiveSheet devant j’ai toujours le même message (voir mon premier message).

Tu m’as conseillé de ne pas utilisé with, mais comment je peux faire car quand je l’enlève Access me dit qu’il manque un ‘=’ après mon activeSheet.queryTables.add .

Sinon que penses tu de tout cela ?

En tout cas un grand merci pour ta participation, c’est fort aimable !

@+

deltree · Janvier 24, 2006, 10:00

Le “With” sert à faire référence à une variable sans la nommer, on peut très bien s’en passer, parce que pour moi, ça rend le code illisible.


with toto
 .method1 "zrz"
 .method2 "zzer"
end with

équivaut à


toto.method1 "zrz"
toto.method2 "zzer"

Ton erreur initiale vient surement du fait qu’il ne trouve pas la la cellule, tu peux la mettre dans une variable, plutôt que le nom de la cellule.
bilan, ça donne:

   
dim cellule
set cellule = ActiveWorkbook.Worksheets("Nom de ta feuille").Range("A6")
sqlstring = "SELECT Clients.CodePointDeVente, Clients.NomClient, Clients.CP, Clients.AdresseClient FROM Clients"
   connstring = "ODBC;DSN=MS Access Database"
   dim qTable
   set qTable = ActiveSheet.QueryTables.Add(Connection:=connstring, _
    Destination:=cellule, SQL:=sqlstring)
    qTable.Refresh

Donc j’ai ajouté les objets cellule et qTable, qui s’affecte avec set obj=…
et on voit mieux que dans le bloc with, la méthode refresh était appelée sur un objet renvoyé par QueryTables.add

et dans ton premier post tu appelait plus de méthode sur cet objet avant le refresh, ça donnerait:


       qTable.Name = "Lancer la requête à partir de MS Access Database"
       qTable.FieldNames = False
       qTable.RowNumbers = False
       qTable.FillAdjacentFormulas = False
       qTable.PreserveFormatting = True
       qTable.RefreshOnFileOpen = False
       qTable.BackgroundQuery = True
       qTable.RefreshStyle = xlInsertDeleteCells
       qTable.SavePassword = True
       qTable.SaveData = True
       qTable.AdjustColumnWidth = True
       qTable.RefreshPeriod = 0
       qTable.PreserveColumnInfo = True
       qTable.Refresh BackgroundQuery:=False

voila, je préfère répéter la variable à chaque fois plutôt que d’utiliser un With, pour moi c’est plus clair, et la variable est dispo dans le debugger.

J’espère avoir réussi à en convaincre quelques un que cet instruction With est une horreur; le pire, c’est qu’il s’en sont inspiré dans la nouvelle version de Java (les import static) mais ceci est une autre histoire.

alongra · Janvier 29, 2006, 9:22

Saluuuut !!!

Désolé de répondre que maintenant, je sors de maladie donc je ne suis pas allé de la semaine sur mon ordi.

Bon alors, tout d’abord je tenais à te remercier pour ta réponse, c’est très sympas !

là je pense que j’y suis presque (ui, ça marche pas encore top top) car quand j’éxécute j’ai le message “variable objet non définie” au passage de la deuxième boucle (la première passe toujours très bien, et pourtant ton code me parait vraiment mieux que le miens.

En faite, d’après mes nombreux test je suis presque sur que l’erreur vient de l’ActiveWorkbook, et c’est cette variable qui fait défaut, mais je sais pas comment l’initialiser au début de chaque boucle, je t’ai mis le code de ma boucle pour que tu comprennent mieux l’truc. En tout cas c’est vraiment bizare comme histoire…

For I = 1 To 2 'max

    '######## OUVERTURE DU FICHIER COMMISSION.XLS ############
    'Déclaration des variables
    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("D:\Commission\Commission.xls")
    'wsExcel correspond à la première feuille du fichier
    Set wsExcel = wbExcel.Worksheets(1)
    appExcel.Visible = True
    appExcel.Cursor = xlWait

    
    '###### FIN CREATION ########

    requete = "SELECT NomReprésentant FROM Représentant WHERE Numéro = " & I
    Set rst = db.OpenRecordset(requete)

    '############## DEBUT DE LA REQUETE ##############
    Dim cellule
    Set cellule = appExcel.ActiveWorkbook.Worksheets("sheet1").Range("A6")
    sqlstring = "SELECT Clients.CodePointDeVente, Clients.NomClient, Clients.CP, Clients.AdresseClient FROM Clients"
    connstring = "ODBC;DSN=MS Access Database"
    Dim qTable
    Set qTable = ActiveSheet.QueryTables.Add(Connection:=connstring, _
    Destination:=cellule, SQL:=sqlstring)
    qTable.Refresh

    '############################## FIN DE LA REQUETE ############################################
    
    
    '################ DEBUT DE LA PAUSE DU PROGRAMME ############################################
    Call Tempo
    '################ FIN DE LA PAUSE ###########################################################
    
    'requete1 = "SELECT F10 FROM a_Strator WHERE F10 =" & rst
    'Set rst = db.OpenRecordset(requete1)
    
    res = rst("NomReprésentant")
    wsExcel.Range("B3").Value = res

    'SAUVERGADE DES FICHIERS
    wsExcel.SaveAs ("D:\Commission\" & res & ".xls")
    Call Tempo1
    'wsExcel.SaveAs ("D:\Commission\" & res & ".xls")
    'wbExcel.Close 'Fermeture du classeur Excel
    wbExcel.Application.Quit
    appExcel.Application.Quit
    'appExcel.Quit 'Fermeture de l'application Excel

    'Désallocation mémoire
    Set wsExcel = Nothing
    Set wbExcel = Nothing
    Set appExcel = Nothing
    Set cellule = Nothing
    Set qTable = Nothing

Next

deltree · Janvier 30, 2006, 10:43

Salut,
prompt rétablissement alors.
Pour le workbook, c’est comme pour la Worksheet, plutôt que de prendre l’active, affecte le explicitement.
soit le workbook que tu ouvre précedemment est le bon, alors tu réutilise ta variable:


Set cellule = wbExcel.Worksheets("sheet1").Range("A6")

soit c’est un autre, alors tu le retrouve par le nom (sans le path):


    Dim wBook As Workbook
    
    Set wBook = Workbooks("test.xls")
    Set cellule = wBook.Worksheets("sheet1").Range("A6")

alongra · Janvier 30, 2006, 8:24

Chaluuut !!

Je tiens de nouveau à te remercier pour ton aides démesurée !!

Enfin sa marche !!! En faite l’erreur venait pas de tout de où je pensais. En effet, la variable qui déconnait était appExcel, je la déclarait dans la boucle (quel idiot…) depuis que j’ai mis la déclaration avant la boucle, tout marche.

Bref, merci encore !!

deltree · Janvier 31, 2006, 10:06

C’est cool alors :super: