Valeurs de cellules d'une table comme variable pour une fonction VBA access [résolu]

kraenys · 7 Octobre 2013

Bonjour le forum,

Voici mon problème, je cherche à adapter un bout de code pour qu'il puisse traiter plusieurs cas simultanément.

Jusqu'ici, voici à quoi le code ressemble:

Code:

Function ExportXLS_automatique()


'   Objets Access
Dim dbsBase As DAO.Database
Dim rstRequete As DAO.Recordset
Dim fld As DAO.Field

'   Objets Excel
Dim appexcel As Object
Dim wbkRequete As Object
Dim wksRequete As Object


'   Variables de boucles
Dim intLig As Long
Dim intCol As Long

'   Création du classeur Excel
Set appexcel = CreateObject("Excel.application")
Set wbkRequete = appexcel.Workbooks.Add
Set wksRequete = appexcel.ActiveSheet
appexcel.Visible = True

'   Ouverture de la table
Set dbsBase = DBEngine.OpenDatabase("C:\Users\F54174\Documents\DUMP_EAM.accdb")
Set rstRequete = dbsBase.OpenRecordset("Export_access", dbOpenDynaset)

'   Mise à jour de la feuille active
With wksRequete


'  En-têtes de colonnes renseignées à partir des noms de champs
   intCol = 1
   For Each fld In rstRequete.Fields
      .Cells(1, intCol).Value = fld.Name
      intCol = intCol + 1
   Next fld
   
'   Parcours des enregistrements
'   Ajout d’une ligne par enregistrement
   intLig = 2
   Do While Not rstRequete.EOF
      intCol = 1
      For Each fld In rstRequete.Fields
         .Cells(intLig, intCol).Value = rstRequete(intCol - 1)
         intCol = intCol + 1
      Next fld
      intLig = intLig + 1
      rstRequete.MoveNext
   Loop
'   Affectation d’un nom à la feuille Excel
   .Name = "Export_access"
End With

'   Fermeture des objets Access
rstRequete.Close
dbsBase.Close

'   Activation du classeur dans Excel
appexcel.ActiveWindow.Activate

appexcel.Visible = True
'Call Macro_xlsm
appexcel.Run ("modèle.xlsm!Macro_debut")
wbkRequete.SaveAs FileName:= _
        "C:\Users\F54174\Documents\" & "Export_access", FileFormat _
        :=xlOpenXMLWorkbookMacroEnabled 'Puis on sauvegarde le fichier complet
wbkRequete.Close
appexcel.ActiveWorkbook.Close
appexcel.Application.Quit
Set appexcel = Nothing
End Function

J'ai une table qui contient des suites de caractères représentant le nom de requêtes à traiter. ( j'ai exporté cette table sous XLD1.xlsx mais ils agit bien d'une table access à la base)
Le but final est que chacune des requêtes inscrites ici soient traitées dans ma fonction ExportXLS_automatique

Il faudrait donc remplacer

Code:

 Set rstRequete = dbsBase.OpenRecordset("Export_access", dbOpenDynaset)

par

Code:

 Set rstRequete = dbsBase.OpenRecordset(Une variable contenant chacune des requetes contenues dans la table access (ici extraite dans XLD1.xlsx),dbOpenDynaset)

et que l'on sauvegarde le résultat avec le nom de la variable et non "Export_access" dans

Code:

 wbkRequete.SaveAs FileName:= _
        "C:\Users\F54174\Documents\" & "Export_access", FileFormat _
        :=xlOpenXMLWorkbookMacroEnabled 'Puis on sauvegarde le fichier complet

comme c'est actuellement.

Voilà, j'espère ne pas être trop brouillon dans mes explications...

Je ne peux à mon avis pas fournir plus de documents, le fichier sur lequel je travaille dépassant le Go.

Merci d'avance de votre aide, si il y a besoin de précisions ou d'autre documents, je ferai tout mon possible pour fournir de quoi vous aider à m'aider.

Un dernier détail, la fonction présentée au début fonctionne parfaitement quand il sagit de "cas par cas", c'est l'adaptation avec variable pour traiter plusieurs requêtes l'une après l'autre qui pose problème.

Bien à vous.

Kraenys

kraenys · 7 Octobre 2013

Re : Valeurs de cellules d'une table comme variable pour une fonction VBA access

Rebonjour le forum,
j'ai avancé, peut-être que le point pour lequel je bloque désormais vous est familier et vous savez comment le résoudre?

Voici tout d'abord le code actuel:

Code:

Function ExportXLS_automatique()


