Copier la feuille d'un classeur fermé vers un classeur ouvert Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
C
Callitek
Jeune membre
Jeune membre
Messages : 24
Inscrit le : 20 février 2019

Message par Callitek » 26 février 2019, 12:38

Bonjour à tous,

Dans le cadre d'un projet d'entreprise, je souhaite copier la feuille d'un classeur fermé et la coller dans une nouvelle feuille d'un classeur ouvert (celui depuis lequel j'exécute ma macro). Je suis déjà aller voir sur internet mais impossible de trouver quelque chose qui fonctionne ..

Voici mon code :
Sub RequeteClasseurFerme()

    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 = "E:/INSA - GCE/Projet méthode/Documents internes/Tableau de sélection de matériels/00A001 - Test 1/03 - DOSSIER MATERIELS/Tableau de sélection de matériels.xlsm"
    'Nom de la feuille dans le classeur fermé
    NomFeuille = "Feuil1"
    
    Set Cn = New ADODB.Connection
    
    '--- Connection ---
    With Cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & Fichier & _
            ";Extended Properties=Excel 8.0;"
        .Open
    End With
    '-----------------
    
    'Définit la requête.
    '/!\ Attention à ne pas oublier le symbole $ après le nom de la feuille.
    texte_SQL = "SELECT * FROM [Feuille de mission$]"
      
    
    
    Set Rst = New ADODB.Recordset
    Set Rst = Cn.Execute(texte_SQL)
    
    'Ecrit le résultat de la requête dans la cellule A1
    Range("A1").CopyFromRecordset Rst
    
    '--- Fermeture connexion ---
    Cn.Close
    Set Cn = Nothing

End Sub
Lorsque j'exécute celui-ci, j'ai une erreur (Erreur de complilation : Type défini par l'utilisateur non défini) et je ne sais pas pourquoi de plus, ma première ligne se surligne :
Cn As ADODB.Connection
J'attend vos réponses avec impatience !
C
Callitek
Jeune membre
Jeune membre
Messages : 24
Inscrit le : 20 février 2019

Message par Callitek » 26 février 2019, 13:15

Petite mise à jour :

J'ai activé la référence Microsoft ActiveX Data Objects x.x Library et je n'ai plus l'erreur sur ma première ligne, par contre, maintenant j'ai une autre erreur : "Erreur d'exécution '-2147467259 (80004005)' : Erreur Automation Erreur non spécifiée)

Quelqu'un aurait une idée ?
Avatar du membre
pierrep56
Membre impliqué
Membre impliqué
Messages : 1'201
Appréciations reçues : 197
Inscrit le : 18 juin 2014
Version d'Excel : 2016

Message par pierrep56 » 26 février 2019, 17:11

Bonjour,

Plusieurs points :
* Jet c'est un peu ancien, on peut utiliser MSDASQL
* Cn.Execute => c'est à préférer pour les requêtes du genre Insert, Update, Delete. Pour un Select, un Open suffit
* enfin ici en liaison tardive, inutile de s'embêter avec les références.

Voici une proposition de code :
Sub RequeteClasseurFerme()
    Dim Cn As Object, Rst As Object
    Dim Fichier As String, texte_SQL As String
    Dim j As Integer
    
    Fichier = "E:/INSA - GCE/Projet méthode/Documents internes/Tableau de sélection de matériels/00A001 - Test 1/03 - DOSSIER MATERIELS/Tableau de sélection de matériels.xlsm"

    Set Cn = CreateObject("ADODB.Connection")
    Cn.Provider = "MSDASQL"
    Cn.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
             "DBQ=" & Fichier & "; ReadOnly=False;"
             
    texte_SQL = "SELECT * FROM [Feuille de mission$]"

    Set Rst = CreateObject("ADODB.Recordset")
    Rst.Open texte_SQL, Cn, 3
    
    With ActiveSheet
        For j = 1 To Rst.Fields.Count   ' =>les entêtes
            .Cells(1, j) = Rst.Fields(j - 1).Name
        Next j
        .Range("A2").CopyFromRecordset Rst  ' =>les données
    End With
    
    Cn.Close
    Set Rst = Nothing
    Set Cn = Nothing
End Sub
Pierre

edit :
PS : on peut rendre ce code un peu plus "générique" pour pouvoir l'utiliser plusieurs fois dans une même appli :
Sub Exemple_d_appel()
    Dim Fichier As String, Feuil As String, Destination As String
    
    Fichier = "E:/INSA - GCE/Projet méthode/Documents internes/Tableau de sélection de matériels/00A001 - Test 1/03 - DOSSIER MATERIELS/Tableau de sélection de matériels.xlsm"
    Feuil = "Feuille de mission"
    Destination = "Feuil1"
    RequeteClasseurFerme Fichier, Feuil, Destination
End Sub


Sub RequeteClasseurFerme(Fichier As String, Feuil As String, Destination As String)
    Dim Cn As Object, Rst As Object, j As Integer
    
    Set Cn = CreateObject("ADODB.Connection")
    Cn.Provider = "MSDASQL"
    Cn.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
             "DBQ=" & Fichier & "; ReadOnly=False;"

    Set Rst = CreateObject("ADODB.Recordset")
    Rst.Open "SELECT * FROM [" & Feuil & " $]", Cn, 3
    
    With Sheet(Destination)
        For j = 1 To Rst.Fields.Count   ' =>les entêtes
            .Cells(1, j) = Rst.Fields(j - 1).Name
        Next j
        .Range("A2").CopyFromRecordset Rst  ' =>les données
    End With
    
    Cn.Close
    Set Rst = Nothing
    Set Cn = Nothing
End Sub
3 membres du forum aiment ce message.
C
Callitek
Jeune membre
Jeune membre
Messages : 24
Inscrit le : 20 février 2019

Message par Callitek » 26 février 2019, 18:31

Cool merci, ça fonctionne ! Par contre, il y a juste un problème, dans les cases B1 à N1, il me met des valeurs (F2 à F14) alors qu'il n'y a rien dans mon classeur fermé, sais-tu pourquoi ?
Avatar du membre
pierrep56
Membre impliqué
Membre impliqué
Messages : 1'201
Appréciations reçues : 197
Inscrit le : 18 juin 2014
Version d'Excel : 2016

Message par pierrep56 » 27 février 2019, 12:11

Bonjour,

La question est intéressante.

Pour rappel, le fonctionnement ordinaire d'Excel est de faire au mieux des ordres donnés et au mieux de ce qu'il trouve.
Dans le cas de ce type de requête : "SELECT * FROM [Feuille de mission$]", Excel va chercher dans cet onglet toute info qui ressemble à des données quelconques ... et même si elles ont été effacées!
Dans ton cas, il se peut qu'il y ait eu des saisies dans les colonnes B à N, secondairement effacées mais dans le doute Excel traite ces colonnes quand même.

Pour éviter ces info inutiles, plusieurs méthodes possibles :

1/ La méthode "Bourin" : je connais les entêtes pertinentes, je les saisis en ligne 1, et dans le code j'efface la boucle For j = 1 To Rst.Fields.Count
C'est simple, mais si le fichier de données comporte beaucoup de lignes et de colonnes, on perd du temps pour rien. A éviter.

2/ La méthode "Ceinture" : je restreins les colonnes à lire dans la clause FROM de la requête Sql.
Ici par exemple pour limiter aux colonnes de A à C :
"SELECT * FROM [Feuille de mission$A:C]"
Rq : si on veut on peut aussi limiter le nombre de lignes, par exemple =>
[Feuille de mission$A1:C100]

3/ La méthode "Bretelles" : je restreins les colonnes à lire en spécifiant nommément les entêtes :
"SELECT Id, Materiel FROM [Feuille de mission$]" (en supposant qu'il y ait 2 entêtes `Id` et `Materiel` dans le fichier de données)
Cette méthode suffit la plupart du temps.
Rq : /!\ si une entête est un mot composé, par exemple `Date de péremption`, il est nécessaire d'entourer l'entête de `guillemets-du-7` (ALT-Gr + 7 2 fois puis backspace) et non pas 'guillemets-du-4'. Soit :
"SELECT Id, Materiel, `Date de péremption` FROM [Feuille de mission$]"

4/ La méthode "Ceinture ET Bretelles" : je combine les 2 restrictions précédentes :
"SELECT Id, Materiel FROM [Feuille de mission$A:C]"
C'est la plus rapide d'exécution en cas de fichier de données imposant (de plusieurs gigas).


- Ah mais, donc on peut moduler le texte de la requête?
- Et oui, et c'est là tout l'intérêt de la méthode, le SELECT est une commande en langage SQL, et on peut tout à fait écrire des requêtes plus complexes en utilisant les possibilités du SQL (et c'est très puissant!)
Un exemple tiré d'une de mes appli (avec jointure de plusieurs onglets et fonctions d’agrégation et de tri)
    Req = "SELECT MAX(V.Date_visite), M.Freq, R.Responsable, M.Id, M.Denomination, " & _
          " M.Reference, M.Emplacement, M.Batiment, M.Etage, M.Info1  " & _
          " FROM ([" & Materiel & "$] AS M " & _
          " LEFT JOIN [" & Verif & "$] AS V ON M.Id=V.Id)" & _
          " LEFT JOIN [" & Responsables & "$] AS R ON R.Id_Resp=M.Id_Resp" & _
          " WHERE (dateadd('m',12/M.freq, V.Date_visite)<=" & CLng(Date) & _
          " OR V.Date_visite>=" & derJ & _
          " OR ISNULL(V.Date_visite)) " & _
          " AND M.Id_Resp=" & Filtre & _
          " GROUP BY M.Freq, R.Responsable, M.Id, M.Denomination, M.Reference, M.Emplacement, " & _
          " M.Batiment, M.Etage, M.Info1 " & _
          " ORDER BY  MAX(V.Date_visite) ASC, M.Denomination ASC"
Là en l’occurrence le résultat de cette requête est placée dans un "Array" pour traiter rapidement les info recueillies secondairement.

En conclusion : le SQL, c'est bon, mangez-en!

Pierre

PS : LA Source à avoir dans ses favoris/marque-pages : http://sql.sh/
1 membre du forum aime ce message.
C
Callitek
Jeune membre
Jeune membre
Messages : 24
Inscrit le : 20 février 2019

Message par Callitek » 27 février 2019, 12:44

Oh excellent merci ! Je vais pouvoir me débrouiller avec tous ça !!
b
bidexcel
Membre habitué
Membre habitué
Messages : 65
Appréciation reçue : 1
Inscrit le : 21 novembre 2017
Version d'Excel : 2013 FR

Message par bidexcel » 20 juin 2019, 19:59

pierrep56 a écrit :
26 février 2019, 17:11
Bonjour,

Plusieurs points :
* Jet c'est un peu ancien, on peut utiliser MSDASQL
* Cn.Execute => c'est à préférer pour les requêtes du genre Insert, Update, Delete. Pour un Select, un Open suffit
* enfin ici en liaison tardive, inutile de s'embêter avec les références.

Voici une proposition de code :
Sub RequeteClasseurFerme()
    Dim Cn As Object, Rst As Object
    Dim Fichier As String, texte_SQL As String
    Dim j As Integer
    
    Fichier = "E:/INSA - GCE/Projet méthode/Documents internes/Tableau de sélection de matériels/00A001 - Test 1/03 - DOSSIER MATERIELS/Tableau de sélection de matériels.xlsm"

    Set Cn = CreateObject("ADODB.Connection")
    Cn.Provider = "MSDASQL"
    Cn.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
             "DBQ=" & Fichier & "; ReadOnly=False;"
             
    texte_SQL = "SELECT * FROM [Feuille de mission$]"

    Set Rst = CreateObject("ADODB.Recordset")
    Rst.Open texte_SQL, Cn, 3
    
    With ActiveSheet
        For j = 1 To Rst.Fields.Count   ' =>les entêtes
            .Cells(1, j) = Rst.Fields(j - 1).Name
        Next j
        .Range("A2").CopyFromRecordset Rst  ' =>les données
    End With
    
    Cn.Close
    Set Rst = Nothing
    Set Cn = Nothing
End Sub
Pierre

edit :
PS : on peut rendre ce code un peu plus "générique" pour pouvoir l'utiliser plusieurs fois dans une même appli :
Sub Exemple_d_appel()
    Dim Fichier As String, Feuil As String, Destination As String
    
    Fichier = "E:/INSA - GCE/Projet méthode/Documents internes/Tableau de sélection de matériels/00A001 - Test 1/03 - DOSSIER MATERIELS/Tableau de sélection de matériels.xlsm"
    Feuil = "Feuille de mission"
    Destination = "Feuil1"
    RequeteClasseurFerme Fichier, Feuil, Destination
End Sub


Sub RequeteClasseurFerme(Fichier As String, Feuil As String, Destination As String)
    Dim Cn As Object, Rst As Object, j As Integer
    
    Set Cn = CreateObject("ADODB.Connection")
    Cn.Provider = "MSDASQL"
    Cn.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
             "DBQ=" & Fichier & "; ReadOnly=False;"

    Set Rst = CreateObject("ADODB.Recordset")
    Rst.Open "SELECT * FROM [" & Feuil & " $]", Cn, 3
    
    With Sheet(Destination)
        For j = 1 To Rst.Fields.Count   ' =>les entêtes
            .Cells(1, j) = Rst.Fields(j - 1).Name
        Next j
        .Range("A2").CopyFromRecordset Rst  ' =>les données
    End With
    
    Cn.Close
    Set Rst = Nothing
    Set Cn = Nothing
End Sub
Bonsoir Pierre j'ai le même soucis que Calitek. J'ai copier ton codes pour le réadapter à mon fichier mais j'ai des messages d'erreur
Ereur 1.PNG
Quand j'active le Microsoft ActiveX Data Objects x.x Library
Ereur 2 micorsot dat.PNG
En validant il met met ''Nom de module, de projet ou de bibliothèque déjà utilisé "
Ereur 3.PNG
J'utilise Excel 2016
le code que j'ai coller et remplacer le chemin
Sub Exemple_d_appel()
Dim Fichier As String, Feuil As String, Destination As String

Fichier = "C:\Users\amouedraogo\Desktop\Macro\Analyse technique.xlsm"
Feuil = "Base de données cours"
Destination = "Base de données cours"
RequeteClasseurFerme Fichier, Feuil, Destination
End Sub

Sub RequeteClasseurFerme(Fichier As String, Feuil As String, Destination As String)
Dim Cn As Object, Rst As Object, j As Integer

Set Cn = CreateObject("ADODB.Connection")
Cn.Provider = "MSDASQL"
Cn.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
"DBQ=" & Fichier & "; ReadOnly=False;"

Set Rst = CreateObject("ADODB.Recordset")
Rst.Open "SELECT * FROM [" & Basededonnescours & " $]", Cn, 3

With Sheet(Destination)
For j = 1 To Rst.Fields.Count ' =>les entêtes
.Cells(1, j) = Rst.Fields(j - 1).Name
Next j
.Range("A2").CopyFromRecordset Rst ' =>les données
End With

Cn.Close
Set Rst = Nothing
Set Cn = Nothing
End Sub
Merci de m'aider :|
Avatar du membre
pierrep56
Membre impliqué
Membre impliqué
Messages : 1'201
Appréciations reçues : 197
Inscrit le : 18 juin 2014
Version d'Excel : 2016

Message par pierrep56 » 21 juin 2019, 10:27

Déjà, c'est inutile d'activer des références supplémentaires (et contre productif).

Plutôt que des explications, voici une démo avec lecture xls et accdb via ADO

Pierre
Démojuin2019.zip
(55.06 Kio) Téléchargé 52 fois
1 membre du forum aime ce message.
b
bidexcel
Membre habitué
Membre habitué
Messages : 65
Appréciation reçue : 1
Inscrit le : 21 novembre 2017
Version d'Excel : 2013 FR

Message par bidexcel » 21 juin 2019, 14:00

pierrep56 a écrit :
21 juin 2019, 10:27
Déjà, c'est inutile d'activer des références supplémentaires (et contre productif).

Plutôt que des explications, voici une démo avec lecture xls et accdb via ADO

Pierre
Bonjour Pierre
J'ai essayé de comprendre le mécanisme de la demo fournie mais :( trop fort pour moi :) . Je suis novice en VBA mais Acces j'ai pas encore manipuler. je cherchais juste à travers le code que tu as fourni à changer les références des fichiers Excel pour faire tourner la macro en fonction de mon besoin.

Plus clairement je vourdrais copier les données de la feuille "Base de données cours" du fichier fermé 'Analyse technique finale.xlm dont l'adresse est C:\Users\amouedraogo\Desktop\Macro\Analyse technique\Analyse technique finale.xlm vers la feuille "Base de données cours" du classeur ouvert "Evaluation.xlm"

Voici les fichiers en question
Evaluation stés.xlsm
(19.38 Kio) Téléchargé 21 fois
Analyse technique finale .rar
(826.14 Kio) Téléchargé 25 fois
Please si tu peux bien m'aider
Avatar du membre
pierrep56
Membre impliqué
Membre impliqué
Messages : 1'201
Appréciations reçues : 197
Inscrit le : 18 juin 2014
Version d'Excel : 2016

Message par pierrep56 » 21 juin 2019, 15:43

1/ Attention au nom de fichier : Analyse technique finale .xlsm n'est pas Analyse technique finale.xlsm => cf espace avant le point

2/ Je ne sais pas quel est ce p$*!n de format de fichier, mais en copiant/collant les données dans un fichier neuf nommé Analyse technique finale.xlsm, onglet Base de données cours le code fonctionne sans problème.

3/ Si les données commencent en ligne 3, il faut l'indiquer dans la requête

Voir fichier démo => en modifiant dans le code le chemin du nouveau fichier Analyse technique finale.xlsm (sans espace avant le point)

Pierre
Recup_ADO.zip
(368.85 Kio) Téléchargé 43 fois
1 membre du forum aime ce message.
Répondre
  • Sujets similaires
    Réponses
    Vues
    Dernier message