[Résolu]VBA - Récupération de nom et de valeur sans passer par une variable

NeMoS · 29 Juin 2012

Bonjour à tous

Désolée pour le titre, j'ai un peu de mal à exprimer de façon synthétique mon besoin...

Voilà, une partie de ma macro consiste à créer un tableau dans lequel je récupère des informations de plusieurs fichiers que j'ouvre.

J'ai écrit mon code comme suit:

Code:

i = 6
taille_tableau = 20

ThisWorkbook.Sheets("Format_Check").Range("b5").Value = ThisWorkbook.Name
ThisWorkbook.Sheets("Format_Check").Range("c5").FormulaR1C1 = ThisWorkbook.Sheets("PROCESS KNOWLEDGE").Application.WorksheetFunction.CountA(Range("A2:AZ3"))
ThisWorkbook.Sheets("Format_Check").Range("d5").FormulaR1C1 = ThisWorkbook.Sheets("PRODUCT KNOWLEDGE").Application.WorksheetFunction.CountA(Range("A2:AZ3"))
ThisWorkbook.Sheets("Format_Check").Range("e5").FormulaR1C1 = ThisWorkbook.Sheets("GENERAL SKILLS").Application.WorksheetFunction.CountA(Range("A3:AZ3"))

For i = 5 To taille_tableau
    If Sheets("Format_Check").Range("b" & i).Value = "" Then
    ThisWorkbook.Sheets("Format_Check").Range("b" & i).FormulaR1C1 = wb.Name
    ThisWorkbook.Sheets("Format_Check").Range("c" & i).Value = wb.Sheets("PROCESS KNOWLEDGE").Application.WorksheetFunction.CountA(Range("A2:AZ3"))
    ThisWorkbook.Sheets("Format_Check").Range("d" & i).FormulaR1C1 = wb.Sheets("PRODUCT KNOWLEDGE").Application.WorksheetFunction.CountA(Range("A2:AZ3"))
    ThisWorkbook.Sheets("Format_Check").Range("e" & i).FormulaR1C1 = wb.Sheets("GENERAL SKILLS").Application.WorksheetFunction.CountA(Range("A2:AZ3"))
    i = i + 1
    End If
Next

Donc en gros, je récupère d'abord le nom du fichier et je le mets dans une cellule prédéfinie de mon tableau (créé dans un autre bout de la macro), puis je récupère le résultat du comptage des cellules non vides de différentes feuilles sur la ligne 2 et j'inscris le résultat dans une cellule prédéfinie de mon tableau.

Pour info wb correspond aux différents fichiers que j'ouvre par ailleurs.

Mon soucis c'est qu'il semble que je ne puisse pas écrire un truc du genre:
ThisWorkbook.Sheets("Format_Check").Range("b5").Value = ThisWorkbook.Name

directement, il me renvoie que j'ai une variable "not set"....

Pour être claire: la macro ne fonctionne pas

J'espère ne pas avoir été trop confuse dans mes explications et si jamais l'un d'entre vous a une idée, ou veut me dire que mon code est particulièrement mal écrit 🙂), surtout qu'il n"hésite pas !!!

Merci par avance

Gwen

Venitien · 29 Juin 2012

Re : VBA - Récupération de nom et de valeur sans passer par une variable

Salut Gwen, "ton code est particulièrement mal écrit" 😉 lol

plus sérieusement, essayes en mettant

Code:

nom = ThisWorkBook.Name

en dessous de

Code:

Taille_Tableau = 20

et remplace ThisWorkBook.Name par nom...

J'ai pas de certitude mais c'est en essayant qu'on saura.

A+

Robert · 29 Juin 2012

Re : VBA - Récupération de nom et de valeur sans passer par une variable

Bonjour Gwen, bonjour le forum,

Tu n'as pas besoin de spécifier ThisWorkbook chaque fois à moins que ton code se trouve dans un autre classeur. Ça devrait marcher comme ça :

Code:

i = 6
taille_tableau = 20
With Sheets("Format_Check")
    .Range("b5").Value = ThisWorkbook.Name
    .Range("c5").FormulaR1C1 = Sheets("PROCESS KNOWLEDGE").Application.WorksheetFunction.CountA(Range("A2:AZ3"))
    .Range("d5").FormulaR1C1 = Sheets("PRODUCT KNOWLEDGE").Application.WorksheetFunction.CountA(Range("A2:AZ3"))
    .Range("e5").FormulaR1C1 = Sheets("GENERAL SKILLS").Application.WorksheetFunction.CountA(Range("A3:AZ3"))
    For i = 5 To taille_tableau
        If .Range("b" & i).Value = "" Then
            .Range("b" & i).FormulaR1C1 = wb.Name
            .Range("c" & i).Value = wb.Sheets("PROCESS KNOWLEDGE").Application.WorksheetFunction.CountA(Range("A2:AZ3"))
            .Range("d" & i).FormulaR1C1 = wb.Sheets("PRODUCT KNOWLEDGE").Application.WorksheetFunction.CountA(Range("A2:AZ3"))
            .Range("e" & i).FormulaR1C1 = wb.Sheets("GENERAL SKILLS").Application.WorksheetFunction.CountA(Range("A2:AZ3"))
            i = i + 1
        End If
    Next
End With

Si tu travailles avec plusieurs classeur ouverts je te conseille de déclarer et définir tes variables comme par exemple:

Code:

Dim c1 As Workbook 'déclare la variable c1 (Classeur 1)
Dim c2 As Workbook 'déclare la variable c2 (Classeur 2)
Dim c3 As Workbook 'déclare la variable c3 (Classeur 3)
Dim o1 As Object 'déclare la variable o1 (Onglet 1)
Dim o2 As Object 'déclare la variable o2 (Onglet 1)
Dim o3 As Object ''déclare la variable o3 (Onglet 1)
Set c1 = Workbooks("Classeur1.xls") 'définit le classeur c1 (ou set c1 = Thisworkbook par exemple)
Set c2 = Workbooks("Classeur2.xls") 'définit le classeur c2
Set c2 = Workbooks("Classeur3.xls") 'définit le classeur c3
Set o1 = c1.Sheets("Feui1") 'définit l'onglet de travail o1 du classeur 1
Set o2 = c2.Sheets("Janvier") 'définit l'onglet de travail o2 du classeur 2
Set o3 = c3.Sheets("Total") 'définit l'onglet de travail o3 du classeur 3

o1.Range("B5").Value = c1.Name 'place le nom du classeur c1 dans la cellule B5 de l'onglet o1 (forcément du classeur c1)
o2.Range("A1:C10").Copy o3.Range("A1") 'copie la plage A1:C10 de l'onglet "Janvier" du classeur 2 et la colle dans A1 de l'onglet "Total" du classeur c3
'etc.

Vorens · 29 Juin 2012

Re : VBA - Récupération de nom et de valeur sans passer par une variable

Hello tous le monde,

Chez moi cette ligne fonctionne parfaitement

Code:

Sub test()


ThisWorkbook.Sheets("Feuil1").Range("A2").Value = ThisWorkbook.Name



End Sub

Le problème vient d’ailleurs si toute fois il y a un problème 🙂

Meilleures salutations

Robert · 29 Juin 2012

Re : VBA - Récupération de nom et de valeur sans passer par une variable

Bonjour le fil, bonjour le forum,

Venitien à dit:
Code:

nom = ThisWorkBook.Name

et remplace ThisWorkBook.Name par nom...

heu Venitien... C'est quoi l'intitulé du fil déjà ?

NeMoS · 29 Juin 2012

Re : VBA - Récupération de nom et de valeur sans passer par une variable

Merci à tous,

Venitien : en effet le but de la manœuvre est d'éviter d'avoir des variables intermédiaires ;-) mais si jamais je n'y arrive pas, je devrais en passer par là

Robert: en fait au début je n'avais en effet pas spécifié thisworkbook, mais comme j'avais des petits comportements bizarres, pour être sure que les pb ne venaient pas de là, j'ai précisé.
Les fichiers que j'ouvre sont traités puis fermés les uns après les autres, donc je n'ai toujours que deux fichiers ouverts, thisworkbook et un fichier .xls mis temporairement dans wb car je ne veux pas avoir à dépendre du nom du fichier.
Cependant, merci pour les infos, je vais essayer la méthode et voir ce que ça donne.....d'ailleurs, je devrais vraiment penser au With plus souvent....