'   Objets Access
Dim dbsBase As DAO.Database
Dim rstRequete As DAO.Recordset
Dim rstStock As DAO.Recordset
Dim fld As DAO.Field
Dim fld1 As DAO.Field

'   Objets Excel
Dim appexcel As Object
Dim wbkRequete As Object
Dim wksRequete As Object


'   Variables de boucles
Dim intLig As Long
Dim intLig1 As Long
Dim intCol As Long
Dim intCol1 As Long

'   Création du classeur Excel
Set appexcel = CreateObject("Excel.application")
Set wbkRequete = appexcel.Workbooks.Add
Set wksRequete = appexcel.ActiveSheet
Set wksStock = appexcel.ActiveSheet
appexcel.Visible = True

'   Ouverture de la table
Set dbsBase = DBEngine.OpenDatabase("C:\Users\F54174\Documents\DUMP_EAM.accdb")
Set rstStock = dbsBase.OpenRecordset("Requêtes pour ExportXLS_test", dbOpenDynaset)

With wksStock

'   Parcours des enregistrements
'   Ajout d’une ligne par enregistrement
   intLig1 = 2
   Do While Not rstStock.EOF
      intCol1 = 1
      For Each fld1 In rstStock.Fields
         .Cells(intLig1, intCol1).Value = rstStock(intCol1 - 1)
         'intCol = intCol + 1
      Next fld1


Set rstRequete = dbsBase.OpenRecordset(rstStock(intCol1 - 1).Value, dbOpenDynaset)
'   Mise à jour de la feuille active
With wksRequete


'  En-têtes de colonnes renseignées à partir des noms de champs
   intCol = 1
   For Each fld In rstRequete.Fields
      .Cells(1, intCol).Value = fld.Name
      intCol = intCol + 1
   Next fld
   
'   Parcours des enregistrements
'   Ajout d’une ligne par enregistrement
   intLig = 2
   Do While Not rstRequete.EOF
      intCol = 1
      For Each fld In rstRequete.Fields
         .Cells(intLig, intCol).Value = rstRequete(intCol - 1)
         intCol = intCol + 1
      Next fld
      intLig = intLig + 1
      rstRequete.MoveNext
   Loop
'   Affectation d’un nom à la feuille Excel
   .Name = "Export_access"
End With

'   Fermeture des objets Access
rstRequete.Close
dbsBase.Close

'   Activation du classeur dans Excel
appexcel.ActiveWindow.Activate

appexcel.Visible = True
'Call Macro_xlsm
appexcel.Run ("modèle.xlsm!Macro_debut")
wbkRequete.SaveAs FileName:= _
        "C:\Users\F54174\Documents\" & "nom test", FileFormat _
        :=xlOpenXMLWorkbookMacroEnabled 'Puis on sauvegarde le fichier complet
wbkRequete.Close
appexcel.ActiveWorkbook.Close
appexcel.Application.Quit

        
intLig1 = intLig1 + 1
'rstStock.MoveNext
Loop
End With
Set appexcel = Nothing
End Function

La première execution du code se passe désormais sans souci, j'accède à la bonne requête que je dois travailler avec la fonction (elle correspond à rstStock(intCol1 - 1).Value). Lors de la première boucle, elle vaut la première ligne du fichier XLD1.xlsx

Lorsque j'arrive à la deuxième Exécution, j'ai une erreur à

Code:

    Do While Not rstStock.EOF

On me dit que l'objet est incorrect ou n'est plus défini. (Erreur 3420) Et en effet, lorsque je met un espion dessus, il n'a aucune valeur.

Voilà où j'en suis actuellement, si vous avez une idée qui pourrait me faire avancer, je suis preneur, vous m'aideriez beaucoup.

Bien à vous.

Kraenys

Guest · 7 Octobre 2013

Re : Valeurs de cellules d'une table comme variable pour une fonction VBA access

Re,

Peut-être qu'en décommentant la ligne : 'rstStock.MoveNext

A+

kraenys · 7 Octobre 2013

Re : Valeurs de cellules d'une table comme variable pour une fonction VBA access

Bonjour Hasco.

Merci de votre réponse.

J'ai décommenté la ligne

Code:

 'rstStock.MoveNext

et lancé la fonction pour tester. Le message d'erreur "l'objet est incorrect ou n'est plus défini. (Erreur 3420) " se met désormais sur cette ligne là.

le rstStock semble déjà ne plus être défini et renvoie dejà une valeur nulle.

Avez-vous une idée de comment parer celà? Un autre

Code:

With wksStock

