Macro RechercheV dans fichier fermé

[neilu]

Bonjour le forum,

Je bloque sur les lignes de commandes permettant d'effectuer une rechercheV dans un classeur fermé en spécifiant son emplacement. Entre les Execute.Excel4macro et ADO, je me pers un peu.

J'ai un fichier ouvert avec des informations. Une macro trie les infos, une autre les envoie par email. le soucis est que le fichier ouvert contient les noms, et les adresses mails sont dans un autre classeur fermé (pour faire simple : C:\Users\jug\Desktop)

Comment faire pour que la variable destinataire soit égale à la valeur de la colonne B, ligne du nom recherché (en gros une sorte de rechercheV).

Voici 2 fichiers exemples.

En vous remerciant.

 

[MichD]

Re : Macro RechercheV dans fichier fermé

Bonjour,

Regarde dans ton fichier ouvert, tu as un exemple créé avec ADO.
La référence "Microsoft ActiveX Data Objects 2.8 Library" doit être
cochée dans la fenêtre de l'éditeur de code / barre des menus / outils /
références...

À la fin de la fonction dans le fichier fermé, tu ajouteras ces 2 lignes de code
que j'ai omises pour libérer la mémoire des objets créés.

Rst.Close: Conn.Close
Set Rst = Nothing: Set Conn = Nothing

Function GetValueWithADO(Classeur As String, _
Feuille As String, CellAdresse As String, Critère As String)
'Microsoft ActiveX Data Objects 2.8 Library
Dim Rst As New ADODB.Recordset
Dim Conn As New Connection
Dim Requete As String

Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & Classeur & ";" & _
    "Extended Properties=""Excel 12.0;HDR=NO;"""

Requete = "SELECT F2 FROM [" & Feuille & "$" & CellAdresse & "]" & _
        "Where F1 like '" & Critère & "'"
Rst.Open Requete, Conn, adOpenForwardOnly, adLockReadOnly, adCmdText
GetValueWithADO = Application.Clean(Rst.GetString(NumRows:=1))
Rst.Close: Conn.Close
Set Rst = Nothing: Set Conn = Nothing
 End Function
'-------------------------------
Sub mail()
Dim OutApp As Object, OutMail As Object
Dim Destinataire As String
Dim Fichier As String, Feuille As String
Dim CellAdr As String, Critère As String

'-------Variables à renseigner---------
Fichier = ThisWorkbook.Path & "\fichier fermé.xlsx"
Feuille = "Sheet1"
CellAdr = Range("A:B").Address(0, 0)
'L'Adresse de la cellule dont on veut extraire l'adresse courriel.
Critère = Worksheets("Sheet1").Range("A4")
'---------------------------------
 
Destinataire = GetValueWithADO(Fichier, _
            Feuille, CellAdr, Critère)

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
    .To = Destinataire
    .CC = ""
    .BCC = ""
    .Subject = "bla bla bla"
    .Body = "bla bla bla"
    .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[MichD]

Re : Macro RechercheV dans fichier fermé

Si tu recherches une formule avec la fonction "RechercheV", dans le "fichier ouvert.xlsm",
tu peux utiliser la formule suivante en A1 et la recopier sur la colonne :

Tu adaptes le nom du chemin où se retrouve le fichier
=RECHERCHEV(A1;'E:\Excel\[fichier fermé.xlsx]Sheet1'!$A$1:$B$4;2;FAUX)

 

[neilu]

Re : Macro RechercheV dans fichier fermé

Bonsoir MichD,

Merci pour ces 2 alternatives, je vais essayer les 2 dès demain. Je ne savais pas que la formule RecherheV fonctionnait si la cible était un fichier fermé.

Mais je pense que j'opterai pour la Macro. Je te tiens au courant.

 

[neilu]

Re : Macro RechercheV dans fichier fermé

Bonjour,

Cela marche très bien mais lorsque le fichier fermé et ma liste réelle de contact, l'erreur suivante s'affiche :
Error 3021
Ethier BOF or EOF is true, or the current record has been deleted.
Requested operation requires a current record.

Pour info, mon fichier contact fait plus de 600 lignes. Si je n'en met qu'une dizaine, ça fonctionne.

Une idée ?

 

[MichD]

Re : Macro RechercheV dans fichier fermé

