Utilisation de ADO avec des cellules contenant beaucoup d'informations

[siocnarf]

Bonsoir,

Je travaille sur une problématique où je dois fusionner plusieurs fichiers.

Lorsque j'arrive sur:

FeuilleDest.Range("A" & Li).CopyFromRecordset rsData

La copie commence puis s'arrête avec une erreur:
Erreurs d'exécution -2147467259(80004005)
La méthode 'CopyFromRecordset' de l'objet 'Range' a échoué.
Je remarque que je m'arrête à la ligne 30 colonne L systématiquement alors que je devrais avoir 3000 lignes. L'information dans cette cellule a 125 lignes de long.

Je crois que mon problème est qu'il y a trop d'informations dans la cellule à copier mais je ne peut pas la raccourcir. Comment pourrais-je faire en sorte que cela fonctionne.

Sub TestConso()
Dim Fich1$, Fich2$, Source1$, Source2$, Cible$

  Fich1 = "c:\temp\a.xlsx"
  Fich2 = "c:\temp\b.xlsx"
  Source1 = "Feuil2"
  Source2 = "Feuil2"
  Cible = "Feuil2"
  ConsoDatas Fich1, Source1, Cible
  ConsoDatas Fich2, Source1, Cible
End Sub

Public Sub ConsoDatas(NomFichier$, FeuilleSource$, FeuilleCible$)
'Va chercher dans le classeur NomFichier (sans l'ouvrir) les données
'de la feuille FeuilleSource et les copie dans la feuille FeuilleCible
'du classeur actif, à la suite des données (éventuellement) déjà présentes.
'(La ligne d'entêtes de FeuilleSource n'est pas importée)
'inspiré de Rob Bovey, mpep
'nécessite une référence à la librairie
'Microsoft ActiveX Data Object 2.x Library
'http://www.excel-downloads.com/forum/35896-activer-la-reference-microsoft-activex-data-object.html
'ThisWorkbook.VBProject.References.AddFromFile ("C:\Program Files\Common Files\System\ado\msado20.tlb ")
'http://blogs.codes-sources.com/coq/archive/2007/09/21/classeurs-excel-via-oledb-et-pour-les-versions-2007-xlsb-xlsm-xlsx.aspx
'http://frederic.sigonneau.free.fr/code/Ado/ADOConsolidation.txt
Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String
Dim Li&, FeuilleDest

'Version de Excel
'"12"= Excel 2007
'"11"= Excel 2003
StrVersion = Application.Version

    If StrVersion < 12 Then
        'Pre-2007
        ''' Crée la chaîne de connexion
        'Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties="Excel 8.0;HDR=YES;IMEX=1";
        szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & NomFichier & ";" & _
            "Extended Properties=Excel 8.0;"
    Else
        ' À partir de 2007
        'Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Excel 12.0;HDR=YES;IMEX=1";
        szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & NomFichier & ";" & _
            "Extended Properties=Excel 12.0;"

    End If

    ' La requête est basée sur le nom de la feuille. Ce nom
    ' doit se terminer par un $ et doit être entouré de crochets droits.
    szSQL = "SELECT * FROM [" & FeuilleSource & "$];"

    Set rsData = New ADODB.Recordset
    rsData.Open szSQL, szConnect, adOpenForwardOnly, _
        adLockReadOnly, adCmdText

    
    

    'où envoyer les données :
    Workbooks("consol.xlsx").Activate
    Set FeuilleDest = ActiveWorkbook.Sheets(FeuilleCible)
    'Quelle est la dernière ligne de la feuille?
    Derniereligne = Rows.Count
    Li = FeuilleDest.Range("A" & Derniereligne).End(xlUp).Row + 1
    If Li = 2 Then Li = 1 'Je souhaite que la copie se fasse à partir de la première ligne.
    'envoi sur la première ligne vide
    If Not rsData.EOF Then
      FeuilleDest.Range("A" & Li).CopyFromRecordset rsData
    Else
      'si la source était vide...
      MsgBox "Aucun enregistrement renvoyé.", vbCritical
    End If

    ''' On nettoie pour finir...
    rsData.Close
    Set rsData = Nothing

End Sub

Merci,


François Racine

 

[Jam]

Re : Utilisation de ADO avec des cellules contenant beaucoup d'informations

Salut François,

