XL 2019 extraire des données d'un fichier Excel sans l'ouvrir avec VBA

Kenza18

XLDnaute Nouveau
Bonjour,

je souhaite extraire les données d'un fichier Excel très volumineux (lire le fichier sans l'ouvrir).

les données que je veux extraire sont les suivantes :

les cellules du champ code-techno == '4GF'. --> [ ce champ se trouve sur la colonne D]

Est ce que c'est possible de m'aider à trouver une solution avec VBA s'il vous plait ?

En vous remerciant d'avance pour vote aide.



Cordialement,
Kenza
 

soan

XLDnaute Barbatruc
Inactif
@chris, @Kenza18,

comme tu avais écrit qu'Access ne peut pas lire des fichiers .csv de plus d'un million de lignes,
je croyais que tu parlais de créer un nouveau fichier Access, en mettant toutes les données
dans des tables Access.

par la même occasion, ça permettrait à @Kenza18 de profiter de la puissance de la Base de
Données Access (SGBDR = Système de Gestion de Bases de Données Relationnelles).


soan
 

Kenza18

XLDnaute Nouveau
@chris, @Kenza18,

comme tu avais écrit qu'Access ne peut pas lire des fichiers .csv de plus d'un million de lignes,
je croyais que tu parlais de créer un nouveau fichier Access, en mettant toutes les données
dans des tables Access.

par la même occasion, ça permettrait à @Kenza18 de profiter de la puissance de la Base de
Données Access (SGBDR = Système de Gestion de Bases de Données Relationnelles).


soan
vous pouvez m'expliquer comment cela est possible s'il vous plait ?
 

soan

XLDnaute Barbatruc
Inactif
Bonjour @Kenza18, (post modifié et complété ce matin, le 21 décembre)

il faudrait que tu saches utiliser Access, et comme pour tout nouveau logiciel, il faut
un temps d'apprentissage ! ce serait long pour t'expliquer comment t'en servir, et en
plus, on est ici sur le Forum Excel ! pour Access, c'est dans : « Questions / Autres
applications
» ; tu peux aussi faire une recherche google avec ces trois mots :
« cours access gratuit ».

j'ai ajouté tout ce qui est écrit ci-dessous ; bonne lecture ! 😜


-------------------------------------------------------------------------------------------------

tu sais déjà ce qu'est un traitement de textes comme Word et un tableur comme Excel ;
pour une Base de données comme Access, ça se passe en 2 temps : 1) stocker toutes
les données dans des tables ; 2) interroger la Base de données pour faire ressortir les
informations qui nous sont utiles.


-------------------------------------------------------------------------------------------------

voici un résumé très court : dans Access, il y a en particulier : les Tables ; Requêtes ;
Formulaires ; États ; il y a en plus les relations entre les tables, les macros, et les
modules.


-------------------------------------------------------------------------------------------------

* toutes les données d'une base Access sont stockées dans les Tables, et uniquement
dans ces Tables ; la notion de clé primaire est vraiment très importante, car c'est
elle qui permet d'accéder aux données d'une Table ; y'a aussi un principe d'unicité :
les données ne doivent pas comporter de doublons, et surtout pas pour la clé primaire,
sinon, Access ne pourrait pas déterminer quelle est la « fiche » qui est concernée.

* les Requêtes permettent d'interroger la base et de faire ressortir les données qui
nous intéressent ; même s'il est possible de modifier les données dans des « feuilles
de réponse », c'est quand même les données des Tables qui sont modifiées ; on dit
que les Tables sont sous-jacentes aux Requêtes ; notion QBE = Query By Exemple
= Requête par l'exemple.

* les Formulaires permettent de mieux présenter les données des Tables, à raison
d'un seul enregistrement par Formulaire ; un enregistrement est une ligne de
données, les différents éléments de cette ligne étant les champs ; exemples :
Nom ; Prénom ; Adresse ; Code Postal ; Ville ; là aussi, même s'il est possible
de modifier les données dans les formulaires, c'est quand même les données
des Tables qui sont modifiées ; on dit que les Tables sont sous-jacentes aux
Formulaires.

* les États permettent de mieux présenter les données à imprimer ; on ne peut
pas modifier les données des États ; les États sont basés sur les Tables et / ou
sur les Requêtes.


-------------------------------------------------------------------------------------------------

pour plus d'infos, tu peux suivre le lien de mon post #35 ci-dessous.

soan
 
Dernière édition:

soan

XLDnaute Barbatruc
Inactif
@Kenza18

voici un lien : Cours Guide Microsoft Access 2010 gratuit en PDF

si tu as une autre version d'Office, tu peux me dire laquelle, et j'essayerai
de te trouver un autre lien pour l'Access de cette autre version d'Office.

