VBA macro copier coller plage non vide

[Sylvie]

Bonjour à tous,

je souhaiterais pouvoir intégrer dans une feuille de mon classeur le code macro qui me permettrait de copier les valeurs des cellules des premières lignes non vides se trouvant dans la plage R19 à T29. Je précise que cette feuille s'appelle en réalité 'calculs' et non feuil1 (au cas où ceci serait important)

Le collage des valeurs se fait à destination d'un fichier ouvert nommé
'copie résultats.xls' : collage spécial des valeurs à partir de la première ligne non vide de la colonne A à D de la ligne 4 à .....

Ce qui m'interesse surtout c'est d'automatiser la selection puis la copie de ma plage non vide de ma feuille 'calculs'.
Ensuite j'effecturais le collage spécial dans ma feuille de destination à moins que l'un de vous puisse l'intégrer dans le code ou pourquoi pas en faire un autre code distinct dans la feuille d'arrivée (ca serait très bien aussi .
Je dois faire cette selection et ce copier coller sur 90 classeurs différents d'où ma demande.

Merci par avance à vous tous
Bonne fin de journée
Sylvie [file name=Sylvie_20050708181011.zip size=3794]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/Sylvie_20050708181011.zip[/file]

 

[_Thierry]

Bonsoir Sylvie, le Forum

Aie aie, avec toi je m'attends toujours à une surprise de derrière les fagots !!! lol

A première vue le code n'est pas vraiment sorcier à pondre (Il va ressembler un peu à un de ceux que je proposais dans 'Syntaxe' il y a quelques minutes), mais là où j'ai des doutes c'est pour ceci :

LA MACRO DOIT ME PERMETTRE DE COPIER LES VALEURS
(ET NON LES FORMULES EFFACEES ICI) DE LA PLAGE R19 à U22
LA LIGNE 23 NE DEVRA PAS ETRE COPIEE
LA PLAGE MAXIMALE A EXPLORER EST R19: U29

Hypothèse 1
C'est à dire en aucun cas on ne doit copier ce qui se trouve dans la plage R23:U23 ? Mais on doit quand même copier si les Plages R24:U24.... Si elle n'est pas vide ?

Hypothèse 2
En fait on ne doit copier que ce qui n'est pas Vide dans les Plage de Cellules R19:U29... (Plus Logique !!! et plus simple lol)

Par ailleurs j'ai une question toute bête, que doit t'on considérer comme 'Vide' ceci uniquement :

R20 = ''
S20 = ''
T20 = 0003
U20 = ''

Ou Bien...

Si une seule des trois cellules (R20 / S20 et U20) est vide ... On considère Vide ? (Faut savoir où faire les test, ou bien faire le test sur toutes les trois cellules ?)

Ensuite on verra !!! lol
Bon Week End
@+Thierry

 

[_Thierry]

Re Sylvie...

Hihihi Question Subsidaire !!!

'(ET NON LES FORMULES EFFACEES ICI) DE LA PLAGE R19 à U22'


Si il y a des Formules... Qu'elle est la valeur dans les Cellules 'Vides' qui ne seront finalement pas si 'Vides' que ça !!! lol

Bonne Soirée
@+Thierry

 

[Sylvie]

Bonsoir Thierry, le Forum,

Aie aie aie, j'ai encore dit une anerie qui ne passera pas inapercue je le crains. De toutes façons dès que j'essaie d'être précise je me prends les pieds dans le tapis :angry:

Je vais tenter d'être plus rigoureuse :
Ton hypothèse 2 est la bonne mon cher Thierry
En fait on ne doit copier que ce qui n'est pas Vide dans les Plage de Cellules R19:U29.)
Une ligne est dite vide dès que S20 est vide (même si il s'affiche 0 à côté dans la colonne R). Quand je dis vide, je veux dire sans qu'un chantier s'affiche.

Merci encore
A+
Sylvie

Question subsidiaire Thierry : si tu viens en avion à Lyon comment comptes tu ramener les caisses de Beaujolais dont je vais t'être redevable ?

 

[_Thierry]

Re Coucou

Bon message reçu pour le Beaujolais !!! lol

Bon je file du Bureau là, mais je m'attaque à ce truc quand je serai rentré, sinon demain si je tombe en traquenard à l'apéro lol

Bonne Soirée
@+Thierry

 

[_Thierry]

ADO Lecture Feuille Classeur Précise (Index 2)

Bonjour Sylvie, le Forum

Bon avant de commencer, je voulais un peu savoir si celà avait un lien direct avec ce Programme :

Lien supprimé ???

Car évidemment j'ai reconnu la Structure de la Feuille...

Dans l'élaboration d'un truc sur 90 Classeurs, l'idéal est d'avoir la certitude de travailler sur des Structures bien établies...

Par conséquent j'imagine que cette Feuille 'Calculs' est donc en Index 2 sur ces 90 Classeurs............ (c'est ce que mon UserForm fait suite à ta demande)

Et bien Ultra Dommage, car si on avait pensé à l'époque que tu devrais lire et récupérer certains champs de cette Feuille 'Calculs' de ces 90 Classeurs on l'aurait imanquablement passée en Index 1 lors de la manip avec mon UserForm ...

Pourquoi : Parceque aujourd'hui on aurait un Moyen Facile et Monstrueusement Rapide de le Faire par ADO sans à avoir à ouvrir chacun des 90 Classeurs... Arf Arf Arf...

Aussi je me permets de poser la question, quelqu'un de nos éminents spécialistes ADO, ont-il déjà expérimenté un recherche en Lecture d'un Classeur Fermé (Sans Champs Nommé of Course) mais sur une Range d'une Feuille précise pouvant se située n'importe où dans les différente Feuilles ?

En fait moi, j'arrive bien à récupérer une Range sur la Feuille Index 1... Peut-on forcer par exemple la lecture sur Index 2 ? (Sans évidemment à aller nommer un Champs dans chacun des 90 Classeurs)

Merci d'avance à ceux et celles qui pourront nous aider dans ce Projet...

Sinon, pour les reste j'ai déjà pratiquement tout de Prêt...

Au pire on pourrait faire tourner une macro Sporadiquement (en fait qu'une seule fois) qui bouclera pour ouvrir tous ces 90 Classeurs pour changer l'Ordre des Index, pour qu'ensuite le Programme ADO puisse se Mettre à Jour à volonté très rapidement mais bon......

Ah oui dernière Question pour toi Sylvie, ces 90 Classeurs sont tous dans le même répertoire au moins ? Et ont il un Nom en Commun, style 'Alves Analytique.xls'

Bon Samedi
@+Thierry


PS : L'ideal aussi serait que tu mettes en ligne aussi 1 de ces fameux 90 classeurs tel qu'il est (Sans vrai nom, ni vrai salaire, mais avec tout le reste, Formules Etc ainsi que toutes les Feuilles)

 

[MichelXld]

Re:ADO Lecture Feuille Classeur Précise (Index 2)

bonjour Sylvie , bonjour @+Thierry

je n'ai pas suivi la totalité du sujet , mais pour importer les données de la 2eme feuille du classeur fermé , tu peux tester

Sub donneesDeuxiemeFeuilleClasseur()
'necessite d'activer la reference Microsoft ActiveX Data Objects x.x Library
'necessite d 'activer la reference Microsoft ADO Ext 2.7 for DLL ans Security

Dim Cn As ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim Cat As ADOX.Catalog
Dim Fichier As String
Dim xConnect As String, Feuille As String
Dim Cible As String
Dim i As Byte

Fichier = 'C:\\Documents and Settings\\michel\\dossier\\general\\excel\\piloterFlash.xls'

xConnect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & Fichier & ';' & _
'Extended Properties=Excel 8.0;' 'preparation connection

Set Cat = CreateObject('ADOX.Catalog')
Set Cn = CreateObject('ADODB.Connection')

Cn.Open xConnect
Set Cat.ActiveConnection = Cn

Feuille = Cat.tables(1).Name 'nom de la 2eme feuille dans le classeur fermé

Cible = 'SELECT * FROM [' & Feuille & '];'

Set Rs = New ADODB.Recordset
Rs.Open Cible, Cn, adOpenForwardOnly, adLockReadOnly, adCmdText

If Not Rs.EOF Then
'For i = 0 To Rs.Fields.Count - 1 'recupere entetes
'Range('A1').Offset(0, i) = Rs.Fields(i).Name
'Next
Range('A2').CopyFromRecordset Rs
Else
MsgBox 'Aucun enregistrement renvoyé.', vbCritical
End If

Rs.Close
Set Rs = Nothing
Set Cn = Nothing
Set Cat = Nothing
End Sub

sinon ci joint quelques exemples pour récupérer les noms des classeurs fermés :

Lien supprimé


pour importer les données de toutes les feuilles d'un classeur fermé sans connaitre leur nom (exemple 2 dans le zip )

Lien supprimé



bon apres midi
MichelXld

 

[_Thierry]

Re:ADO Lecture Feuille Classeur Précise (Index 2)

Bonjour Michel

Merci beaucoup à toi.

Je vais voir si je peux adapter ça, car mon idée est assez différente sur la conception de ma requête ADO sur ce coup là.

Je reviens un peu plus tard.

En tout cas, c'est toujours super de pouvoir compter sur toi.

Bon Après Midi
@+Thierry

 

[_Thierry]

Re:ADO Lecture Feuille Classeur Précise (Index 2)

Re Bonjour Michel

Je suis en train de me documenter sur ADOX, mais d'ores et déjà je remarque une anomalie dans le fonctionnement si j'ai des champs nommés dans le Classeur Source... En effet on dirait que celà prime plutôt que les noms de Feuilles...

Ensuite j'aii un autre souci à venir dans l'avenir !!! lol

En fait je dois donc scanner la Plage de Cellules 'R19:U29' de chaque Feuilles 'Calculs' dans 90 Classeurs...

Or si j'utilise 'SELECT * FROM [' & Feuille & '];' qui donc doit être sensé être la Feuille (quand j'y arriverai lol) je ne vois pas comment m'en sortir pour compléter la requête SQL pour ne pointer sur la Range R19:U29...

En fait maintenant (sans préciser de Feuille) je passe ma plage 'RangeSource' dans la Commande (ADOCommand) SQL 'SELECT * from' Directement (Sans ADOX) comme ceci :

Sub TheADOReader()
Dim XLFile As String
Dim RangeSource As String
Dim ADOConnection As ADODB.Connection
Dim ADOCommand As ADODB.Command
Dim ADORecordSet As ADODB.Recordset

XLFile = ThisWorkbook.Path & 'MyTestFileSource.xls'

RangeSource = 'R19:U29'

  Set ADOConnection = New ADODB.Connection
      ADOConnection.Open 'Provider=Microsoft.Jet.OLEDB.4.0;' & _
                          'Data Source=' & XLFile & ';' & _
                          'Extended Properties=''Excel 8.0;HDR=No;'';'

  Set ADOCommand = New ADODB.Command
    With ADOCommand
        .ActiveConnection = ADOConnection
        .CommandText = 'SELECT * from [' & RangeSource & ']'
    End With
 
  Set ADORecordSet = New ADODB.Recordset
      ADORecordSet.Open ADOCommand, , adOpenKeyset, adLockOptimistic
 
  Set ADORecordSet = ADOConnection.Execute('[' & RangeSource & ']')

  With Sheets('ADOCollector')
      .Range('A' & .Range('A65536').End(xlUp).Row).CopyFromRecordset ADORecordSet
  End With

ADORecordSet.Close
ADOConnection.Close
End Sub

En fait tout va pour le meilleur des mondes, si la feuille qui contient la Plage 'R19:U29' est bien située en Index 1 du Classeur 'MyTestFileSource.xls'... Mais sinon NON !!!

Et pour ADOX, j'ai des champs nommée en prime dans le Classeur Source ... Alors je n'en vois pas la Fin...

Mais bon je cherche encre !!!

@+Thierry

 

[_Thierry]

Re:ADO Lecture Feuille Classeur Précise (Index 2)

Re Bonsoir Michel

Je viens de faire différentes expériences sur ADOX tel que tu l'as proposé....

J'ai trouvé un peu d'éclaircissement...

Mon Classeur Source de 'MyTestFileSource.xls' avec 3 Feuilles

Onglet Index 1 Nom : 'ZZZZ'
Onglet Index 2 Nom : 'RRRR'
Onglet Index 3 Nom : 'AAAA'

Si je paramètre le Code ainsi
Feuille = Cat.tables(0).Name

Je remonte les Donnée de la Feuille index 3 'AAAA'

Et si je paramètre
Feuille = Cat.tables(2).Name

Je remonte les Donnée de la Feuille index 1 'ZZZZ'

J'en conclus que c'est l'ordre alphabétique des Noms qui sert donc d'Index pour 'ADOX.Catalogue'...


Maintenant je 'Corse' (Salut Pat5) dans mon même Classeur 'MyTestFileSource.xls'

En nommant un Champs 'A'...

Tout est décalé !!!
Feuille = Cat.tables(0).Name = Ce Champs 'A'
Feuille = Cat.tables(3).Name = Feuille 'ZZZZ'

C'est ingérable ça LOL !!!!

Et pour continuer ma thérorie, j'ai ajouté encore un Champs que j'ai nommé 'ZZZZZZZZZZ' et bien il a bien un Retour de données en Cat.Table(4)....

Enfin il doit y avoir mieux à faire avec cet ADOX que je ne connais pas encore vraiment !!! LOL

Bon je continue !!!

@+Thierry

 

[MichelXld]

Re:ADO Lecture Feuille Classeur Précise (Index 2)

bonsoir Thierry

effectivement l'ordre d'index des tables semble correspondre à l'ordre alphabétique


peux tu tester ces 2 exemples

1. boucle sur toutes les tables d'un classeur fermé en filtrant les plages nommées ( qui n'ont pas de $ dans leur nom ) , puis import des plage de cellule R19:U29 de chaque feuille