Cette erreur est causée lorsque la recherche de l'usager n'existe pas
dans la table du classeur fermé. Le code suivant tient compte de cette
éventualité.
(Attention, il arrive que l'information de la cellule contienne seulement
un espace inopportun et la requête ne trouve pas le nom indiqué.

Function GetValueWithADO(Classeur As String, _
Feuille As String, CellAdresse As String, Critère As String)
'Microsoft ActiveX Data Objects 2.8 Library
Dim Rst As New ADODB.Recordset
Dim Conn As New Connection
Dim Requete As String

Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & Classeur & ";" & _
    "Extended Properties=""Excel 12.0;HDR=NO;"""

Requete = "SELECT F2 FROM [" & Feuille & "$" & CellAdresse & "]" & _
        "Where F1 like '" & Critère & "'"
Rst.Open Requete, Conn, adOpenStatic, adLockReadOnly, adCmdText
If Rst.RecordCount > 0 Then
    GetValueWithADO = Application.Clean(Rst.GetString(NumRows:=1))
Else
    GetValueWithADO = ""
End If
Rst.Close: Conn.Close
Set Rst = Nothing: Set Conn = Nothing
 End Function

=============================

Sub mail()
Dim OutApp As Object, OutMail As Object
Dim Destinataire As String
Dim Fichier As String, Feuille As String
Dim CellAdr As String, Critère As String

'-------Variables à renseigner---------
Fichier = ThisWorkbook.Path & "\fichier fermé.xlsx"
Feuille = "Sheet1"
CellAdr = Range("A:B").Address(0, 0)
Critère = Worksheets("Sheet1").Range("A4")
'---------------------------------------
 
Destinataire = GetValueWithADO(Fichier, _
            Feuille, CellAdr, Critère)

If Destinataire = "" Then
    MsgBox "L'usager """ & Critère & """ " & _
    "n 'existe pas dans la table.", _
        vbCritical + vbOKOnly, "Attention"
    Exit Sub
End If
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
    .To = Destinataire
    .CC = ""
    .BCC = ""
    .Subject = "bla bla bla"
    .Body = "bla bla bla"
    .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[neilu]

Re : Macro RechercheV dans fichier fermé

Hello,

Je n'y aurais pas pensé. C'est tout bon à présent. Merci 1000 fois pour ton implication !

 

[neilu]

Re : Macro RechercheV dans fichier fermé

Bonjour à tous,

Je reviens sur ce post à cause du problème suivant.

Tout fonctionnait très bien : une macro génère des emails et va faire une RechercheV dans un fichier Excel fermé pour récupéré l'adresse Email. ce fichier fermé est sur le réseau d'entreprise. La macro est aussi sur le réseau et en partagée (Excel add-ins).

Mais à l'éxécution, le 1er mail est généré avec l'adresse correspondante puis la fenêtre d'erreur s'affiche :


Le lien du fichier est correct. Il n'est pas ouvert, il est en partagé et j'ai les pleins droits. Les autres utilisateurs ne rencontrent pas ce soucis. Si je place le fichier en local sur mon PC, je n'ai plus de problème.

En cliquant sur debug, les lignes suivantes de la fonction sont surlignées en jaune. la fonction complète est celle donnée par MichD précedemment :

Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & Classeur & ";" & _
    "Extended Properties=""Excel 12.0;HDR=NO;"""

Est-ce un problème d'accés réseau ?

Merci pour votre aide.

 

[MichD]

Re : Macro RechercheV dans fichier fermé

A ) Je ne suis pas en réseau, il m'est difficile de tester quoi que ce soit!

il est en partagé et j'ai les pleins droits. Les autres utilisateurs ne rencontrent pas ce soucis. Si je place le fichier en local sur mon PC, je n'ai plus de problème

1 - Le fichier est partagé, c'est-à-dire que cela est fait par la commande de "Partage" dans le menu (ruban) d'Excel.
Est-ce bien cela dont tu fais référence?

2 - "Les autres utilisateurs ne rencontrent pas ce soucis" Ledit fichier est opérationnel pour tous les autres usagers
Il n'y a que toi sur ton ordinateur qui n'arrive pas à exécuter la procédure. Est-ce cela?

 

[neilu]

Re : Macro RechercheV dans fichier fermé

Bonsoir MichD,

Le fichier fermé est paramétré en partagé via les options du ruban en effet. J'ai pas pensé à essayer via les propriétés du fichier. J'avais fait ça pensant que ça aller résoudre le problème mais non. Avant il n'y avait aucun partage particulier.

Tout à fait, je suis le seul à ne pas pouvoir accéder au fichier via la macro. Je suis portant l'auteur de ce fichier. C'est un comble

J'ai essayer de le renommer et de le placer dans un autre répertoire, même erreur. Par contre en le mettant sur le bureau pas de soucis. Et manuellement je peux l'ouvrir sans problème sur le réseau.

Merci d'avance.

 

[MichD]

Re : Macro RechercheV dans fichier fermé

Regarde ce fil : (en anglais)

How do I get past this error: "It is already opened exclusively by another user, or you need permission..."? - TntMPD

 

[neilu]

Re : Macro RechercheV dans fichier fermé

Sans le savoir j'avais commencé à le faire. J'ai seulement fait un copier coller du fichier puis j'ai modifié la macro pour qu'elle pointe sur le fichier copié mais ça ne marchait pas.

Demain je ferai exactement comme décris.

Merci, je te tiens au courant.

 

[neilu]

Re : Macro RechercheV dans fichier fermé

Bonjour,

Avant de faire ce qui est mentionnée dans le fil mentionné plus haut, j'ai refait un test sans rien changer. Et ce matin, tout fonctionne. je ne comprends pas pourquoi. Bref, si le problème venait à se présenter de nouveau, je tenterai de faire le copier coller...

merci