Vorens: c'est pas juste....moi y'a rien à faire ça veut pas.....

Merci à tous et je vous tiens au courant

Gwen

NeMoS · 29 Juin 2012

Re : VBA - Récupération de nom et de valeur sans passer par une variable

re-bonjour,

Bon, j'ai essayé d'éclaircir mon code comme me l'avait suggéré Robert, Malheureusement cela ne change rien....
En mettant des points d'arrêt j'ai pu voir que quand il passe à l'exécution suivante (compter les cellules non-vide) il a bien récupéré le nom du fichier mais pourquoi il ne l'inscrit pas dans la cellule...Mystère

Quant à mes countA ils ne donnent absolument aucun résultat !!!!!

Bon, il va falloir que je creuse car je ne vois pas pourquoi cela ne marche pas....

Des idées ??? 😕

Merci
Gwen

Vorens · 29 Juin 2012

Re : VBA - Récupération de nom et de valeur sans passer par une variable

Re,

Va falloir commencer à penser à nous donner ton fichier car la on va pas pouvoir grand chose de plus pour toi...

Staple1600 · 29 Juin 2012

Re : VBA - Récupération de nom et de valeur sans passer par une variable

Bonjour à tous

NeMos:
Le plus simple serait de mettre en pièce jointe un fichier exemple (anonymisé bien sûr 😉 ) mais à la structure identique à ceux que tu veux ouvrir dans ta macro.
On pourra alors en faire 2,3 copies pour tester un code VBA adéquat présent dans un classeur autre que ta PJ et ses copies.

NeMoS · 29 Juin 2012

Re : VBA - Récupération de nom et de valeur sans passer par une variable

Bon,

Voici mon fichier, je vous explique un peu parce que la macro contient plusieurs trucs et je sais pas trop comment découper

Le fichier "....GLOBAL.xls" est celui qui contient les macros. Sur la page INSTRUCTION il y a trois bouton, celui qui nous intéresse est le second (Check File consistency) autrement dit command_button_2

Puis je vous donne un fichier vide qui ne contient pas de macro et qui n'a pas besoin de données non plus.
Pour regarder si ça marche vraiment il faut au moins faire deux copies de ce fichier.

Comment ça marche:
1/ enregistrer le ou les fichiers vides quelque part, n'importe où du moment qu'ils soient ensembles et tous seuls (c'est mieux)

2/ Clicker sur le bouton
une première msgbox d'info s'ouvre, click sur OK

3/ Normalement une seconde boite de dialogue s'ouvre et vous demande où sont les fichiers, là il faut répondre NO (c'est important car si vous répondez oui, ça va pas marcher vu que j'ai pas fini d'écrire le code....

Je dis normalement parce que cela marchait très bien mais depuis 10min il ne veut plus ouvrir cette msg box sauf si je mets un point d'arrêt dans le code et que je le fais à la main....comprends pas.

Bref, là il va falloir aller chercher le répertoire dans lequel vous avez mis les fichiers vides

Quand vous répondez "NO" cela vous renvoie au Case2 dans la macro du command_button_2.
C'est là que se trouve le bout de code qui me pose pb en ce moment

Le vrai but c'est de comparer le nombre de colonne entre la matrice de référence (GLOBAL) et les autres matrices, afin de vérifier qu'il n'y a pas eu de modification du format de la matrice et ensuite de renvoyer ces info (nombre de colonnes "utilisées"de chacune des feuilles) dans un tableau créé en macro dans un nouvel onglet créer par macro...

Désolée pour mon code, je ne suis pas une spécialiste VBA et j'ai tendance à être un peu.....bourrine 🙂
Si vous avez des questions n'hésitez pas....

Bon courage !!!!

Et encore merci pour votre aide

NeMoS · 29 Juin 2012

Re : VBA - Récupération de nom et de valeur sans passer par une variable

Re-re bonjour à tous....

Bon, je vois que je vous ai un peu noyé dans ma macro 🙂)) ce que je peux aisément comprendre, je vous avais prévenu....mon code n'est pas des plus fins.

Cependant, pour une raison que j'ignore (parce que je n'ai pas l'impression que mes modif étaient si importantes) j'ai réussi à obtenir en partie ce que je voulais, mais voilà, j'ai un autre soucis toujours dans la même partie de code....

