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

jabenj

XLDnaute Junior
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 ;) !
 

Pièces jointes

  • champs - vba.txt
    9.3 KB · Affichages: 10

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour JabenJ,
Requête bizarre. Un formulaire de 568 champs?
Au moins pour le point 3 avec cette aide :
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 )
 

Pièces jointes

  • BigArray.xlsm
    29.8 KB · Affichages: 7

jabenj

XLDnaute Junior
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 !!!!
 

Statistiques des forums

Discussions
311 720
Messages
2 081 886
Membres
101 830
dernier inscrit
sonia poulaert