XL 2016 [RESOLU] Macro avec 568 variables...

jabenj · 28 Juillet 2020

Bonjour à tous et bel été !

J'ai besoin d'aide sur un code qui me rend dingue depuis qques temps...

Problématique :
1. J'ai récupéré une macro très bien foutue et fonctionnelle ici : http://www.hobbesworld.com/bureautique/tutorielrecupinfo.php
2. J'ai donc créé mes formulaires avec 568 champs tous nommés uniquement.
3. Je n'arrive pas à tous les récupérer : VBA m'envoie paître car trop nombreux.. (malgré la "mise à la ligne" avec les _ / j'ai même tenté sans succés de lister les variables sur une feuille excel et tenter de l'appeler via vb... )
4. j'aimerai ajouter en amont (colonne A) le nom du fichier récupéré

Questions :
A. Comment faire pour récupérer l'intégralité des champs créés sous word svp ?
B. Comment faire pour ajouter le nom du fichier en amont de la récupération svp?

Documents :
a. la liste des variables en txt
b. Le code au cas où :

VB:

Sub import_client()
Dim Fich As Worksheet
Set Fich = ThisWorkbook.Worksheets("BDD resultats")
chemin = "K:\5 Transverse\CLIC\Rapport d'Activité CLIC-RA 2019\RETOURS\"
mesfichiers = Dir(chemin & "*.doc")
Dim Variables As Variant
Variables = Array("denomination", "adresse", "statut_jurid", "Nom_Resp", "Fonc_resp")

nb_Champs = 5
num_row = 1
i = 0

For i = 0 To nb_Champs - 1
  Fich.Cells(num_row, i + 1) = Variables(i)
Next i

Set FichierWord = CreateObject("word.application")
FichierWord.Visible = True
FichierWord.DisplayAlerts = False

Do While mesfichiers <> ""
  If mesfichiers <> "." And mesfichiers <> ".." And mesfichiers <> "clients.doc" Then
    monDocument = chemin & mesfichiers
    FichierWord.documents.Open Filename:=monDocument, ReadOnly:=True
    num_row = num_row + 1
    num_col = 1
    For i = 0 To nb_Champs - 1
      Fich.Cells(num_row, i + 1) = FichierWord.activedocument.formfields(Variables(i)).result
    Next i
    FichierWord.documents.Close (0)
  End If
  mesfichiers = Dir
Loop
FichierWord.Quit

End Sub

