XL 2019 Copier une plage dans un fichier fermé en VBA

[Amilo]

Bonjour le forum,

J'ai un fichier contenant le même nombre de colonnes et les même titres avec un certain nombre de valeurs sous forme d'un tableau structuré
Ce dernier est créé à partir d'une requête Power query
Le nombre de lignes varie selon la source qui a été connectée à la requête.
Le fichier ressemble à celui en pièce jointe avec toujours au même endroits des titres de colonnes sans valeurs (cellules vides)

Après actualisation des données, j'aimerais exporter toutes les valeurs sans les titres de colonne dans un autre fichier fermé qui se trouve dans un autre dossier.
Je souhaiterais copier les valeurs à chaque fois dans la même feuille "Feuil1" et à partir de la ligne 2 de cet autre fichier fermé.
A chaque exécution du code VBA, j'aimerais écraser les anciennes valeurs et coller celles qui ont été chargées dans le fichier Power query

En vous remerciant par avance

Cordialement

 

[kiki29]

Salut, à voir si curieux : Lire et écrire dans les classeurs Excel fermés

 

[Amilo]

Salut, à voir si curieux : Lire et écrire dans les classeurs Excel fermés

Bonjour kiki29,
Merci pour votre réponse,
Je sais dans quoi m'investir ce WE
Cordialement

 

[oguruma]

Je dois avoir un truc comme ça dans ma caisse à outils.... faut que je retrouve.... une classe ou un machin comme ça

 

[Amilo]

Merci @oguruma , ça me sauverait mon WE si vous le trouvez
Cordialement

 

[oguruma]

Merci @oguruma , ça me sauverait mon WE si vous le trouvez
Cordialement

Hi, de toutes manières nous sommes déjà presqu'à Noël et Dimanche Certainement Nouvel An. Donc un peu de VBA pour passer le temps, pourquoi pas

 

[oguruma]

Hello, juste une petite question un peu Basique, est-ce réellement nécessaire que tu lances dans cette aventure ?
Est-ce lié :
- des pb de sécurité t'interdisant d'ouvrir certains classeurs externes ?
- à un traitement batch tournant par exemple chaque nuit pour lequel il faut mettre à jour des fichiers Excel ?
- à une mise à jour en série sur plusieurs classeurs de même nature que tu dois rafraîchir... ?
- à une application excel des métiers moa qui ne sont pas très techniques et pour qui il faut automatiser au max les actions "end user" ?

si ça répond à l'une de ces questions ou à un mixte de une ou plusieurs d'entre elles, je dirai OUI.
Dans la négative si c'est du ponctuel, occasionnel et limité à un ou deux voir trois. classeurs... sauf erreur je crois que c'est plus simple de faire un bout de code qui va ouvrir le fichier excel, y copier la plage de données à l'endroit voulu et de le sauvegarder et fermer.

Sinon la mise en oeuvre passera par de l'ADO... en fait c'est comme si tu établissais une connexion à une base Access.ou autre comme Oracle.. car on va faire comprendre à Excel que le classeur Excel va se comporter comme une base Access et intervenir avec des instructions Update comme si on était dans une base de données Access, Oracle, ou autre. Ca veut dire que pour une ou plusieurs plages de données on passerait par l'équivalent d'un curseur pour mettre à jour les éléments un à un... Tu situes ? Bon en plus si ton tableau Excel "est mal fagoté" ça va être Teuchi...
Idéalement il faudrait que la Maj cible soit de forme Tableau Structuré (hé oui encore lui). En fait comme une table d'une structure similaire à celles d'Access.
J'ai été peut-être un peu trop technique mais c'est pour bien te sensibiliser à la maintenance que tu devras faire derrière cela ? à toi de voir, je t'ai peut-être dans le doute là ? dsl.
Ce que j'ai tournais sur une V2010 pour mettre à jour deux / trois bricoles..... rien de plus

 

[Amilo]

Bonjour @oguruma ,

