XL 2016 VBA - Code pour écrire une plage dans une feuille d'un classeur fermé

[Dudu2]

Bonjour
Quelqu'un aurait ça dans ses tiroirs ?
Cordialement

P.S. J'ai le code pour lire en classeur fermé besoin.

 

[dysorthographie]

Bonsoir,
Si tu avais un exemple de classeur fermé et un échantillon de données à injecter/modifier dans le classeur fermé.

Saches qu'il n'est pas possible de supprimer des lignes dans un classeur fermé.

 

[kiki29]

Salut, à lire : Lire et écrire dans les classeurs Excel fermés

 

[Zon]

Salut tout le monde,

je ne sais pas si l'ADO est toujours aussi instable...

Voici exemple en passant par une liaison temporaire, il y a peut être mieux en 2023,

Ici on va récupérer dans le classeur fermé Nomfichier.xlsx situé dans c:\dossier\ la plage A1:B100 , le résultat est mis dans la feuille "FeuilleActive" mais aussi retourné par la fonction recupval.

Sub Princ()
Dim Temp

Temp = RecupVal("C:\DOSSIER\", "Nomfichier.xlsx", "FeuilNomfichier", "A1:B100", "FeuilleActive")

End Sub

Function RecupVal(Chemin$, Nomfichier$, NomFeuille, Plage$, F As Worksheet)
    With F.Range(Plage)
      .Formula = "='" & Chemin & "[" & Nomfichier & "]" & NomFeuille & "'!" & Plage
      .Value = .Value
      RecupVal = .Value
   End With
End Function

Tout dépend ce que tu veux faire au final ..


A+++

 

[Dudu2]

Bonjour à tous,
Pour l'exemple disons que ce serait de placer les valeurs "B3" et "B4" dans les cellules B3 et B4 de la feuille "Feuil1" d'un classeur fermé.

Merci @kiki29 pour le lien très utile. Je ne l'avais trouvé ayant fait mes recherches en anglais seulement je suppose.

@Zon, c'est plutôt écrire que lire qu'il me faudrait.

 

[Dudu2]

Alors j'ai récupéré ça pour écrire dans 1 cellule:

Sub exportDonneeDansCelluleClasseurFerme()
    'Référencer la bibliothèque Microsoft ActiveX Data Objects x.x
    Dim Cn As ADODB.Connection
    Dim Cd As ADODB.Command
    Dim Rst As ADODB.Recordset
    Dim Fichier As String
  
    Fichier = "F:\Téléchargements\Classeur1.xlsx"
  
    Set Cn = New ADODB.Connection
    Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & Fichier & ";" & _
        "Extended Properties=""Excel 8.0;HDR=No;"";"
  
    Set Cd = New ADODB.Command
    Cd.ActiveConnection = Cn
    Cd.CommandText = "SELECT * FROM [Feuil1$G20:G20]"
  
    Set Rst = New ADODB.Recordset
    Rst.Open Cd, , adOpenKeyset, adLockOptimistic
    Rst(0).Value = "Donnée test"
    Rst.Update
  
    Cn.Close
    Set Cn = Nothing
    Set Cd = Nothing
    Set Rst = Nothing
End Sub

Mais ça coince:

 

[kiki29]

Salut, voir ceci du lien proposé plus haut, en l'adaptant à ton contexte.

    With Cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
            & Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
        .Open
    End With

 

[patricktoulon]

Bonjour @Dudu2 comme ça de bon matin à la fraiche
tout d'abords attention au moteur de connexion que vous utiliser
on est plus en xls donc on oublie le moteur "jet"
voici comment j'ajoute une ligne dans une base
l'exemple st simple il y a 3 colonnes
j'ai fait au mieux pour te commenter cela

Sub AddLigneDansBaseXLSX()
'patricktoulon
    Dim AdoC As Object, Lenom$, Leprenom$, Letel$, Fichier As String, Feuille As String, strSQL As String
    Dim colonne$, strSQLdestination$, strSQLscriptdonnée$

    Fichier = "C:\Users\patricktoulon\Desktop\destination.xlsx"     'chemin complet du fichier fermé
   
    Feuille = "Feuil1"                                              'nom de la feuille dans le fichier fermé
   
    colonne = "nom,prenom,Tel"                                      '(nom des entetes de plage ou header de TS)dans le fichier fermé

    'les données a mettre dans le fichier de destination
    Lenom = "dudu2"                                                 'donnée pour la colonne 1(ici en l'occurence la colonne ou se trouve"nom")
   
    Leprenom = "duduche"                                            'donnée pour la colonne 2(ici en l'occurence la colonne ou se trouve"prenom")
   
    Letel = "06 06 06 06 06"                                        'donnée pour la colonne 3(ici en l'occurence la colonne ou se trouve"Tel")

    Set AdoC = CreateObject("ADODB.Connection")                     'creation de l'object de connection

    With AdoC
        'string de connection de l'object AdoC(moteur fichier 2007  et +)
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""

        .Open    'ouverture de la connection

        strSQLdestination = "INSERT INTO[" & Feuille & "$](" & colonne & ")"                    'encodage du script de la destination de la requete

        strSQLscriptdonnée = "VALUES('" & Join(Array(Lenom, Leprenom, Letel), "','") & "')"     'encodage des donnée dans le script de la requete

        strSQL = strSQLdestination & strSQLscriptdonnée                                         'compile du string de la requete

        Debug.Print strSQL                                                                      'juste pour voir le string de la requete

        .Execute strSQL                                                                         'execution de la requete

        .Close                                                                                  'fermeture de la connection
    End With
    Set AdoC = Nothing                                                                          'destruction de l'object  de connection