2. import uniquement de la plage de cellules R19:U29 dans la feuille nommée 'Calculs'
Cible = 'SELECT * FROM [Calculs$];'


j'espere que ça pourra t'aider

bonne soiree
MichelXld [file name=extrairePlagesDonnees.zip size=22974]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/extrairePlagesDonnees.zip[/file]

Message édité par: MichelXld, à: 09/07/2005 21:43

 

[michel_m]

Re:ADO Lecture Feuille Classeur Précise (Index 2)

Bonsoir Sylvie, Thierry, Michel

En fait, comme Sylvie indique que les feuilles-source s'appelle toujours 'calculs', il n'y a pas besoin d'utiliser ADOX.

Ci dessous une proposition de macro (faire attention aux antislash) non terminée car je dois m'absenter, excusez moi!. Il reste la restitution du tableau intermédiaire à faire et regarder l'histoire des étiquettes (ligne 28)

Mais comme on a pas pour l'instant d'info sur les 90 classeurs, il faudra voir si on restitue le tablo à chaque fichier ou globalement à la fin, ce qui me paraît plus rapide

Je terminerai tout cà si personne n'aura complété et amélioré

Bonne soirée

Michel



'Option Explicit
Const onglet As String = 'calculs'
Const zone As String = 'R18:U29' 'Une ligne enplus au dessus pour fausse ligne d'étiquette; voir avec HDR dans connection?