après appexcel.application.quit permettrait-il de redéfinir rstStock? Ou alors peut-être de remettre directement à la fin de appexcel.application.quit

Code:

 Set rstStock = dbsBase.OpenRecordset("Requêtes pour ExportXLS_test", dbOpenDynaset)

?

Merci de votre aide.

Kraenys

Guest · 7 Octobre 2013

Re : Valeurs de cellules d'une table comme variable pour une fonction VBA access

Re,

A vu de nez puisque aucun support.

Ben oui puisqu'il semble que les lignes ci-dessous sont placées après la destruction des objets Database.

Code:

intLig1 = intLig1 + 1
'rstStock.MoveNext
Loop

Regarde l'aide excel sur la méthode .CopyFromRecordset de l'objet Range:

Après la boucle affichant les noms de champs mettre :

Code:

.Cells(2,1).copyFromRecordset rstRequete

Et plus besoin de la boucle sur les lignes! (Do While Not rstRequete.EOF)

A+

kraenys · 7 Octobre 2013

Re : Valeurs de cellules d'une table comme variable pour une fonction VBA access

Merci de votre réponse, je vais essayer ça dès demain sur mon poste. Merci de votre aide et désolé de ne pas pouvoir fournir de support plus concret.

Bien à vous.

Kraenys

kraenys · 8 Octobre 2013

Re : Valeurs de cellules d'une table comme variable pour une fonction VBA access

Bonjour Hasco, Bonjour le forum.

Je pense qu'on tient le bon fil.

Voici mon code une fois modifié:

Code:

Function ExportXLS_automatique()


'   Objets Access
Dim dbsBase As DAO.Database
Dim rstRequete As DAO.Recordset
Dim rstStock As DAO.Recordset
Dim fld As DAO.Field
Dim fld1 As DAO.Field

'   Objets Excel
Dim appexcel As Object
Dim wbkRequete As Object
Dim wksRequete As Object


'   Variables de boucles
Dim intLig As Long
Dim intLig1 As Long
Dim intCol As Long
Dim intCol1 As Long

'   Création du classeur Excel
Set appexcel = CreateObject("Excel.application")
Set wbkRequete = appexcel.Workbooks.Add
Set wksRequete = appexcel.ActiveSheet
Set wksStock = appexcel.ActiveSheet
appexcel.Visible = True

'   Ouverture de la table
Set dbsBase = DBEngine.OpenDatabase("C:\Users\F54174\Documents\DUMP_EAM.accdb")
Set rstStock = dbsBase.OpenRecordset("Requêtes pour ExportXLS_test", dbOpenDynaset)


With wksStock

    .Cells(2, 1).CopyFromRecordset rstStock
'   Parcours des enregistrements
'   Ajout d’une ligne par enregistrement
   'intLig1 = 1
   'Do While Not rstStock.EOF
      intCol1 = 1
   '   For Each fld1 In rstStock.Fields
   '   .Cells(intLig1, intCol1).Value = rstStock(intCol1 - 1)
   '      intCol = intCol + 1
   '   Next fld1
   

Set rstRequete = dbsBase.OpenRecordset(rstStock(intCol1 - 1).Value, dbOpenDynaset)
'   Mise à jour de la feuille active
With wksRequete


'  En-têtes de colonnes renseignées à partir des noms de champs
   intCol = 1
   For Each fld In rstRequete.Fields
      .Cells(1, intCol).Value = fld.Name
      intCol = intCol + 1
   Next fld
   

    .Cells(2, 1).CopyFromRecordset rstRequete
'   Affectation d’un nom à la feuille Excel
   .Name = "Export_access"
End With

'   Fermeture des objets Access
rstRequete.Close
dbsBase.Close

'   Activation du classeur dans Excel
appexcel.ActiveWindow.Activate

appexcel.Visible = True

appexcel.Run ("modèle.xlsm!Macro_debut")
wbkRequete.SaveAs FileName:= _
        "C:\Users\F54174\Documents\" & "test", FileFormat _
        :=xlOpenXMLWorkbookMacroEnabled 'Puis on sauvegarde le fichier complet
wbkRequete.Close
appexcel.ActiveWorkbook.Close
appexcel.Application.Quit


'wbkStock.Open
'intLig1 = intLig1 + 1
'Loop
End With
Set appexcel = Nothing
End Function

Lors de l'exécution de celui-ci, on obtient une erreur 3021 "Aucun enregistrement en cours" sur la ligne

Code:

Set rstRequete = dbsBase.OpenRecordset(rstStock(intCol1 - 1).Value, dbOpenDynaset)