End Sub

il est facile de transformer la partie strsql dans une boucle pour copier des lignes d'un tableau vers ton fichier fermé

bon allez maintenant café

mon fichier fermé peut se presenter comme ça

ou comme ça

 

[Dudu2]

Bonjour @patricktoulon,
De bon matin, à la fraiche, c'est vrai, 26° devant la Baie des Anges comme probablement devant la Rade, ça change des 34° et ça fait du bien, même si comme moi on aime la chaleur. Point trop n'en faut quand même !
Alors tu as un une réponse à ça aussi ?
Tu ne cesseras de m'étonner ! Je termine ma nuit interrompue à 05h00 et teste ton code.

 

[Dudu2]

Avant de regarder / tester ton code...
Au passage dans la fonction que j'ai construite autour d'un code récupéré pour lire dans un classeur fermé, effectivement, le JET n'est pas ce qui est utilisé.
Après modification ça coince toujours:

Sub exportDonneeDansCelluleClasseurFerme()
    'Référencer la bibliothèque Microsoft ActiveX Data Objects x.x
    Dim Cn As ADODB.Connection
    Dim Cd As ADODB.Command
    Dim Rst As ADODB.Recordset
    Dim Fichier As String
    
    Fichier = "F:\Téléchargements\Classeur1.xlsx"
    
    Set Cn = New ADODB.Connection
    'Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & Fichier & ";" & _
        "Extended Properties=""Excel 8.0;HDR=No;"";"
    Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & Fichier & ";Extended Properties=""Excel 12.0;HDR=NO;IMEX=1"";"
    
    Set Cd = New ADODB.Command
    Cd.ActiveConnection = Cn
    Cd.CommandText = "SELECT * FROM [Feuil1$G20:G20]"
    
    Set Rst = New ADODB.Recordset
    Rst.Open Cd, , adOpenKeyset, adLockOptimistic
    Rst(0).Value = "Donnée test"
    Rst.Update
    
    Cn.Close
    Set Cn = Nothing
    Set Cd = Nothing
    Set Rst = Nothing
End Sub

 

[patricktoulon]

remercie robert allias @dysorthographie sur XLD allias Rdurupt sur DVP
the king en la matière, c'est lui qui m'a tout appris sur ADO
je regarde ton code

 

[patricktoulon]

re
Attention aussi quand tu travaille avec des fichier se trouvant dans les downloads
W10 et 11 y sont encore plus sensibles que W7
windows defender surveille en permanence ce dossier ca peut poser des problèmes de delay dans les requetes
et le msgbox d'erreur renvoyé n'est pas toujours valide en terme de raison de l'erreur

 

[patricktoulon]

re
j'avoue mis a part le moteur je ne vois pas
pour le coup j'ai repris mes fonctions persos de mon xla

du coup la sub est réutilisable

les arguments adOpenKeyset et adLockOptimistic sont en dur et numeric ici
car j'utilise createobject donc latebinding
je n'ai pas l'implémentation en late binding
ça devient donc des variables vides
donc ici ça sera 1 et 3

Sub test()
    Dim fichier$, Feuille$, cel$, valeur
   
    fichier = "C:\Users\patricktoulon\Desktop\recepteur.xlsx"
   
    Feuille = "Feuil1"
   
    cel = "G30"
   
    valeur = "taratata"
   
    exportOneCel fichier, Feuille, cel, valeur
End Sub

Sub exportOneCel(ByVal fichier$, ByVal Feuille$, ByVal cel$, valeur)
'patricktoulon
    Dim AdoC As Object, CMd As Object, Rst As Object

    Set AdoC = CreateObject("ADODB.Connection")    'creation de l'object de connection

    Set Rst = CreateObject("ADODB.Recordset")    'creation de l'object recordset

    'ouverture de la connection
    AdoC.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & fichier & ";" & "Extended Properties=""Excel 12.0;HDR=NO;"""

    Set CMd = CreateObject("ADODB.Command")    'creation de l'object command de Adoc

    CMd.ActiveConnection = AdoC

    'commande  à  exécuter de AdoC
    CMd.CommandText = "SELECT * FROM [" & Feuille & "$" & cel & ":" & cel & "]"

    Rst.Open CMd, , 1, 3    'ouverture de la commande de AdoC dans le recordset

    Rst(0) = valeur    'inscription de la valeur dans l'item 0 du recordset

    Rst.Update    'update du recordset

    'on peut tout lacher maintenant
    AdoC.Close
    Set AdoC = Nothing
    Set CMd = Nothing
    Set Rst = Nothing
End Sub

voila voila

 

[Dudu2]

Merci pour le retour.
En fait le code du Post #10 (et ton dernier ci-dessus) ne fonctionnent QUE si la cellule à écrire est dans le UsedRange. Sinon punition !

Dommage pour la restriction mais c'est sans doute l'effet de bord de considérer le fichier .xlsx comme une BD.

 

[dysorthographie]

Bonjour,
Si tu penses qu'il n'est pas utile de fournir un échantillonnage comme je te l'ai suggéré,je le comprends.