Changer le classeur lié chaque année

[Martini]

Bonjour.

Je voudrais reporter la valeur d'une cellule d'un onglet de "classeur 2" dans mon "classeur 1" avec la possibilité de changer le nom du "classeur 2" (en "classeur 3" pour l'an prochain).

En clair, je voudrais avoir le résultat de ='[budget2007.xls]Budget global 2007'!$D9/12*B3 dans ma cellule cette année et ='[budget2008.xls]Budget global 2008'!$D9/12*B3 l'année prochaine.

J'ai une date sous forme 01/06/2007 dans mon "classeur 1" et ça serait pratique de pouvoir en récupérer l'année pour modifier le résultat de la cellule.

......comment fais-je
Merci pour vos idées

 

[Catrice]

Re : Changer le classeur lié chaque année

Bonjour,

Une solution avec INDIRECT :
=INDIRECT("'[budget"&ANNEE(A1)&".xls]Budget global "&ANNEE(A1)&"'!$D9")/12*B2
Où A1 contient une date 1/5/2007 par exemple.

Ou bien :
=INDIRECT("'[budget"&ANNEE(AUJOURDHUI())&".xls]Budget global "&ANNEE(AUJOURDHUI())&"'!$D9")/12*B2

 

[Martini]

Re : Changer le classeur lié chaque année

Merci
Mais on retombe dans le problème de la nécessité d'avoir "classeur 2" ouvert

Y'a pas d'autre solutions (voir en VBA...) ?

 

[Catrice]

Re : Changer le classeur lié chaque année

Re,

Meme problemes et meme réponses ...
En Vba, il y a la solution que je t'ai proposé dans l'autre Fil.
Ou bien INDIRECT.EXT ...

Ces 2 solutions ne sont pas satisfaisantes ?

 

[Martini]

Re : Changer le classeur lié chaque année

VBA> Même problème pour le chemin. Dommage qu'on ne puisse pas utiliser un chemin relatif (à moins qu'on puisse récupérer le dossier du classeur courant pour connaître l'arborescence ?).

INDIRECT.EXT> Est-ce qu'on peut l'intégrer au classeur ?


Merci

 

[Orodreth]

Re : Changer le classeur lié chaque année

Bonjour.

Récupérer le dossier courant:

MonChemin = activeworkbook.path & "\"

Après, il y a des méthodes pour boucler sur les dossiers/fichiers. (je cherche)

Je te renvoie à la wiki de Mechl pour la connection aux classeurs Excel fermés, servant alors de base de données.

Cordialement,
Thomas

 

[Catrice]

Re : Changer le classeur lié chaque année

Bonjour,

La fonction INDIRECT.EXT est une xll.
En récupérant le code (ou en développant un équivalent) on peut l'intégrer dans ton fichier XLS.

Sinon, on peut récupérer le chemin d'un fichier en Vba. Avec ThisWorkbook.Path.
Voir les fichiers joints.
Si les 2 fichiers sont dans le meme dossier, la macro met à jour les données en fonction du bon fichier.

Il est possible d'automatiser, mais sur quel évènement ?
Si on décide d'executer la macro à chaque saisie, ça risque d'etre un peu lourd/long.
On peut définir la mise à jour sur la saisie de quelques cellules seulement, ou à l'enregistrement, ou au recalcul etc ..

 

[Martini]

Re : Changer le classeur lié chaque année

Orodreth> Merci

Bonjour,

La fonction INDIRECT.EXT est une xll.
En récupérant le code (ou en développant un équivalent) on peut l'intégrer dans ton fichier XLS.

J'avais pas pris le temps de regarder. Merci pour l'info.

Si les 2 fichiers sont dans le meme dossier, la macro met à jour les données en fonction du bon fichier.

L'un est dans un dossier "budget", l'autre à la racine du répertoire.

Il est possible d'automatiser, mais sur quel évènement ?

Sur la saisie dans la cellule de la date de début. Ou sur saisie dans une nouvelle cellue qui contiendra l'année du budget à utiliser si c'est trop lourd (le classeur est déjà très lourd).


/!\ Ca ne concerne plus "tableau de bord mensuel" puisqu'on a trouvé la solution dans l'autre topic et que ça ne gène pas d'ouvrir le classeur lié.
Là c'est un peu plus embêtant puisqu'une fois le budget fait plus personne ne devrait avoir de raison de le rouvrir...

 

[Orodreth]

Re : Changer le classeur lié chaque année

Re.

Voici un code très simple pour boucler sur des fichiers dans un dossier:

Algo parcours dossier =

NomClasseur = Dir(leChemin & "\" & "*.xls")
Do While NomClasseur <> ""
    NomClasseur = Dir
Loop

Ca boucle sur chaque fichier dont l'extension est ".xls" (donc excel), et tu peux les manipuler via les méthodes de pilotage de classeurs excel fermés indiqués sur le wiki.

Voila ce que j'ai pu retrouver.

Cordialement,
Thomas

 

[Martini]

Re : Changer le classeur lié chaque année

Bonjour.

Catrice> Ce qui serait bien, c'est que la macro se contente de modifier la formule en fonction de l'année. Il n'y aurait donc pas besoin de mettre quoi que ce soit à jour à une éventuelle modification du fichier de budget.
Et on règle comme ça le problème du chemin relatif/absolu

Orodreth> J'ai pas compris ce que tu veux que je fasse de la boucle sur chaque fichier xls

Ce que je voudrais, c'est ='[budget2007.xls]Budget global 2007'!$D9/12*B3 dans ma cellule cette année et ='[budget2008.xls]Budget global 2008'!$D9/12*B3 l'année prochaine (formule mise à jour par la macro à chaque modification de la cellue A1).

J'ai pensé à un code. J'ai mis en rouge et en gras ce que je ne sais pas coder MAIS il y a mieux que ce code à utiliser (cf les puces d'après).

Sub ModifAnnuelle() 
Dim c As Range
Dim i as integer
i=9
    For Each c In Range("c4:c100")
         [B][COLOR="Red"]La formule doit être :[/COLOR][/B] "='[budget" & A1 & ".xls]Budget global " & A1 & "'!$D" & i & "/12*B3"
i=i+1
    Next 
End Sub

Mais j'ai un problème avec cette macro : parfois je dois sauter une cellule (pas de formule dans cette cellule mais il n'y a pas de saut dans le classeur lié <- je suis pas certain d'être compréhensible), ce qui signifie que je dois :

• Utiliser plusieurs macros, une par bloc entier
• Utiliser une macro plus intelligente que la mienne qui récupère la formule actuelle de la cellule et ne modifie que les 37 premiers caractères de celle-ci pour l'adapter à la nouvelle année *

* C'est de loin ma solution préférée puisque le nombre de lignes pourrait être amené à changer et les formules décalées.

Je pense que c'est faisable mais je sais pas comment
Vous avez de belles idées qui empliraient mon coeur d'une grande joie ?

 

[Catrice]

Re : Changer le classeur lié chaque année

Bonsoir,

Catrice> Ce qui serait bien, c'est que la macro se contente de modifier la formule en fonction de l'année. Il n'y aurait donc pas besoin de mettre quoi que ce soit à jour à une éventuelle modification du fichier de budget.
Et on règle comme ça le problème du chemin relatif/absolu

On peut faire ça en utilisant la formule du début avec un INDIRECT sur le N° d'année.
Mais il faut quand meme rapatrier les données.

 

[Martini]

Re : Changer le classeur lié chaque année

Merci Catrice mais je voudrais éviter de rappatrier les données.

J'ai essayé qqch et ça marche mais PAS QUAND EXCEL LE TRANSFORME EN CHEMIN ABSOLU (càd quand le classeur lié est fermé) :

Sub Test()
    Dim c As Range
    Dim Formule As String
    Dim annee As Integer
    Dim base As String
    ' A5 contient l'année
    base = "='[budget" & Range("a5") & ".xls]Budget global " & Range("a5")
        For Each c In Range("c8:c15")
    Formule = c.Formula
    Mid(Formule, 1, 36) = base
    c.Formula = Formule
        Next c
End Sub

Est-ce que je peux pas plutôt rechercher deux nombres de 4 chiffres dans la formule et les remplacer par le contenu de la cellule A5

 

[Catrice]

Re : Changer le classeur lié chaque année

Bonsoir,

J'avoue etre un peu predu, je ne sais pas où tu en est dans tes recherches.
Peut etre faudrait il faire un point...

Sinon, effectivement, tu peux remplacer 2007 par 2008 (ou autre année) par macro :

Sub Test()
Cells.Replace What:="2007", Replacement:="2008", LookAt:=xlPart
End Sub

Attention, il faut peut etre optimiser la recherche, sinon tout les 2007 seront remplacer dans le fichier.
Au lieu de "2007" peut etre "NomFichier 2007"

 

[Martini]

Re : Changer le classeur lié chaque année

Sinon, effectivement, tu peux remplacer 2007 par 2008 (ou autre année) par macro :

Je voudrais remplacer les deux suites de quatre chiffres de la formule par le contenu de la cellule A5 (2008, 2009, ...).

Est-ce qu'on ne peut pas rechercher un nombre de quatre chiffres à l'intérieur d'une expression alphanumérique ?

 

[Catrice]

Re : Changer le classeur lié chaque année

Bonjour,

C'est ce que fait le code proposé dans le post précédent.
Mais il y a d'autres méthodes.

On peut modifier le code comme suit :

Sub Test()
Cells.Replace What:="2007", Replacement:=Range("A5").Value, LookAt:=xlPart
End Sub