Le tableau final est issu de power query donc déja en tableau structuré.
En complément des requêtes, j'ai également une macro qui me sert à naviguer dans l'explorateur de dossiers et de récupérer automatiquement le chemin du fichier Excel souhaité (Path),
Une fois le chemin .xlsx récupéré dans une cellule avec un paramètre Path défini, ma requête Power query exécute toutes les transformations sur le Excel.Workbook(Web.Contents("https://.......") via le Path (Sharepoint)

Ce fichier a pour extension .xlsm qui n'est pas au format admis pour un export dans le système ERP

Seuls les format .xlsx, xls et un 3ème (csv il me semble) sont acceptés par le module d'import de l'ERP
Actuellement, nous faisons un copier/coller manuel du tableau .xlsm vers le template .xlsx

ça prend quelques secondes d'ouvrir les 2 fichiers puis de refermer ensuite le .xlsx avant export.
Je pensais alors automatiser et accélérer ce processus en VBA

Cordialement

 

[oguruma]

hello, si j'ai bien compris ton fichier .xlsx ou .xls sert en fait d'interface pour mettre à jour ton ERP. C'est bien cela ?
La génération automatique du fichier d'interface au format Excel poserait problème. C'est bien cela ?
Un fichier d'interface .csv pourrait être l'alternative à condition que ce type soit admis (ça resterait à confirmer). C'est bien cela aussi ?

Pourquoi ces questions : j'essaie de recentrer l'entrée, la sortie et le besoin.

Alors solution peut-être simpliste mais qui t'enlèverait l'épine du pied consistant à passer par de l'ADO (maj fichier fermé) : générer le fichier .csv
Pour cela, étudier la structure d'enregistrement attendue avec ce type de format et ton fichier où si se situe ta Macro génère ce fichier. Ensuite passer certainement par les process automatiques de ton ERP pour y injecter ce .csv.

La génération de ce .csv passe les mécanismes de gestion de fichiers texte en VBA.
SI tu n'es pas à l'aise avec cela voici le package d'instructions à utiliser

Open OPEN_NOM_DE_FICHIER.csv For Output As 1
(As 1 = N° de canal qui peut être obtenu automatiquement par l'instruction FreeFile --> conseillé)
Print #1 (n° de canal), ENREGISTREMENT
ENREGISTREMENT : contient les données séparées par un ";"
Attention voir si l'ERP réclame un en-tête (titre colonne)
Close #N° de canal
mais tu peux faire Close sans N° de canal
Avant tout open, il est recommandé de faire un Close qui ferme tous les fichiers qui pourraient être encore ouvert.... conseil à faire si tu plantes avec les fichiers ouverts....
Print #Canal, DONNEES;
Le ";" évite le retour à la ligne (donc pas de CR/LF ou dit autrement x0Dx0A) (chr(13)&chr(10) pour être complet
Print #1, data1;
Print #1, data2;
Print #1, data3;
Print #1, data4
==> à l'issue de data4 tu auras là le x0DxOA

voilà tu as le détail peut-être d'une solution plus simple.

 

[Amilo]

Bonjour @oguruma ,

Le fichier .xlsx sert effectivement d'interface pour générer des écritures dans l'ERP via un module existant à cet effet.
Et merci pour ces explications mais je n'ai pas le niveau requis pour tout comprendre
En tout cas, je pars des données d'un .xlsm pour les transférer de préférence vers un .xlsx
Pour info, les entêtes de colonnes sont déjà préremplis en première ligne dans le .xlsx

Je ferai sinon quelques essais en fIn de journée.

Cordialement

 

[Cousinhub]

Bonjour,
Question peut-être idiote... ou simpliste...

...
Seuls les format .xlsx, xls et un 3ème (csv il me semble) sont acceptés par le module d'import de l'ERP

Pourquoi ne pas enregistrer ton fichier en .xls au lieu de .xlsm, comme ton ERP n'en veut pas?
Peut-être?
(Mais je pense que tu as déjà essayé...)
Bonne journée, et bon W-E

 

[Amilo]

Bonjour @Cousinhub ,

Dans le fichier, j'ai un mélange de Power query, de VBA et un bouton de commande pour appeler une macro.
Effectivement, le VBA fonctionne également avec l'extension .xlsx.
Sauf erreur de ma part, les anciennes versions Excel au format .xls ne prenaient pas en charge les macros

Sinon, la raison principale est le bouton de commande qui se trouve dans un coin de l'onglet.
Il ne faut aucun objet, colonnes ou lignes vides en dehors du modèle de données à exporter.

J'ai une autre version avec le bouton de commande et le chemin Path, se trouvant dans un autre onglet.
Mais c'est une bonne piste à tester avec cette version, je vais déjà enregistrer en xlsx et faire un essai.

Je vous tiens informé, dés que je me connecte sur l'ordinateur professionnel probablement en fin de soirée

Merci

Cordialement

 

[patricktoulon]

bonjour

Effectivement, le VBA fonctionne également avec l'extension .xlsx.

a ben ca alors première nouvelle

Sauf erreur de ma part, les anciennes versions Excel au format .xls ne prenaient pas en charge les macros

depuis quand le "xls" ne prend pas en charge les macros?
là on marche sur la tète

 

[Amilo]

Bonjour,
Le xlsm que j'ai enregistré sous xlsx fonctionne malgré le message d'alerte sur la prise en charge des macros. J'étais moi même surpris.
Pour le xls, certainement qu'il prend en charge les macros. Je ne l'utilise plus depuis plus de 10 ans. Alors vous avez certainement raison. J'aurais du faire quelques recherches sur les extensions et leur propriété. Mais probablement que, dans quelques années, on ne "parlera" plus du. xls
Cordialement

 

[patricktoulon]

re

Le xlsm que j'ai enregistré sous xlsx fonctionne malgré le message d'alerte sur la prise en charge des macros. J'étais moi même surpris.

c'est pas surpris que tu devrais être mais inquiet