Automatiser la mise à jour d'un fichier Excel chaque semaine

Bonjour à toutes et tous.

J'ai actuellement une macro qui me permet de mettre en forme et de compiler 2 fichiers Excel : le 1er est la dernière version de mon fichier de travail, le 2nd contient les informations mises à jours.

Le fichier issu de la compilation permet de savoir si on peut ou non facturer des prestations de service. Il comporte une 1ere colonne qui est une liste déroulante (A facturer, Facturé, Ne pas facturer, Annulé/traité, ou rien). Selon le choix, la ligne se colorie de différentes couleurs.

Mon souci est que la macro fonctionne sur mon poste mais évidemment pas avec Excel en ligne. Donc quand je suis absent, personne ne peut le faire à ma place.

J'aimerai donc remplacer ma macro par l'équivalent disponible en ligne, afin que mes collègues puissent le faire sans moi.

L'idée ultime serait que nous ayons un fichier "Facturation" sur notre Sharepoint, avec une mise en forme définie, et que celui-ci se mette à jour automatiquement dès qu'on vient ajouter dans un dossier spécifique l'extraction fraîchement faite, et ce grâce à Power Automate.

Pourriez-vous m'orienter sur la méthode à utiliser pour arriver à mes fins ? Power Query ? Script ? Je suis un peu perdu, car je ne suis pas sûr qu'on puisse obtenir toutes les modifications que j'arrive à faire avec la macro, avec Power Query et/ou un script et tout ça en ligne...

Merci par avance !

Hello,

Si c'est de la modification et des traitements sur deux fichiers PowerQuery est votre ami !

Sans fichier on ne peut rien faire par contre si vous mettez une exemple de vos deux fichiers avec les différents résultats auxquels vous voulez arriver, nous pourrons sûrement vous aider

PS : PowerQuery pourra dynamiquement aller chercher les derniers fichiers posés sur le sharepoint si nécessaire

@+

Le fichier "facturation" est ce à quoi je voudrais arriver.

Le fichier "extraction erp septembre" est, comme son nom l'indique, une extraction des informations issues de notre logiciel ERP, contenant de nombreuses lignes (ici j'ai gardé seulement quelques lignes).

Le but recherché est donc :

- d'ajouter les lignes du fichier "extraction" aux lignes du fichier "Facturation", sauf si ces lignes existent déjà,

- supprimer plusieurs colonnes qui ne nous sont pas utiles,

- ajouter une colonne "commentaire" entre les colonnes "STATUT" et "Nom intervenant", et une colonne "ETAT" en 1ère colonne. Cette dernière comprendra une liste déroulante de choix (facturé, à facturer, annulé/traité, ne pas facturer, ou rien) et une mise en forme conditionnelle de la ligne entière selon le choix fait (à facturer = vert, ne pas facturer = rouge, annulé/traité = reste en blanc, facturé = gris foncé, et enfin à facturer (avec commentaire en colonne C) = orange).

Je voudrais aussi, tant qu'à faire, qu'une fois qu'une ligne a été traité (état "facturé" ou "annulé/traité"), elle disparaisse du feuillet et qu'elle soit ajoutée à un second feuillet.

Voilà voilà ... Je tatonne sur Power Query et avec l'enregistreur de scripts, je m'en remets à vous. Je ne sais pas si je peux faire tout ce que je voudrais avec l'une ou l'autre des solutions.

Encore merci.

Bonjour,

Peut-on créer une liste déroulante à partir d'un Script, ou à partir de Power Query ?

Merci !

Bonjour,

J'ai la même question concernant les mises en forme conditionnelles : peut-on les automatiser par un autre moyen que VBA ?

Merci.

édit : je me réponds à moi-même 😁 : oui, tu peux faire une MEFC via un script, mais à priori pas une liste déroulante.

Quelqu'un pour confirmer svp ?

Bonjour,

Je m'auto-corrige !

Oui, on peut faire une liste déroulante à partir d'un script, j'ai trouvé un exemple sur le net.

Maintenant il faut que je comprenne le script et que je le modifie pour qu'il colle à mon besoin.

Quelqu'un pourrait m'aider ? Je voudrais appliquer le script à toute la colonne A (sauf en-tête en A1) de ma feuille "En cours".

La liste des valeurs que peut prendre ma liste serait en feuille 2, de A2 à A5 :

