[VBA]Dernière colonne non-vide classeur fermé [Résolu/Fermé]

Signaler
Messages postés
231
Date d'inscription
vendredi 3 octobre 2008
Statut
Membre
Dernière intervention
22 juin 2014
-
michel_m
Messages postés
16042
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
23 janvier 2020
-
Bonjour,

pouvez-vous m'aider pour résoudre mon problème :
(je précise que je débute en VBA)

J'ai un fichier toto.xls ouvert et dans lequel je travaille.
Je cherche le n° de la dernière colonne non vide de la ligne 1 du fichier titi.xls. Il serait agréable de ne pas avoir à ouvrir titi.xls si on peut s'en dispenser.

Grosso modo, appliquer
col_non_vide = Range("IV1").End(xlToLeft).Column

sur mon classeur fermé

Pouvez-vous m'aider s'il-vous-plaît ?
Merci d'avance

6 réponses

Messages postés
7911
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
24 janvier 2020
1 267
Peut-être :
Sub DernèreColonneFichierFermé()
Dim NomFichier As String
Dim Chemin As String
Dim Formule As String
Dim col As Integer
Dim col_non_vide As Integer

  Range("A1").ClearContents
  Chemin = "D:\Temp\"
  NomFichier = "Classeur_à_lire.xls"
  For col_non_vide = Columns.Count To 3 Step -1
    Formule = "='" & Chemin & "[" & NomFichier & "]Feuil1" & "'!" & "R1C" & col_non_vide
    Range("A1").FormulaR1C1 = Formule
    Range("A1").Value = Range("A1").Value
    If Range("A1").Value <> 0 Then Exit For
  Next col_non_vide
  
  MsgBox col_non_vide
  
End Sub
3
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 82896 internautes nous ont dit merci ce mois-ci

Messages postés
231
Date d'inscription
vendredi 3 octobre 2008
Statut
Membre
Dernière intervention
22 juin 2014
23
Bonjour, merci de ta réponse.

Cela fonctionne !
J'aimerais comprendre toutes les étapes, peux-tu me dire si je suis dans le vrai :

La boucle part de la dernière colonne du fichier, pour atteindre la 3ième par pas de -1.

En A1, on met un lien vers le classeur fermé, pointant la 1ère ligne et la colonne de la boucle. On fait une sorte de copier/Coller Valeur. Si cette valeur est différente de 0, on sort de la boucle.

C'est à peu près ça ? Plutôt que "différente de 0", n'existerait-il pas une sorte de fonction non(estVide()) ?

En tout cas, merci beaucoup de ton aide !
Patrice33740
Messages postés
7911
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
24 janvier 2020
1 267
C'est exactement cela.

Le différent de 0 vient du fait que le résultat d'une simple formule de recopie de cellule vide (ex. =A1) est zéro (et donc pas vide).

@michel_m,
Plutôt que de passer par le registre, ne serait-il pas plus simple d'écrire directement dans le classeur (fermé) contenant la macro ?
Mais dans les deux cas il faut ouvrir le fichier d'origine pour enregistrer l'info.

Cordialement
Patrice
michel_m
Messages postés
16042
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
23 janvier 2020
2 854
Bonjour,

non, je ne pense pas....

le nbre de colonnes (col) sera inscrite automatiquement dans HKCU dès que l'on utilisera "titi" et on on l'utilisera au moins une fois puisque c'est un fichier source (voire pour installer la macro!)

Après "titi" pourra rester fermé en quelque sorte définitivement; si il est nécessaire -évolution ou maintenance de l'appli, par ex - d'ajouter 1 ou plusieurs colonnes celles ci seront prises en compte dès la fermeture de titi; (on pourrait la prendre en compte en temps réel par une événementielle worksheet_change sur la ligne 1, "titi" pouvant alors resté ouvert ou fermé sans incidence sur le travail dans "toto")

D'autre part, si pour une raison x ou y, on décide de déplacer dans un autre répertoire titi cela n'aura aucune incidence dans l'appel de la valeur dans le registre -je n'ai pas testé sur un changement de lecteur dans le pc