En fait il y a x fichiers à ouvrir, et la macro les ouvre bien, mais pour une raison que je n'arrive pas à comprendre,, la boucle if ne fonctionne que pour le premier fichier. Il ouvre bien les autres mais ne récupère pas les infos.....

Je crois que ma boucle if est mal pensée....
Ce que je veux faire c'est ouvrir le premier fichier, vérifier que la ligne dans laquelle je veux copier les infos est vide (il suffit que je vérifie la première cellule, pas besoin de vérifier la ligne entière) et aller à la ligne suivante si ce n'est pas le cas.
De cette façon, quand il ouvre le premier fichier il copie les infos dans b6
le second fichier aura ses info dans b7 etc.....
ça marche pour b6 (le premier fichier), mais pour les autres fichiers, il sort directement de la boucle, ferme le fichier et ouvre le suivant ainsi de suite...

Ci-joint la partie de code en question :

Code:

MsgBox "Select the folder in which the data files have been stored"

ChoosePath (chemin)
'Appel de la fonction qui ouvre le browser de dossier et récupère l'adresse du dossier de stockage des fichiers

ChDir chemin
monfichier = Dir("*.xls")

While monfichier <> ""
Set wb = Workbooks.Open(monfichier)
monfichier = Dir()

i = 6

If ThisWorkbook.Sheets("Format_Check").Range("b" & i).Value = "" Then
    ThisWorkbook.Sheets("Format_Check").Range("b" & i).FormulaR1C1 = wb.Name
    ThisWorkbook.Sheets("Format_Check").Range("c" & i).Value = wb.Sheets("PROCESS KNOWLEDGE").Application.WorksheetFunction.CountA(Range("A2:AZ2"))
    ThisWorkbook.Sheets("Format_Check").Range("d" & i).FormulaR1C1 = wb.Sheets("PRODUCT KNOWLEDGE").Application.WorksheetFunction.CountA(Range("A2:AZ2"))
    ThisWorkbook.Sheets("Format_Check").Range("e" & i).FormulaR1C1 = wb.Sheets("GENERAL SKILLS").Application.WorksheetFunction.CountA(Range("A2:AZ2"))
Else: i = i + 1
End If


Application.CutCopyMode = False
'on ferme tous les fichiers sauf le global sans sauvegarder aucunes modifications
wb.Close SaveChanges:=False
Set wb = Nothing
 
Wend

Encore merci pour votre aide

Gwen

NeMoS · 2 Juillet 2012

Re : VBA - Récupération de nom et de valeur sans passer par une variable

Bonjour à tous,

Bon, ben j'ai résolu mon problème.
Le premier : merci Robert, cela fonctionne mieux avec ta méthode, cela ne fonctionnait pas bien car j'avais un with sans end with qui trainait et qui me mettait le bazar dans le code.

POur mon second problème, le we a été bénéfique : c'était simplement que ma déclaration de la valeur de départ de ma variable i était mal placée...

Donc finalement tout fonctionne bien (sauf mon counta qui compte n'importe quoi mais ça je pense pouvoir le résoudre...)

Encore merci à tous

Gwen

[Résolu]VBA - Récupération de nom et de valeur sans passer par une variable

NeMoS

XLDnaute Junior

Venitien

XLDnaute Occasionnel

Robert

XLDnaute Barbatruc

Vorens

XLDnaute Occasionnel

Robert

XLDnaute Barbatruc

NeMoS

XLDnaute Junior

NeMoS

XLDnaute Junior

Vorens

XLDnaute Occasionnel

Staple1600

XLDnaute Barbatruc

NeMoS

XLDnaute Junior

Pièces jointes

NeMoS

XLDnaute Junior

NeMoS

XLDnaute Junior

Discussions similaires

[Résolu]VBA - Récupération de nom et de valeur sans passer par une variable

XLDnaute Junior

XLDnaute Occasionnel

XLDnaute Barbatruc

XLDnaute Occasionnel

XLDnaute Barbatruc

XLDnaute Junior

XLDnaute Junior

XLDnaute Occasionnel

XLDnaute Barbatruc

XLDnaute Junior

Pièces jointes

XLDnaute Junior

XLDnaute Junior

Discussions similaires

Privacy & Transparency

Privacy & Transparency