Function FichOuvert(F As String) As Boolean
'Auteur: Didier_mdf sur forum www.Excel-downloads.com (merci)
Dim Wk As Workbook
On Error Resume Next
Set Wk = Workbooks(F) '(Merci à Ti pour cette astuce)
On Error GoTo 0
FichOuvert = Not Wk Is Nothing
End Function
Sub recup_zone()

Dim source As ADODB.Connection
Dim rqt As ADODB.Recordset
Dim chemin As String, classxls As String, fichier As String, texte_sql As String
Dim tablo()
Dim cptr As Long, lig As Byte

'initialisation provisoire
chemin = ThisWorkbook.Path
classxls = 'sylvie.xls'
fichier = chemin & '\\' & classxls

'teste si le classeur source est fermé
'If FichOuvert(fichier) = True Then
'MsgBox 'Pour que l'opération demandée soit effectuée,' & vbCr & _
''Le classeur ' & classxls & ' ne doit pas être ouvert. ', vbCritical
'Exit Sub
'End If

'active la connection
Set source = New ADODB.Connection
source.Open 'Provider = Microsoft.Jet.OLEDB.4.0;' & _
'data source=' & fichier & ';' & _
'extended properties=''Excel 8.0;'''

Set rqt = New Recordset
Set rqt = source.Execute('SELECT * from `' & onglet & '$' & zone & '`')