-------------------------------------------------------------------------------

regarde aussi les propositions de @job75 et @R@chid ci-dessous :
post #36 et post #37 ; peut-être que t'auras pas besoin d'Access ?
mais c'est quand même intéressant de connaître ce logiciel. ;)


soan
 
Dernière édition:

job75

XLDnaute Barbatruc
Voici la macro pour importer un fichier texte .txt :
VB:
Sub Importer_TXT()
Dim t#, chemin$, fichier$, ncol%, texte$, i&, s, n&, a(), col%, resu(), dest As Range
t = Timer
chemin = ThisWorkbook.Path & "\" 'à adapter
fichier = "Source.txt" 'à adapter
ncol = 8 'nombre de colonnes à copier
'---lecture séquentielle du fichier texte---
Open chemin & fichier For Input As #1 'ouverture du fichier texte
Do While Not EOF(1) 'EndOfFile: fin du fichier
    Line Input #1, texte 'récupère la ligne
    i = i + 1
    s = Split(texte, vbTab) 'séparateur : tabulation
    If i = 1 Or s(3) = "4GF" Then 'critère en 4ème colonne
        n = n + 1
        ReDim Preserve a(1 To ncol, 1 To n)
        For col = 1 To ncol
            a(col, n) = s(col - 1)
        Next col
    End If
Loop
Close #1 'fermeture du fichier texte
'---transposition---
ReDim resu(1 To n, 1 To ncol)
For i = 1 To n
    For col = 1 To ncol
        resu(i, col) = a(col, i)
Next col, i
'---restitution dans la feuille Excel quand c'est possible---
With Feuil1 'CodeName à adapter
    If .FilterMode Then .ShowAllData
    Set dest = .[A1] '1ère cellule de destination, à adapter
    If n > .Rows.Count - dest.Row + 1 Then MsgBox "Tableau trop grand pour Excel !", 48: Exit Sub
    If n Then dest.Resize(n, ncol) = resu
    dest.Offset(n).Resize(.Rows.Count - n - dest.Row + 1, ncol).ClearContents 'RAZ en dessous
    With .UsedRange: End With 'actualise les barres de défilement
End With
MsgBox "Durée " & Format(Timer - t, "0.00 \sec"), , "Import"
End Sub
Testez les fichiers zippés joints, chez moi l'import se fait en 0,45 seconde.

Le fichier Source.txt a 40 000 lignes.
 

Pièces jointes

  • Dossier(2).zip
    23.9 KB · Affichages: 15

R@chid

XLDnaute Barbatruc
Bonjour @ tous,
@Kenza18
On ne sait pas comment tu vas utiliser ces données après l'importation, sinon, lors de l'importation via Power Query, tu peux toutefois ne créer que la connexion avec le fichier source et ajouter les données importées au modèle de données pour faire des calculs avec des TCDs ou PowerPivot.
2020-12-21_01-05-44.jpg


Cordialement
 

chris

XLDnaute Barbatruc
Bonjour à tous
On ne sait pas comment tu vas utiliser ces données après l'importation, sinon, lors de l'importation via Power Query, tu peux toutefois ne créer que la connexion avec le fichier source et ajouter les données importées au modèle de données pour faire des calculs avec des TCDs ou PowerPivot.
Il n'est pas nécessaire d'ajouter les données au modèle PowerPivot pour faire un TCD à partir d'une requête
On évite même en général sauf besoin de mesures particulières dans un TCD car le modèle de données est lourd

Comme tu avais écrit qu'Access ne peut pas lire des fichiers .csv de plus d'un million de lignes,
Je parlais d'Excel pas d'Access pour 1 millions de lignes.
Les limites d'accès sont différentes

Le problème c'est que ce fil tient du débat sur le sexe des anges !
On ne connait toujours ni le contexte, ni l'objectif opérationnels...
 

Airone784

XLDnaute Occasionnel
Bonjour @job75,
VB:
Sub Importer()
Dim t#, chemin$, fichier$, dercol$, formule$, n&
t = Timer
chemin = ThisWorkbook.Path & "\" 'à adapter
fichier = "Source.xlsx" 'à adapter
dercol = "H" 'dernière colonne à copier
formule = "'" & chemin & "[" & fichier & "]Feuil1'!"
On Error Resume Next
n = ExecuteExcel4Macro("MATCH(""zzz""," & formule & "C4)") 'dernière cellule texte en colonne D
On Error GoTo 0
'---restitution---
Application.ScreenUpdating = False
With Feuil1 'CodeName de la feuille de destination
    If .FilterMode Then .ShowAllData 'si la feuille est filtrée
    With .Range("A1:" & dercol & 1).Resize(n)
        .FormulaArray = "=" & formule & "A1:" & dercol & n 'formule de liaison matricielle
        .Value = .Value 'supprime la formule
       .Rows(1) .Offset(n).Resize(Rows.Count - n).ClearContents 'RAZ en dessous
        .Sort .Columns(4), Header:=xlYes 'tri pour regrouper et accélérer
        .Columns(4).Replace "4GF", "#N/A"
        On Error Resume Next
        .Columns(4).Offset(1).SpecialCells(xlCellTypeConstants, 3).EntireRow.Delete 'textes et nombres
        .Columns(4).Replace "#N/A", "4GF"
        .Replace 0, "", xlWhole 'efface les valeurs zéro
    End With
    With .UsedRange: End With 'actualise les barres de défilement