dans toto, j'ai mis l'appel dans un module mais la macro xxx pourrait être installée comme événementielle

C'est ce qui m'a paru le + simple et le + souple comme fonctionnement


Titi restant fermé:

on aurait pu aussi utiliser ADO ou ADOX (solution 1) avec une requête comptant le nbre de champ en ligne 1 -fields.count - mais cela demande beaucoup de renseignements sur la source, ADO et ADOX ayant des limites très strictes pour son utilisation et pas forcément évidente à assimiler pour quelqu'un qui débute comme Foreverson (on a tous débuté!)


Une autre solution assez élégante aurait consisté à mettre dans une cellule de titi le nombre de colonnes et d'appeler dans "toto" par une macro dite du grand manitou US Walkenbach;
chemin= "D:\........"
dercol= ExecuteExcel4Macro("'" & chemin & "\[titi.xls]Feuil1'!R1C26")



Cordialement
Patrice33740
Messages postés
7911
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
24 janvier 2020
1 267
Il se pourrait aussi, comme je l'ai supposé, que le fichier soit actualisé régulièrement par un autre utilisateur sur une autre machine et que Foreverson ne souhaite ni l'ouvrir ni le modifier.

Cordialement
michel_m
Messages postés
16042
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
23 janvier 2020
2 854
Re
foreverson avait écrit en #4
C'est une application pour mon ordi, pas de réseau, pas de serveur.

...
Messages postés
16042
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
23 janvier 2020
2 854
bonjour,

Peut-^tre 1ou 2 solutions,Mais:
nom de la feuille concernée dans titi ?

soluTion1

1/ s'agit -il d'une base de données dans titi avec les titres en ligne 1 et 1)champ en colonne A
3/ sino, Il y a t il des cellules vides entre la dernière colonne utilisée et la colonne A?

solution 2
1/ ton appli est elle en monoposte ?
2/ sinon as tu les droits d'accès au serveur (ou faisant office de )

Dans l'attente
Messages postés
231
Date d'inscription
vendredi 3 octobre 2008
Statut
Membre
Dernière intervention
22 juin 2014
23
La feuille dans titi s'appelle effectif. Lignes de titres sur la 1ère ligne, champ des colonnes en A, B et C.

A priori, pas de cellules vides entre la dernière colonne utilisée et la colonne A. Du coup, plutôt que de partir de la fin, je pense que partir de la 1ère colonne pour chercher la dernière colonne non vide permettrait de gagner quelques micro-secondes.

C'est une application pour mon ordi, pas de réseau, pas de serveur.
Messages postés
16042
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
23 janvier 2020
2 854
Re,


Solution 2

dans module1 de ton classeur "titi", on prend en compte la dernière colonne à chaque utilisation de titi (modif, ajout...)
Dans le module "Thisworkbook" dans titi ( si tu ne connais pas: Alt+F11 , ctrl+R pour faire apparaitre l'explorateur de projet, thisworkbook)

Private Sub Workbook_BeforeClose(Cancel As Boolean)   
Dim col As Byte   
     col = Sheets("effectif").Range("IV1").End(xlToLeft).Column   
     'écriture dans HKey_current_user/software / VB and VBA Program settings   
     SaveSetting appname:="ccm", section:="truc", Key:="machin", setting:=col   
End Sub


col est alors dans la base de registre windows, tu peux éteindre l'ordi, laisser "titi" fermé voire le transporter dans un autre dossier

Dans ton classeur "toto"
Sub xxxxx()   
Dim dercol_titi As Byte   
 'Lit la valeur dans la base de registre   
     dercol_titi = GetSetting(appname:="ccm", section:="truc", Key:="machin")   
End Sub   

Sub nettoyer()   
Sub EffacedansRegistre()   
     On Error Resume Next   
     DeleteSetting "ccm"   
End Sub   
End Sub

Tu peux choisir le noms que tu veux pour appname, section, et Key



Michel
Messages postés
231
Date d'inscription
vendredi 3 octobre 2008
Statut
Membre
Dernière intervention
22 juin 2014
23
Ok, merci à tous les deux pour votre aide !