Je vous remercie infiniment d'avance pour ce que vous pourrez me dire de faire !!!
A très bientôt (et que ça ne vous empêche pas de profiter de vos vacances pour ceux qui en ont 😉 !

sylvanu · 28 Juillet 2020

Bonjour JabenJ,
Requête bizarre. Un formulaire de 568 champs?
Au moins pour le point 3 avec cette aide :

Fusion de deux tableau VBA - Macros et VBA Excel

Chères amies chers amis du forum Je souhaites fusionner deux tableau VBA de tailles différentes par une méthode ou algo plus optimal que boucle plus redim preserve. L objectif final est d obtenir un array qui

www.developpez.net

Voir PJ.
Au moins tous les champs finissent dans l' Array;
( Bien vérifié que je n'ai pas fait d'erreur, mais rectifier sera simple )

jabenj · 29 Juillet 2020

Excellent, merci bcp pour ton prompt retour !!
En effet, c'est bien vu la scission des variables .. j'ai adapté ton idée et fait ça dans le code : et ça marche !!!

Merci bcp pour l'astuce !!!

VB:

Sub import_client()
Dim Fich As Worksheet
Set Fich = ThisWorkbook.Worksheets("BDD résultats")
chemin = "K:\1 Secrétaires -Assistants\RELAIS AUTONOMIE\Rapport d'Activité CLIC-RA\RETOURS\"
mesfichiers = Dir(chemin & "*.docm")
Dim Variables As Variant
Dim variables2 As Variant
Dim variables3 As Variant
Dim variables4 As Variant
Variables = Array("denomination", "adresse", "statut_jurid", "Nom_Resp", "Fonc_resp", "clicra_denom", "clicra_adresse", "clicra_tel", "clicra_mail", _
"clicra_web", "respra_nom", "respra_fonction", "respra_tel", "respra_mail", "Nom_Prénom1", "Statut1", "Fonction1", "Formation1", "Quotite1", "Tps_AIO1", _
"Tps_EC1", "Tps_Pre1", "Tps_AGS1", "Tps_typemission1", "Tps_ETP1", "Nom_Prénom2", "Statut2", "Fonction2", "Formation2", "Quotite2", "Tps_AIO2", _
"Tps_EC2", "Tps_Pre2", "Tps_AGS2", "Tps_typemission2", "Tps_ETP2", "Nom_Prénom3", "Statut3", "Fonction3", "Formation3", "Quotite3", "Tps_AIO3", _
"Tps_EC3", "Tps_Pre3", "Tps_AGS3", "Tps_typemission3", "Tps_ETP3", "Nom_Prénom4", "Statut4", "Fonction4", "Formation4", "Quotite4", "Tps_AIO4", _
"Tps_EC4", "Tps_Pre4", "Tps_AGS4", "Tps_typemission4", "Tps_ETP4", "Nom_Prénom5", "Statut5", "Fonction5", "Formation5", "Quotite5", "Tps_AIO5", _
"Tps_EC5", "Tps_Pre5", "Tps_AGS5", "Tps_typemission5", "Tps_ETP5", "Nom_Prénom6", "Statut6", "Fonction6", "Formation6", "Quotite6", "Tps_AIO6", _
"Tps_EC6", "Tps_Pre6", "Tps_AGS6", "Tps_typemission6", "Tps_ETP6", "Nom_Prénom7", "Statut7", "Fonction7", "Formation7", "Quotite7", "Tps_AIO7", _
"Tps_EC7", "Tps_Pre7", "Tps_AGS7", "Tps_typemission7", "Tps_ETP7", "Comm_moyperso", "nom_local", "adresse_local", "horaires_local", "oui_lieupr", _
"non_lieupr", "comm_lieuprinc", "oui_loc_indé", "non_loc_indé", "comm_loc_indé", "oui_esp_conf", "non_esp_conf", "comm_esp_conf", "oui_acc_pub", _
"non_acc_pub", "comm_acc_pub", "oui_esp_pc", "non_esp_pc", "comm_esp_pc", "oui_esp_doc", "non_esp_doc", "comm_esp_doc", "oui_réu", "non_réu", _
"comm_réu", "oui_loc_pret", "non_loc_pret", "comm_loc_pret", "oui_sign_ext", "non_sign_ext", "comm_sign_ext", "oui_acces_pmr", "non_acces_pmr", _
"comm_acces_pmr", "oui_transp_comm", "non_transp_comm", "comm_transp_comm", "sit_loc_CV", "sit_loc_PERI", "sit_loc_OTHER", "sit_loc_comm", _
"ouverture_rep_tsf", "oui_pc_pro", "non_pc_pro", "type_pc_pro", "cb_pc_pro", "oui_pc_pro_reseau", "non_pc_pro_reseau", "oui_pc_pro_web", _
"non_pc_pro_web", "oui_pc_pro_how", "non_pc_pro_why", "oui_pc_user", "non_pc_user", "what_pc_user", "comb_pc_user", "oui_pc_user_reseau", _
"non_pc_user_reseau", "oui_pc_user_web", "non_pc_user_web", "oui_pc_user_how", "non_pc_user_why", "oui_SI", "non_SI", "perimetre_SI", _
"oui_soft_user", "non_soft_user", "which_soft_user", "others_soft_user", "oui_mob_tools", "non_mob_tools", "type_mob_tools", "comb_mob_tools")

variables2 = Array("oui_vehicule", "non_vehicule", "details_vehicule", "oui_livret_acc", "non_livret_acc", "comm_livret_acc", "oui_charteDL", "non_charteDL", _
"comm_charteDL", "oui_rglt_fct", "non_rglt_fct", "comm_rglt_fct", "oui_dpd", "non_dpd", "comm_dpd", "oui_projet_sce", "non_projet_sce", _
"comm_projet_sce", "oui_DIPC", "non_DIPC", "comm_DIPC", "oui_pap", "non_pap", "comm_pap", "oui_grille_sit_compl", "non_grille_sit_compl", _
"com_grille_sit_compl", "oui_gerontog", "non_gerontog", "comm_gerontog", "oui_outils_comm_int", "non_outils_comm_int", "comm_outils_comm_int", _
"partic_hbt_ou_PA", "montant_moy_commune", "montant_tot_commune", "Autrefin1", "Autrefin2", "Autrefin3", "Valmateriel", "Valperso", _
"montant_moy_EPCI", "montant_tot_EPCI", "mtt_moy_ctb_ap_proj", "mtt_tot_ctb_ap_proj", _
"mtt_moy_Fds_priv", "mtt_tot_Fds_priv", "mtt_moy_finan_other1", "mtt_tot_finan_other1", "mtt_moy_finan_other2", "mtt_tot_finan_other2", _
"mtt_moy_finan_other3", "mtt_tot_finan_other3", "id_val_materiel", "mtt_moy_val_materiel", "mtt_tot_val_materiel", _
"id_val_personnel", "mtt_moy_val_personne", "mtt_tot_val_personne", "comm_Moy_Financ", "desc_plaq_info", "obj_plaq_info", "nb_plaq_info", _
"public_plaq_info", "desc_affiches", "obj_affiches", "nb_affiches", "public_affiches", "desc_presse", "obj_presse", "nb_presse", "public_presse", _
"desc_pub_mun_dpt", "obj_pub_mun_dpt", "obj_pub_mun_dpt", "pub_pub_mun_dpt", "desc_interv_ext", "obj_interv_ext", "nb_interv_ext", _
"public_interv_ext", "desc_internet", "obj_internet", "nb_internet", "public_internet", "desc_forum", "obj_forum", "nb_forum", _
"public_forum", "nb_autre_RA", "nb_pro_dpt", "nb_pro_santé", "nb_communes", "nb_hopital", "nb_ssiad_sad", "nb_reseau_sante", _
"nb_struc_hbgt", "autre_orient_precis", "nb_autre_orient_prec", "nb_bao", "nb_deja_venuRA", _
"nb_brochure", "nb_club", "nb_conf", "nb_forum", "nb_web_presse", "autre_connu_prec", "nb_autre_connu_prec", "comm_stats_MAO", _
"date_logo_RA", "date_mep_visuel", "date_comm_numeriq", "kekemono_oui", "kekemono_non", "date_habilitations", "even2019_precision", _
"analyse_moy_comm", "analyse_moyens_dispo", "augmention_freq", "satisfa_usager", "nb_usager_PAP", "pourcent_pop_PAP", "H_nbeval_m60", _
"F_nbeval_m60", "H_PAP_m60", "F_PAP_m60", "H_nbeval_6069", "H_nbeval_6069", "H_PAP_6069", "F_PAP_6069", "H_nbeval_7079", _
"F_nbeval_7079", "H_PAP_7079", "F_PAP_7079", "H_nbeval_8090", "F_nbeval_8090", "H_PAP_8090", "F_PAP_8090", "H_nbeval_p90", _
"F_nbeval_p90", "H_PAP_p90", "F_PAP_p90", "PE_tot_cpl", "PE_tot_cpl_p_auto", "PE_tot_cpl_p_dep", "PE_tot_cpl_non_aide", _
"PE_tot_seul", "PE_tot_seul_p_auto", "PE_tot_seul_p_dep", "PE_tot_seul_non_aide", "PE_DNC", "PPAP_tot_cpl", _
"PPAP_tot_cpl_p_auto", "PPAP_tot_cpl_p_dep", "PPAP_tot_cpl_non_aid", "PPAP_tot_seul", "PPAP_tot_seul_p_auto", "PPAP_tot_seul_p_dep", _
"PPAP_tot_seul_non_ai", "PPAP_tot_seul_DNC", "PE_GIR1a4", "PE_GIR1", "PE_GIR2", "PE_GIR3", "PE_GIR4", "PE_GIR56", "PE_NE", "PPAP_GIR1a4", _
"PPAP_GIR1", "PPAP_GIR2", "PPAP_GIR3", "PPAP_GIR4", "PPAP_GIR56", "PPAP_NE", "PE_MDPH", "PPAP_MDPH", "PE_accomp", "PPAP_accomp")

variables3 = Array("PE_CARSAT", "PPAP_CARSAT", "PE_MSA", "PPAP_MSA", "PE_RSI", "PPAP_RSI", "PE_other", "PPAP_other", "PE_PC", "PE_FE", "PE_PoleA", _
"PE_RA", "PE_PL", "PE_PHosp", "PE_SS", "PE_PCSSM", "PE_SSIADSAD", "PE_MAIA", "PE_CCAS", "PPAP_PC", "PPAP_FE", "PPAP_PoleA", "PPAP_RA", _
"PPAP_PL", "PPAP_PHosp", "PPAP_SS", "PPAP_PCSSM", "PPAP_SSIADSAD", "PPAP_MAIA", "PPAP_CCAS", "NB_1interv_PAP", "NB_2interv_PAP", _
"NB_3interv_PAP", "NB_4interv_PAP", "PE_m1mois", "PPAP_m1mois", "PE_1a5mois", "PPAP_1a5mois", "PE_6a11mois", "PPAP_6a11mois", "PE_sup1an", _
"PPAP_sup1an", "Comm_tps_pec", "PAP_maintiendom", "PAP_accesdroits", "PAP_loisirsviesoc", "PAP_tspt", "PAP_hbgtdef", "PAP_lgt", _
"PAP_accessoins", "PAP_MSV", "PAP_aideaidants", "PAP_sante", "indic1", "PAP_ind1", "indic2", "PAP_ind2", "comm_repartpbpap", _
"evol_PAP_maintiendom", "evol_PAP_accesdroits", "evol_PAP_loisirsvies", "evol_PAP_tspt", "evol_PAP_hbgtdef", "evol_PAP_lgt", _
"evol_PAP_accessoins", "evol_PAP_MSV", "evol_PAP_aideaidants", "evol_PAP_sante", "evol_PAP_ind1", "evol_PAP_ind2", "sitcomplx_tot", _
"sitcomplx_CS", "sitcomplx_CU", "sitcomplx_CE", "MAIA_OI", "MAIA_AI", "PAP_traités", "PAP_DC", "PAP_refus", "PAP_hospi", "PAP_etab", _
"PAP_horscomp", "PAP_gMAIA", "PAP_autrepro", "PAP_demeng", "PAP_pasacc1an", "PAP_othercloture", "comm_PAPsit", "BQ_obsdispos", "BQ_VAD", _
"BQ_reusynt", "BQ_conctel", "BQ_Atoutsfreins", "BQAP_typaction1", "BQAP_descsynth1", "BQAP_cadrefinanc1", "BQAP_dateDU1", "BQAP_dateAU1", _
"BQAP_lieux1", "BQAP_nbparticip1", "BQAP_typaction2", "BQAP_descsynth2", "BQAP_cadrefinanc2", "BQAP_dateDU2", "BQAP_dateAU2", "BQAP_lieux2", _
"BQAP_nbparticip2", "BQAP_typaction3", "BQAP_descsynth3", "BQAP_cadrefinanc3", "BQAP_dateDU3", "BQAP_dateAU3", "BQAP_lieux3", "BQAP_nbparticip3", _
"BQAP_typaction4", "BQAP_descsynth4", "BQAP_cadrefinanc4", "BQAP_dateDU4", "BQAP_dateAU4", "BQAP_lieux4", "BQAP_nbparticip4", "BQAP_typaction5", _
"BQAP_descsynth5", "BQAP_cadrefinanc5", "BQAP_dateDU5", "BQAP_dateAU5", "BQAP_lieux5", "BQAP_nbparticip5", "BQAP_typaction6", "BQAP_descsynth6", _
"BQAP_cadrefinanc6", "BQAP_dateDU6", "BQAP_dateAU6", "BQAP_lieux6", "BQAP_nbparticip6", "BQAP_typaction7", "BQAP_descsynth7", "BQAP_cadrefinanc7", _
"BQAP_dateDU7", "BQAP_dateAU7", "BQAP_lieux7", "BQAP_nbparticip7", "autres_actionscoll", "TAP_SGBV_GIR1a4", "TAP_SGBV_GIR56", "TAP_SGBV_tot", _
"TAP_SGBV_AF", "TAP_SGBV_CG", "TAP_SGBV_dtmttgir14", "TAP_SGBV_nut_gir1a4", "TAP_SGBV_nut_gir56", "TAP_SGBV_nut_tot", "TAP_SGBV_nut_AF", _
"TAP_SGBV_nut_CG", "TAP_SGBV_mem_gir1a4", "TAP_SGBV_mem_gir56", "TAP_SGBV_mem_tot", "TAP_SGBV_mem_AF", "TAP_SGBV_mem_CG", "TAP_SGBV_som_gir1a4", _
"TAP_SGBV_som_gir56", "TAP_SGBV_som_tot", "TAP_SGBV_som_AF", "TAP_SGBV_som_CG", "TAP_SGBV_AP_gir1a4", "TAP_SGBV_AP_gir56", "TAP_SGBV_AP_tot", _
"TAP_SGBV_AP_AF", "TAP_SGBV_AP_CG", "TAP_SGBV_BES_gir1a4", "TAP_SGBV_BES_gir56", "TAP_SGBV_BES_tot")

variables4 = Array("TAP_SGBV_BES_AF", "TAP_SGBV_BES_CG", "TAP_SGBV_AA_gir1a4", "TAP_SGBV_AA_gir56", "TAP_SGBV_AA_tot", "TAP_SGBV_AA_AF", "TAP_SGBV_AA_CG", _
"TAP_SGBV_UN_gir1a4", "TAP_SGBV_UN_gir56", "TAP_SGBV_UN_tot", "TAP_SGBV_UN_AF", "TAP_SGBV_UN_CG", "TAP_SGBV_AACP_gir1a4", "TAP_SGBV_AACP_gir56", _
"TAP_SGBV_AACP_tot", "TAP_SGBV_AACP_AF", "TAP_SGBV_AACP_CG", "TAP_LS_gir1a4", "TAP_LS_gir56", "TAP_LS_tot", "TAP_LS_AF", "TAP_LS_CG", _
"TAP_LS_dtmttgir14", "TAP_HCV_gir1a4", "TAP_HCV_gir56", "TAP_HCV_tot", "TAP_HCV_AF", "TAP_HCV_CG", "TAP_HCV_dtmttgir14", "TAP_AAC_gir1a4", _
"TAP_AAC_gir56", "TAP_AAC_tot", "TAP_AAC_AF", "TAP_AAC_CG", "TAP_AAC_dtmttgir14", "TAP_AAC_SS_gir1a4", "TAP_AAC_SS_gir56", "TAP_AAC_SS_tot", _
"TAP_AAC_SS_AF", "TAP_AAC_SS_CG", "TAP_AAC_AD_gir1a4", "TAP_AAC_AD_gir56", "TAP_AAC_AD_tot", "TAP_AAC_AD_AF", "TAP_AAC_AD_CG", "TAP_AAC_PR_gir1a4", _
"TAP_AAC_PR_gir56", "TAP_AAC_PR_tot", "TAP_AAC_PR_AF", "TAP_AAC_PR_CG", "TAB_comm", "BQAP_analyseAR", "BQAP_analyseTAR", "BQAP_analysefr1levi", _
"BQAP_analyseparten", "CAAE_AG_CRA", "CAAE_AG_DI", "CAAE_AG_AC", "CAAE_AG_RQS", "Persp_AGCRA", "Persp_SDI", "Persp_AC", "Persp_ACCCF")

nb_Champs = 568
nb_champs1 = 164
nb_champs2 = 177
nb_champs3 = 172
nb_champs4 = 63
num_row = 1
i = 0

For i = 0 To nb_champs1 - 1
  Fich.Cells(num_row, i + 1) = Variables(i)
Next i
For i = 0 To nb_champs2 - 1
  Fich.Cells(num_row, i + 1 + nb_champs1) = variables2(i)
Next i
For i = 0 To nb_champs3 - 1
  Fich.Cells(num_row, i + 1 + nb_champs1 + nb_champs2) = variables3(i)
Next i
For i = 0 To nb_champs4 - 1
  Fich.Cells(num_row, i + 1 + nb_champs1 + nb_champs2 + nb_champs3) = variables4(i)
Next i

Set FichierWord = CreateObject("word.application")
FichierWord.Visible = True
FichierWord.DisplayAlerts = False

Do While mesfichiers <> ""
  If mesfichiers <> "." And mesfichiers <> ".." And mesfichiers <> "rapport d'activité 2019 BASE XLS.docm" Then
    monDocument = chemin & mesfichiers
    FichierWord.documents.Open Filename:=monDocument, ReadOnly:=True
    num_row = num_row + 1
    num_col = 1
    'For i = 0 To nb_Champs - 1
      'Fich.Cells(num_row, i + 1) = FichierWord.activedocument.formfields(Variables(i)).result
    'Next i
    For i = 0 To nb_champs1 - 1
        Fich.Cells(num_row, i + 1) = FichierWord.activedocument.formfields(Variables(i)).result
    Next i
    For i = 0 To nb_champs2 - 1
        Fich.Cells(num_row, i + 1 + nb_champs1) = FichierWord.activedocument.formfields(variables2(i)).result
    Next i
    For i = 0 To nb_champs3 - 1
        Fich.Cells(num_row, i + 1 + nb_champs1 + nb_champs2) = FichierWord.activedocument.formfields(variables3(i)).result
    Next i
    For i = 0 To nb_champs4 - 1
        Fich.Cells(num_row, i + 1 + nb_champs1 + nb_champs2 + nb_champs3) = FichierWord.activedocument.formfields(variables4(i)).result
    Next i
    FichierWord.documents.Close (0)
  End If
  mesfichiers = Dir
Loop
FichierWord.Quit

End Sub

BONNES VACANCES !!! et merci encore !!!!

Effectuez une recherche sur Excel Downloads...

XL 2016 [RESOLU] Macro avec 568 variables...

jabenj

XLDnaute Junior

Pièces jointes

sylvanu

XLDnaute Barbatruc

Fusion de deux tableau VBA - Macros et VBA Excel

Pièces jointes

jabenj

XLDnaute Junior

Discussions similaires

Nous accordons de l'importance à votre vie privée