function main(workbook: ExcelScript.Workbook) {
  // Get the values for data validation.
  const selectedRange = workbook.getSelectedRange();
  const rangeValues = selectedRange.getValues();

  // Convert the values into a comma-delimited string.
  let dataValidationListString = "";
  rangeValues.forEach((rangeValueRow) => {
    rangeValueRow.forEach((value) => {
      dataValidationListString += value + ",";
    });
  });

  // Clear the old range.
  selectedRange.clear(ExcelScript.ClearApplyTo.contents);

  // Apply the data validation to the first cell in the selected range.
  const targetCell = selectedRange.getCell(0,0);
  const dataValidation = targetCell.getDataValidation();

  // Set the content of the dropdown list.
  dataValidation.setRule({
      list: {
        inCellDropDown: true,
        source: dataValidationListString
      }
    });
}

Script trouvé sur le site de microsoft.

Bonne journée à toutes et tous, en espérant que quelqu'un soit en mesure de m'aider.

Voici le script utilisé pour qu'en colonne A soit créée une liste déroulante pouvant prendre les valeurs que je veux :

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();

    selectedSheet.getRange("A3:A600").getDataValidation().setRule({ list: { inCellDropDown: true, source: "Facturé, A facturer, Ne pas facturer, Annulé/traité" } });
    selectedSheet.getRange("A3:A600").getDataValidation().setPrompt({ showPrompt: true, message: "", title: "" });
    selectedSheet.getRange("A3:A600").getDataValidation().setErrorAlert({ showAlert: true, style: ExcelScript.DataValidationAlertStyle.stop, message: "", title: "" });
    selectedSheet.getRange("A3:A600").getDataValidation().setIgnoreBlanks(true);
}

Par contre, pour la mise en forme des lignes entières en fonction de la valeur en colonne A, je bloque.

En effet, je ne sais pas comment appliquer la mise en forme à la ligne entière. Pour le moment ça ne fonctionne que sur la cellule en colonne A.

function main(workbook: ExcelScript.Workbook) {

    const shAttendance = workbook.getWorksheet("Sheet1");
    const rngAttendanceData = shAttendance.getRange("A2:A600");

    let conditionalFormat = rngAttendanceData.addConditionalFormat(
        ExcelScript.ConditionalFormatType.cellValue
    ).getCellValue();

    let rule: ExcelScript.ConditionalCellValueRule = {
        formula1: "=\"A facturer\"",
        operator: ExcelScript.ConditionalCellValueOperator.equalTo
    };
    conditionalFormat.setRule(rule);
    let format = conditionalFormat.getFormat();
    format.getFill().setColor("#039e3C");

    // Add 2nd conditional format
    conditionalFormat = rngAttendanceData.addConditionalFormat(
        ExcelScript.ConditionalFormatType.cellValue
    ).getCellValue();
    rule = {
        formula1: "=\"Facturé\"",
        operator: ExcelScript.ConditionalCellValueOperator.equalTo
    };
    conditionalFormat.setRule(rule);
    format = conditionalFormat.getFormat();
    format.getFill().setColor("#4a4545");

    // Add 3rd conditional format
    conditionalFormat = rngAttendanceData.addConditionalFormat(
      ExcelScript.ConditionalFormatType.cellValue
    ).getCellValue();
    rule = {
        formula1: "=\"Ne pas facturer\"",
        operator: ExcelScript.ConditionalCellValueOperator.equalTo
    };
    conditionalFormat.setRule(rule);
    format = conditionalFormat.getFormat();
    format.getFill().setColor("#f50a12"); // Update as needed

}

Hey,

Toujours pas eu le temps de me pencher sur le sujet mais je regarde régulièrement tes mises à jour et ça m’a l’air bien complexe tout ça

J’espère trouver du temps rapidement

@+

Merci !

En fait ce n'est pas si complexe.

Je découpe le process en plusieurs phases :

- la mise en forme des données par un Script (en cours)