cptr = 1
ReDim tablo(cptr, 4)
rqt.MoveFirst
Do While Not rqt.EOF
If rqt.Fields(1) = Null Then
For col = 0 To 3
tablo(cptr - 1, col) = rqt.Fields(col)
Next col
End If
cptr = cptr + 1
ReDim tablo(cptr, 4)
rqt.MoveNext
Loop

' A fAIRE: rRESTITUTION TABLO DANS FEUILLe

Set rqt = Nothing
Set source = Nothing
End Sub


Je n'avais pas vu les 2 derniers échanges, excusez moi

 

[MichelXld]

Re:ADO Lecture Feuille Classeur Précise (Index 2)

bonsoir Michel

c'est sympa de te joindre à nous

ci joint une autre version : j'ai repris la procedure de Thierry dans le message de 18:19 , en l'adaptant pour importer les donnees de la feuille 'Calculs'

Sub TheADOReader()
Dim XLFile As String
Dim RangeSource As String
Dim ADOConnection As ADODB.Connection
Dim ADOCommand As ADODB.Command
Dim ADORecordSet As ADODB.Recordset
Dim Feuille As String

XLFile = ThisWorkbook.Path & '\\classeurFerme.xls'
Feuille = 'Calculs'

RangeSource = 'R19:U29'

  Set ADOConnection = New ADODB.Connection
      ADOConnection.Open 'Provider=Microsoft.Jet.OLEDB.4.0;' & _
       'Data Source=' & XLFile & ';' & _
        'Extended Properties=''Excel 8.0;HDR=No;'';'

  Set ADOCommand = New ADODB.Command
    With ADOCommand
        .ActiveConnection = ADOConnection
        .CommandText = 'SELECT * FROM `' & Feuille & '$' & RangeSource & '`'
    End With
  
  Set ADORecordSet = New ADODB.Recordset
      ADORecordSet.Open ADOCommand, , adOpenKeyset, adLockOptimistic
  
  Set ADORecordSet = ADOConnection.Execute('`' & Feuille & '$' & RangeSource & '`')

  With Sheets('ADOCollector')
      .Range('A' & .Range('A65536').End(xlUp).Row).CopyFromRecordset ADORecordSet
  End With