Il faudrait modifier (rstStock(intCol1 - 1).Value par chacune des cellules créées par .Cells(2, 1).CopyFromRecordset rstStock
je me demande si ça ne suffirait pas pour la suite?

Sinon, j'ai pu faire quelques test sur la deuxième partie du code qui récupère les requêtes plus complètes, le code marche nickel, et est même plus rapide qu'avant, c'est dejà super!

Merci encore de votre aide.

Bien à vous

Kraenys

Guest · 8 Octobre 2013

Re : Valeurs de cellules d'une table comme variable pour une fonction VBA access

Bonjour,

Commencer d'abord par nettoyer la macro, enlever les lignes qui ne servent plus et ensuite traiter les choses les une après les autres. Je ne peux que voir 'en gros' ce que tu veux faire. Sois plus précis et méthodique.
Tu veux mettre quoi, où, quand, comment. Prends un papier et répond pour toi-même à ces questions.

A+

kraenys · 8 Octobre 2013

Re : Valeurs de cellules d'une table comme variable pour une fonction VBA access

Je préfère à l'heure actuelle éviter d'enlever les lignes passées sous commentaire, ça me permet plus facilement de revenir un peu en arrière si le résultat ne me conviens pas.

Ensuite, je vais appliquer ce que vous me conseillez de faire et reviens vers vous avec une macro épurée et mes objectifs préçis.

Bien à vous.

Kraenys

Guest · 8 Octobre 2013

Re : Valeurs de cellules d'une table comme variable pour une fonction VBA access

Bonjour,

Voir dans zip joint un exemple avec MsAdodb plutôt que DAO qui ne s'utilise guère que dans Access lui-même.
Dans ce zip, une dbb avec un table clients venant de l'exemple Microsoft Les Comptoirs.
A+

kraenys · 8 Octobre 2013

Re : Valeurs de cellules d'une table comme variable pour une fonction VBA access

Re Hasco.

Mon erreur était toute bête, en me reposant les questions j'ai fini par tomber dessus..

Lorsque je passais par Loop qui me faisait traiter mon second cas de requête, j'exécutais:

appexcel.ActiveWorkbook.Close
appexcel.Application.Quit

Je fermais tout, et donc Access reconnaissait plus rien, il n'avait plus de classeur sur lequel travailler ni de table access objet du travail... Il fallait donc redéfinir avant Loop:
' ReCréation du classeur Excel
Set appexcel = CreateObject("Excel.application")
Set wbkRequete = appexcel.Workbooks.Add
Set wksRequete = appexcel.ActiveSheet
Set wksStock = appexcel.ActiveSheet
appexcel.Visible = True

' RéOuverture de la table
Set dbsBase = DBEngine.OpenDatabase("C:\Users\F54174\Documents\DUMP_EAM.accdb")
Set rstStock = dbsBase.OpenRecordset("Requêtes pour ExportXLS_test", dbOpenDynaset)

Et surtout redéfinir wksRequete.Cells dans Set rstRequete = dbsBase.OpenRecordset(wksRequete.Cells(intLig1, 1), dbOpenDynaset). Sinon, access ne sait pas ce qu'est "Cells". Access ne reconnaissant plus le code Excel de manière générale.

Il sagissait en fait simplement d'un manque de précision dans mon code de "Sur quoi je travaille", "à partir de quoi"(surtout ça)...

Merci de votre aide, elle m'a étée précieuse.

Bien à vous.

Kraenys

Guest · 8 Octobre 2013

Re : Valeurs de cellules d'une table comme variable pour une fonction VBA access [rés

Re,

Bravo!

A+

Valeurs de cellules d'une table comme variable pour une fonction VBA access [résolu]

kraenys

XLDnaute Junior

Pièces jointes

kraenys

XLDnaute Junior

Guest

Guest

kraenys

XLDnaute Junior

Guest

Guest

kraenys

XLDnaute Junior

kraenys

XLDnaute Junior

Guest

Guest

kraenys

XLDnaute Junior

Guest

Guest

kraenys

XLDnaute Junior

Guest

Guest

Discussions similaires

Valeurs de cellules d'une table comme variable pour une fonction VBA access [résolu]

kraenys

XLDnaute Junior

Pièces jointes

kraenys

XLDnaute Junior

Guest

Guest

kraenys

XLDnaute Junior

Guest

Guest

kraenys

XLDnaute Junior

kraenys

XLDnaute Junior

Guest

Guest

kraenys

XLDnaute Junior

Guest

Guest

kraenys

XLDnaute Junior

Guest

Guest

Discussions similaires

Privacy & Transparency

Privacy & Transparency