- la compilation des lignes existantes avec les nouvelles et la suppression des doublons (en réflexion, j'ai des bases)

- le déclenchement de tout ceci via un flux Power Automate.

Bonjour,

Voici donc le code pour la mise en couleur de lignes entières, selon le contenu de la colonne A (avec en bonus, une couleur différente si j'ai, en plus de "A facturer" en colonne A, un quelconque commentaire en colonne B, qui provoque un passage en Orange plutôt qu'en vert) :

function main(workbook: ExcelScript.Workbook) {
    let conditionalFormatting: ExcelScript.ConditionalFormat;

    let selectedSheet = workbook.getActiveWorksheet();
    conditionalFormatting = selectedSheet.getRange().addConditionalFormat(ExcelScript.ConditionalFormatType.custom);
    conditionalFormatting.getCustom().getFormat().getFill().setColor("yellowgreen");
    conditionalFormatting.getCustom().getRule().setFormula("=AND($B1=\"\", $A1=\"A facturer\")");

    conditionalFormatting = selectedSheet.getRange().addConditionalFormat(ExcelScript.ConditionalFormatType.custom);
    conditionalFormatting.getCustom().getFormat().getFill().setColor("orange");
    conditionalFormatting.getCustom().getRule().setFormula("=AND($B1<>\"\", $A1=\"A facturer\")");

    conditionalFormatting = selectedSheet.getRange().addConditionalFormat(ExcelScript.ConditionalFormatType.custom);
    conditionalFormatting.getCustom().getFormat().getFill().setColor("Red");
    conditionalFormatting.getCustom().getRule().setFormula("=left($A1,15)=\"Ne pas facturer\"");

    conditionalFormatting = selectedSheet.getRange().addConditionalFormat(ExcelScript.ConditionalFormatType.custom);
    conditionalFormatting.getCustom().getFormat().getFill().setColor("Grey");
    conditionalFormatting.getCustom().getRule().setFormula("=left($A1,7)=\"Facturé\"");

    conditionalFormatting = selectedSheet.getRange().addConditionalFormat(ExcelScript.ConditionalFormatType.custom);
    conditionalFormatting.getCustom().getFormat().getFill().setColor("Grey");
    conditionalFormatting.getCustom().getRule().setFormula("=left($A1,13)=\"Annulé/traité\"");
}

Bonjour,

J'ai beaucoup avancé sur mon 1er script, dont j'ai simplifié les actions (celles supprimées iront dans un 2nd script), mais celui ci-dessous me fait quelque chose de bizarre.

Cette action là :

selectedSheet.getRange("2:" + `${lastRowNumber}`).delete(ExcelScript.DeleteShiftDirection.up);

me supprime quasiment toutes les lignes de mon tableau, sauf la ligne d'en-tête et il me reste 1 seule ligne (celle qui se trouvait en dernière position)...

Sauriez-vous m'aiguiller dans la résolution de ce problème ?

Grosso modo, je supprime des colonnes inutiles, je modifie quelques en-têtes, j'ajoute de nouvelles colonnes, puis je définie une table contenant toutes les lignes non-vides, et ensuite je veux supprimer les lignes dont je n'ai pas besoin (selon les valeurs dans plusieurs colonnes). Pour cet exemple, je veux supprimer toutes les lignes qui ne contiennent pas "94820" en colonne "code site de vente". Après je supprimerais les lignes qui contiennent "1-En retard de planification" en colonne "STATUT", puis encore 2 filtres derrière.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let lastRowNumber = selectedSheet.getUsedRange().getLastRow().getRowIndex()
    console.log(lastRowNumber);

    // Suppression des colonnes inutiles
    selectedSheet.getRange("2:2").delete(ExcelScript.DeleteShiftDirection.up);
    selectedSheet.getRange("AV:BL").delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("AN:AT").delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("AG:AL").delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("Z:AE").delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("R:U").delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("M:P").delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("H:H").delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("D:D").delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("B:B").delete(ExcelScript.DeleteShiftDirection.left);

    // Insertion d'une colonne avant la colonne A
    selectedSheet.getRange("A:A").insert(ExcelScript.InsertShiftDirection.right);
    // Renommer la cellule A1
    // selectedSheet.getRange("A1").setValue("Commentaires");
    // Insertion d'une colonne avant la colonne A
    selectedSheet.getRange("A:A").insert(ExcelScript.InsertShiftDirection.right);
    // Renommer les cellules A1, B1 et C1
    selectedSheet.getRange("A1:C1").setValues([["Etat", "Commentaires", "STATUT"]]);

    // Renommer les cellules d'en-tête
    selectedSheet.getRange("L1").setValue("Fact Auto");
    selectedSheet.getRange("T1").setValue("Custom ID");
    selectedSheet.getRange("R1").setValue("Facturation à venir");
    selectedSheet.getRange("M1").setValue("Date début intervention");
    selectedSheet.getRange("N1").setValue("Date fin intervention");
    selectedSheet.getRange("O1").setValue("Semaine fin");

    // Création du CustomID en colonne T
    selectedSheet.getRange("T2").setFormulaLocal("=CONCAT(D2;E2;P2)");
    selectedSheet.getRange("T2").autoFill("T2:T" + `${lastRowNumber}`, ExcelScript.AutoFillType.fillDefault);

    // Définition de la table Tableau1, de la cellule A1 à la dernière ligne non-vide en colonne T
    let tableau1 = workbook.addTable(selectedSheet.getRange("A1:T" + `${lastRowNumber}`), true);
    // Change the visibility of filter button on table newTable
    tableau1.setShowFilterButton(false);
    // Change the visibility of banded rows on table newTable
    tableau1.setShowBandedRows(false);

    //tableau1 = workbook.getTable("Tableau1");
    // Toggle auto filter on table tableau1
    //tableau1.getAutoFilter().apply(tableau1.getRange());

    lastRowNumber = selectedSheet.getUsedRange().getLastRow().getRowIndex()
    tableau1.getColumnByName("Code site de vente").getFilter().applyCustomFilter("<>*94820*");
    console.log(lastRowNumber);
    // Delete range 108:639 on selectedSheet
    selectedSheet.getRange("2:" + `${lastRowNumber}`).delete(ExcelScript.DeleteShiftDirection.up);
    tableau1.clearFilters();

    //tableau1.getColumnByName("STATUT").getFilter().applyValuesFilter(["1-En retard de planification"]);
    //selectedSheet.getRange("2:" + `${lastRowNumber}`).delete(ExcelScript.DeleteShiftDirection.up);
    //selectedSheet.getRange("2:1000").delete(ExcelScript.DeleteShiftDirection.up);
    //tableau1.clearFilters();
  }