Plusieurs pistes de réflexions:
- D'après ce que j'a trouvé sur le net l'erreur que tu indiques proviendrait d'un problème d'accès aux données -> Point à vérifier (fichier en lecture seule, déjà ouvert, protégé...)
- Lancer l'exécution en pas à pas pour pister le moment où arrive l'erreur + lire le contenu du Recordset via la Fenêtre des Variables Locales qui permet de voir le contenu des objets/enregistrement pendant l'exécution d'un programme.
- Le problème intervient-il à l'interrogation du premier ou du second fichier. As-tu essayé de lancer l'interrogation des fichiers dans un ordre différent (le 2 avant le 1 par exemple).
- Ton fichier dans lequel se situe ta macro est-il en 2007 ou 2003 ? Cette question car, sauf erreur de ma part, ta variable StrVersion renvoie la version du classeur actif et non celle du classeur interrogé...d'où possibilité de l'erreur.

Bon courage,

 

[siocnarf]

Re : Utilisation de ADO avec des cellules contenant beaucoup d'informations

Bonjour,

- D'après ce que j'a trouvé sur le net l'erreur que tu indiques proviendrait d'un problème d'accès aux données -> Point à vérifier (fichier en lecture seule, déjà ouvert, protégé...)

Les 29 premières lignes sont copiées correctement et toutes proviennent du même fichier a.xlsx.

- Lancer l'exécution en pas à pas pour pister le moment où arrive l'erreur + lire le contenu du Recordset via la Fenêtre des Variables Locales qui permet de voir le contenu des objets/enregistrement pendant l'exécution d'un programme.

Ligne 30 colonne L. Je joins une image de la colonne. Il s'agit de l'item 12. On remarquera que le Defined size est énorme comparativement aux champs précédents et suivants.

- Le problème intervient-il à l'interrogation du premier ou du second fichier. As-tu essayé de lancer l'interrogation des fichiers dans un ordre différent (le 2 avant le 1 par exemple).

Je n'ai pas testé le second fichier. Je suis perplexe, en quoi inverser l'ordre d'interrogation de fichiers avec le même format peut-il faire une différence?

- Ton fichier dans lequel se situe ta macro est-il en 2007 ou 2003 ? Cette question car, sauf erreur de ma part, ta variable StrVersion renvoie la version du classeur actif et non celle du classeur interrogé...d'où possibilité de l'erreur.

Tu as raison mais tout est en 2007.

J'ai fait le test en utilisant le fichier b. La même erreur se produit mais à la ligne 23, colonne L. L'information contenue dans la cellule est sur 80 lignes.

Merci,


François

 

[Jam]

Re : Utilisation de ADO avec des cellules contenant beaucoup d'informations

Re,

Je n'ai pas testé le second fichier. Je suis perplexe, en quoi inverser l'ordre d'interrogation de fichiers avec le même format peut-il faire une différence?

Bah pour ce que tu constates juste après

J'ai fait le test en utilisant le fichier b. La même erreur se produit mais à la ligne 23, colonne L. L'information contenue dans la cellule est sur 80 lignes.

Eh, eh, on dirait un début de réponse, non ?
- Tu as 2 fichiers qui sont accessibles et "requetables" donc ce n'est pas un problème d'accès aux 2 fichiers
- Oui, mais qui bloquent sur certains enregistrements et plus précisément sur certaines cellules apparemment. A tester: effacer le contenu de ces cellules et relancer la/les requête(s). Si cela passe, tu pourras incriminer le contenu de la cellule qui n'est pas récupérable via une requête et qu'il faudra gérer autrement. Il s'agit typiquement d'un problème de structure de fichier. Excel, n'est pas une base de données car ses données ne sont pas structurées et par conséquent peuvent générer des problèmes pour des requêtes de bdd. Tu parles de lignes dans une cellule, je crains que soit il n'aime pas les retours chariots soit c'est la taille de la cellule qui passe pas.

Tu as raison mais tout est en 2007.

Bon, ben si c'est pas utile, il vaut mieux le retirer. Tout ce qui ne sert à rien est susceptible de créer des problèmes qu'il sera peut-être dur de résoudre, alors mieux vaut éviter.
De même tu prends la peine de déclarer tes variables (il y a quelques optimisations possibles) il est donc dommage de ne pas le faire avec celle-ci (utilisation d'Option Explicit en en-tête de module vivement conseillée).

Sur ce, bonne nuit et bon courage.

 

[siocnarf]

Re : Utilisation de ADO avec des cellules contenant beaucoup d'informations

Bonjour,

Je confirme que ce champs texte est en cause. Il y a 125 lignes et si on coupe à environ 110 lignes alors cela passe. Je joins le texte en exemple.

Le formatage ne semble pas être la cause. J'ai supprimé des lignes jusqu'à ce que ça passe simplement.

Puisque je souhaite ne pas ouvrir les fichiers pour effectuer des manipulations avant de les fusionner, je crois que je manque d'option. N'y aurait-il pas une façon d'éviter des timeout ou de lui dire de prendre des champs plus gros?

Merci,


François Racine