End With
MsgBox "Durée " & Format(Timer - t, "0.00 \sec"), , "Import"
End Sub

Cette méthode fonctionne bien pour transférer des données d'un fichier A vers B rapidement. J'ai testé avec le fichier que Job75 a mis dans le post #6. Ensuite, je me suis permis d'enlever la partie qui filtre les données car dans mon exemple à moi, je ne veux que récupérer des données d'un fichier B vers fichier A sans rien filtrer.
Donc, je vire la fin du code que j'ai réussi à décrypter. Par contre, en adaptant ce code à mon cas à moi comme je n'ai que des données numériques, n=0 à chaque fois et du coup ça ne fonctionne plus.
Comment faire si mes données sont numériques donc ???

Sinon @patricktoulon

Code:
Sub testAdO()
    Dim fichier$, nomfeuille$, DispoCel As Range
    fichier = ThisWorkbook.Path & "\Planning_GDS.xlsm"
    nomfeuille = "Liste_ressources"
    Set DispoCel = [M3]
    resADO [A1:D300000], fichier, nomfeuille, DispoCel
End Sub
Function resADO(plage, fichier, nomfeuille, destination)


'Dim Cn As ADODB.Connection, texte_SQL$, Rst As ADODB.Recordset
    Dim Cn As Object, texte_SQL$, Rst As Object
      Set Cn = CreateObject("ADODB.Connection")
     Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"";"
         texte_SQL = "SELECT * FROM [" & nomfeuille & "$" & plage.Address(0, 0) & "]"  'la requête.    ' Attention!!!!!!! à ne pas oublier le symbole "$" après le nom de la feuille.


    
    
    Set Rst = CreateObject("ADODB.RecordSet")
    Set Rst = Cn.Execute(texte_SQL)
    
       '--- Boucle sur les entêtes pour récupérer les noms ---
    For i = 0 To Rst.Fields.Count - 1
    Cells(2, i + 13).Activate
        Cells(2, i + 13) = Rst.Fields(i).Name
    Next i
    
    destination.CopyFromRecordset Rst
    '--- Fermeture connexion ---
    Cn.Close
    Set Cn = Nothing: Set Rst = Nothing
End Function
cette méthode fonctionne bien également sauf quand le fichier origine est ouvert où je rencontre un message d'erreur. C'est normal comme comportement ?

1610026552809.png


Merci de vos aides respectives.
 
Dernière édition:

soan

XLDnaute Barbatruc
Inactif
Bonjour Airone784, le fil,

bonne année 2021, et meilleurs vœux ! 🥳

c'est bizarre : on dirait que le demandeur @Kenza18 s'est changé en @Airone784 !

si c'est toi Kenza : j'espère que c'est pas car je t'ai confondu avec Kenza Braiga
que tu as changé de pseudo ! j'espère aussi que tu as lu mon post #34 et aussi
mon post #35 ; si oui, j'espère que toutes mes infos t'ont été utiles. ;)


soan
 

Airone784

XLDnaute Occasionnel
Bonjour Airone784, le fil,

bonne année 2021, et meilleurs vœux ! 🥳

c'est bizarre : on dirait que le demandeur @Kenza18 s'est changé en @Airone784 !

si c'est toi Kenza : j'espère que c'est pas car je t'ai confondu avec Kenza Braiga
que tu as changé de pseudo ! j'espère aussi que tu as lu mon post #34 et aussi
mon post #35 ; si oui, j'espère que toutes mes infos t'ont été utiles. ;)


soan
Bonjour et meilleurs voeux aussi.
Désolé mais je ne comprends pas ta réponse. Je suis bien dans un fichier excel xlsm vers un autre excel xlsm. Je ne souhaite pas utiliser access.
Moi j'ai une base de 15000 lignes grand max que je veux importer d'un excel à un autre mais je bloque avec les codes proposés... D'où ma question !
 

Discussions similaires

Statistiques des forums

Discussions
311 720
Messages
2 081 924
Membres
101 841
dernier inscrit
ferid87