Hello,

Franchement là je saurai pas t'aiguiller...

Je laisse la main à quelqu'un qui connaît cette partie scripting

J'essaie de te faire une proposition moins contraignante via PowerQuery

Petite question, l'identifiant unique d'une ligne c'est le N° Affaire concaténer avec le nom de l'intervenant ? Ou est ce que c'est le N° DI ? J'ai l'impression que le N° de DI est unique par ligne donc je vais me baser la dessus pour la correspondance des lignes déjà existantes

@+

Merci pour ton aide !

Je me sers du "CustomID" comme identifiant (notamment pour supprimer les doublons dans le 2nd Script), car même le N° DI peut être commun à plusieurs intervenants.

Ce CustomID est la concaténation du nom de l'intervenant, du N° DI et du N° affaire.

Hello,

Bon du coup j'ai géré la partie mise en forme conditionnelle et comparatif des lignes que t'as déjà avec ton extraction. On affinera après pour la partie "Traité"

Comment ça marche :

- Onglet Param:

a) Liste déroulante "ETAT" : permet de pas avoir à gérer en dur la validation de données. Demain si t'as besoin de rajouter un ETAT, tu le mets à la suite et ça mettra à jour automatiquement tes listes déroulantes

b) Chemin du fichier de ton extraction à remplir dans la cellule indiquée

- Onglet "Récup lignes non existante" :

a) Dans la requête PowerQuery, je récupère ton onglet Sheet1, je le traite pour les formats de nombre etc etc, suppression de colonnes et également je compare avec les lignes présentes dans ton onglet "En cours" pour ne récupérer que les lignes de ton ERP avec un customID qui diffère.

b) Pour mettre à jour c'est simple, tu renseignes le chemin de ton fichier dans l'onglet Param et après tu fais clic droit puis actualiser sur le tableau vert.

c) J'ai fait un test avec 3 lignes dans ton onglet "En cours", tu peux les supprimer en sélectionnant les trois lignes puis en faisant clic droit puis "Supprimer lignes de tableau". Quand tu les auras supprimer et que tu auras réactualiser le tableau vert, tu verras trois lignes s'ajouter.

- Enfin, j'ai mis à jour tes règles de mise en forme conditionnelle

Dis moi si c'est ce que tu recherches

@+

EDIT : modification avec filtre customID à 10h47

Bonjour Baroute78,

Navré d'avoir mis autant de temps à répondre, mon service info m'a supprimé tous mes mots de passe, mon historique, mes favoris etc.

Très pratique Bref.

Tout d'abord merci pour ce que tu as fait, et pour le temps passé à le faire ! Un grand merci.

J'ai testé sur mon poste, et malheureusement ça ne fonctionne pas : je rentre l'adresse d'une extraction de l'ERP, et le message d'erreur suivant s'affiche :

Et sur Excel online :

Help ...

Bonne fin d'année à tous !