Microsoft 365 Transformation et consolidation en VBA

[Amilo]

Bonjour le forum,

Je sollicite svp votre aide pour une solution en VBA,
Je vous mets en pièces jointes les fichiers exemples et les explications ci-dessous :

- J'ai dans un dossier nommé "Dossier 03_2021" comportant plusieurs fichiers avec une table de structure identique
J'ai mis 2 fichiers exemples ("Fichier A.xlsm" et "Fichier B.xlsm" mais il peut en avoir en réalité une dizaine
Tous les mois j'aurai un nouveau dossier avec à l'intérérieur un certain nombre de fichiers
- Dans chaque fichier se trouve une table de données dans un onglet que j'ai nommé dans cet exemple "FA"

Le but est de retenir et d'exporter uniquement les lignes pour lesquelles :
. il y a un montant dans la colonne E ("MT HT")
. Et une référence sous la forme "P349XXXX" dans la colonne A ("IA")

Toutes les lignes à 0 ne sont pas à retenir quelque soit la forme de la référence en colonne A
Il y a parfois des sous-totaaux entre des plages de données qui ne sont pas à prendre en compte (exemple cellule E30 de l'onglet "FA")
Il peut aussi avoir des montants TTC manquants commme en cellule F35,
Cette ligne 35 remplit les critères même si la cellule TTC est vide car il s'agît d'une omission.
Dans tous les cas, un contrôle visuel est effectué pour corriger ce type d'erreur

Vous trouverez également dans l'onglet "Export FA" davantage de précisions avec le résultat souhaité,
Ce dernier présente une version concue avec un onglet dans chaque fichier où j'irais copier/coller manuellement le réultat de chaque fichier dans un fichier de consolidation.

Une autre version imaginée serait d'avoir tous les résultats dans le fichier de consolidation sans créer un onglet intermédiaire "Export"
Le résultat serait obtenu après sélection de la plage à transformer pour chaque fichier à ouvrir.
Mais je pense que c'est beucoup plus compliqué à mettre en ouevre de cette sorte

Je suis bien sûr preneur de toute proposition

En vous remerciant d'avance de vos réponses et de votre aide

Cordialement

 

[yal]

Une version sans création de fichier. Chaque mois une nouvelle feuille est créée en première feuille du classeur Consolidations qui doit exister au préalable.

 

[Staple1600]

Bonsoir le fil

Test OK au boulot aujourd'hui

Sub Creation_Dossier_SharePoint() 'test OK XL 2019
With CreateObject("WScript.Network")
    .MapNetworkDrive "K:", "\\serveurSharePoint\dossier", False
    MkDir "K:\TestA"
    .RemoveNetworkDrive "K:"
End With
End Sub

NB: Il faut mapper un lecteur réseau avant de pouvoir créer le nouveau dossier.

 

[yal]

Bonsoir Staple1600
Cela règlerait il aussi la question de la création du fichier généré par le code?

 

[Staple1600]

Re

J'ai pas eu le temps de tester plus avant car au boulot, je suis censé bosser
(et pas titiller mon VBA dans mon tableur préféré)
J'essaierai demain.

 

[yal]

Pas cool ton employeur

 

[Amilo]

Bonsoir le fil

Pour info (au cas où cela pourrait dépanner)
Au boulot, je connecte un lecteur réseau sur mes ressources Sharepoint
Dans cette configuration, j'arrive à exécuter mes codes VBA sur les fichiers
(sans passer par l'interface web de Sharepoint)

Bonsoir @Staple1600 , @yal , le forum,
Merci Staple1600 pour votre message,
je ne sais pas si on parle de la même chose mais nous avons également Sharepoint (Cloud) et l'explorateur synchronisé à Sharepoint.

Toute fois, il me semble qu'il nous a été recommandé d'utiliser de préférence Sharepoint plutôt que l'explorateur, probablement en raison de bug ou de retard dans la synchronisation.
Mes précédents tests étaient réalisés depuis l'explorateur puis sur Sharepoint

Sinon, de manière générale les codes VBA fonctionnent sans problème depuis Sharepoint, j'ai quelques fichiers dont l'un avec de nombreux Userforms et un module contenant une centaine de lignes de codes. Sinon aucun des fichiers ne génèrent la création de nouveaux fichiers.

Il faut ouvrir le fichier en mode "Application" pour éxcuter le code sinon il me semble que cela ne fonctionne pas.

Merci et bonne soirée

P.S : Je m'empresse de découvir et répondre aux messages suivants , j'ai vu que yal a eu la bienveillance de me proposer une autre version sans passe par la création de nouveaux fichiers.

Cordialement

 

[Amilo]

Une version sans création de fichier. Chaque mois une nouvelle feuille est créée en première feuille du classeur Consolidations qui doit exister au préalable.

@yal ,
désolé pour le retour tardif,
j'ai eu une journée chargée et merci beaucoup pour le nouveau fichier.
Je n'ai pas encore eu l'occasion de le tester au travail mais je suis persuadé qu'il fonctionnera sans problème.
J'essayerai demain, plutôt en fin de journée et vous tiens informé.

Bonne soirée

 

[Amilo]

Bonsoir le fil

Test OK au boulot aujourd'hui

Sub Creation_Dossier_SharePoint() 'test OK XL 2019
With CreateObject("WScript.Network")
    .MapNetworkDrive "K:", "\\serveurSharePoint\dossier", False
    MkDir "K:\TestA"
    .RemoveNetworkDrive "K:"
End With
End Sub

NB: Il faut mapper un lecteur réseau avant de pouvoir créer le nouveau dossier.

@Staple1600 ,

Merci, je testerai également demain ce bout de code pour voir,
Je ne sais pas trop, si mapper un lecteur et le fait de synchroniser signifient la même chose mais j'essayerai aux emplacements que je dispose notamment l'explorateur et le Sharepoint.

Bonne soirée

Cordialement

 

[Amilo]

@yal ,
Je me suis avancé trop vite en pensant qu'il n'y aurait plus de raison que cela ne fonctionne pas ,
Sur mon PC perso, cela fonctionne très bien mais malheureusement, même message d'erreur sur le PC de travail
(Pour info, j'ai également essayé en laissant ouvert le fichier "Consolidations")

Si le fichier principal (celui avec le bouton de commande est stocké dans un endroit en lecture seule ça ne changera rien.

Vous aviez raison dans votre message #26, effectivement du coup je pense que cela ne changera pas non plus si les données sont stockées dans le même fichier que la bouton de commande VBA.

Seule alternative et sans devoir passer par le "Path" Windows, serait je pense que chaque fichier "Source" ait son propre code avec un onglet pour récupérer le résultat donc dans le même fichier.
Puis charge à la personne de copier/coller manuellement chaque résultat dans un fichier récapitulatif mensuel.

@Staple1600 , j'ai testé également votre code VBA, mais même message, je pense que je ne dois pas avoir le mappage que vous évoquez.

Merci encore

Cordialement

 

[yal]

On peut encore essayer de stocker les résultats dans le même fichier que celui contenant la macro en ajoutant des feuilles.

 

[yal]

A tester

 

[Amilo]

@yal , malheureusement même problème avec cette version,

Je pensais plutôt à un code intégré dans chacun des fichiers "Source" et non plus dans "Consolidations",
Et que le résultat soit généré à l'intérieur du fichier correspondant

Cordialement

 

[yal]

Je ne vois pas ce que cela changerait. Non je pense qu'il ne vous reste plus qu'à comprendre comment fonctionne SharePoint el là je ne vous suis d'aucune utilité vu que je n'y ai pas accès et que je ne l'ai jamais utilisé.
Bon courage

 

[Amilo]

Merci @yal pour tout le travail que vous avez accompli,
Votre fichier me servira j'en suis certain, je l'utiliserai à l'emplacement où il fonctionne

Je regarderai aussi pour éventuellement, une solution Power query,

Bonne journée

 

[Staple1600]

Bonsoir

Précisions
Concernant Sharepoint
Test fait avec Office 2019 sur un PC W10Pro
Je regarderai la version de Sharepoint demain

Pour faire le test, j'ai procédé ainsi:
"\\serveurSharePoint\dossier" est le chemin d'une ressource Sharepoint
(Sur cette ressource, j'ai connecté un lecteur réseau manuellement)
Donc le VBA créé temporairement un autre lecteur réseau pour créer le dossier puis déconnecte ce lecteur réseau.
Si je vais ensuite dans dossier, j'ai un nouveau dossier nommé TestA