ADORecordSet.Close
ADOConnection.Close
End Sub

bonne soiree
MichelXld

Message édité par: MichelXld, à: 09/07/2005 22:29

 

[_Thierry]

Re:ADO Lecture Feuille Classeur Précise (Index 2)

Bonsoir les Michel

Vous êtes vraiment super sympas les gars, franchement l'Union Fait la Force !!!

En tout cas moi je viens de comprendre que le '$' ne servait pas qu'à payer le pétrole !!! lol

En fait c'était ce truc que je ne connaissais pas du tout et évidemment ça change tout pour traquer une feuille précise !!! (Intrépidemment j'avais quand même tenté : RangeSource = 'Calcul!R19:U29', et je me suis fait bien évidemment jetter bien poliment !!! LOL )

Sinon grace à vous j'ai aussi progressé sur ADOX Aussix !!!!

Je vous remercie infiniement.

Il ne manque plus que Sylvie pour les petites questions de finition que je lui ai posées plus haut.

Demain je mettrai en ligne un truc assez sympa avec le résultat de tout ce travail commun. Ce soir j'ai un coup de barre. (je me suis lu au moins deux heures d'aide d'Access 2003)

Bien à vous
@+Thierry

 

[michel_m]

Re:ADO Lecture Feuille Classeur Précise (Index 2)

Re Michel, Thierry

Je rentre juste et ce pb m'a tourné dans la tête cette soirée§...

En fait le pb de Sylvie: importer que les lignes où la colonne C est >à ou <>'' n'est pas résolu... Mon système de tableau est foireux comme chaque fois que je me lance dans les arrays (blocage)

Thierry je m'offre un coup de pub concernant ADOX: regarde ma démo
Lien supprimé passée pratiquement inapercue

Michel