Requête VBA

Question

Bonjour à tous,

Je suis en train d'essayer d'intégrer une requête SQL dans VBA Excel afin d'afficher ma table dans une feuille avant d'en extraire les données.

Mon problème est que depuis 1 mois que je me penche sur le sujet, je faisais des essais en consultant une base de données, ça fonctionnait et je me concentrais alors sur le reste du code VBA.

L'objectif final du fichier est de pouvoir faire la même chose pour 5 bases de données distinctes mais lorsque je transpose le code pour une autre BDD ça ne fonctionne pas, erreur 1001 sur ".Refresh BackgroundQuery:=False"

Afin de fabriquer mon code j'étais passé par l'enregistreur de macro comme me l'a suggéré mon frère avant de faire des retouches. (Je débute en VBA dsl)

Je pense que mon problème vient de ma requête sql mais je ne comprends pas que ça marche pour une base et pas pour les autres.

Ma requête pour la premiere base est la suivante :

Dim strSQLa1 As String

strSQLa1 = ""
strSQLa1 = strSQLa1 & ""...

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=""DSN=pr****es;Description=PR****ES;UID=Administrateur;APP=Micr" _
        , "osoft Office 2003;WSID=;DATABASE=PR****ES"""), Destination:= _
        Range("$C$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = strSQLa1
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = _
        "Tableau_SQL_Queries___PR****ES__192.168.*.*____9.16.2.x1"
        .Refresh BackgroundQuery:=False
    End With


Cette version fonctionne étrangement, si je change les infos de connexion sur une autre base (en remplaçant DSN= notamment) ça me met donc l'erreur sur la ligne .Refresh BackgroundQuery:=False avant d'importer les données.

J'ai voulu me documenter, fabriquer ma propre ligne de commande pour la rendre plus propre et fonctionnelle mais ça ne marche pas, je sais même pas si l'idée était bonne du coup :


Dim strSQLc1 As String

strSQLc1 = ""
strSQLc1 = strSQLc1 & ""...

    With ActiveSheet.QueryTable.Add(Connection:=Array(Array( _
        "ODBC;DSN=pro*******ne;UID=administrateur;PWD=), Destination:= _
        Range("$C$1"))
        .CommandText = strSQLc1
        .Name = "ESSAI"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With


Dans ce code j'ai une erreur sur Range("$C$1") : Erreur de compilation: Attendu : séparateur de liste ou ).
Si je décale un peu les lignes l'erreur passe sur les $ $ comme des caractères non compatibles.

J'espère que vous pourrez m'aider à résoudre ce problème de requête sql dans vba, après ça je commencerai à être un peu plus opérationnel et je pourrai de nouveau dormir la nuit sans m'arracher les cheveux^^

Par avance merci.

Calade · Accepted Answer

Comme je te l'ai dit, je ne connais pas ce genre de choses, mais les points suivants sont à vérifier au niveau de ta 2ème base, en partant que ta 1ère base fonctionne sans problème:

- DSN (Data Source Name) et tu as des explications ici, en principe si la source (ton SGBD) est le même, tu dois avoir la même chose.

- WSIS (WorksStation IDentification) comme son nom l'indique l'identité de la station de travail (la même ?).

- Description = Je ne suis pas sur de ce que cela peut-être donc à contrôler de près.

- DataBase = Je pense le nom de la base de données source.

- .CommandText = la requête qui va extraire des données de ta source, ici ce n'est pas la même ce qui peut être tout à fait normal, mais vérifier que tu prends la bonne.

- DisplayName = à mon avis ce n'est qu'un nom d'affichage, donc pas primordial mais à vérifier dans ton aide quand même.

Tu parles des addresses IP, elles sont à changer si tout ne réside pas sur le même micro (serveur, station(s), etc...), sinon tu laisses la même.

Par contre, j'espère que tu sais quoi mettre à la place des étoiles car je ne peux rien faire sur ce point précis.


Calade

Calade · Answer

Bonjour,

Je n'ai rien testé mais entre les 2 versions tu n'utilises pas la même méthode (OLEDB dans le 1er cas et ODBC dans le 2ème).

Essaie avec exactement la même chose (sauf le nom de fichier évidemment).


Calade

ffs69 · Answer

Bonjour Calade et d'ors et déjà merci d'avoir accordé de l'attention à ma requête 

Je comprends pas trop la différence entre OLEDB et ODBC techniquement.

Juste dans ce que je teste depuis 1mois j'utilisais la première version (OLEDB) pour une même base de données et ça fonctionnait, lorsqu'hier j'ai voulu l'adapter pour les autres bases ça ne fonctionnait pas avec une erreur sur la dernière ligne [.Refresh BackgroundQuery:=False] qui bloque le téléchargement des infos je pense.

La version ODBC c'est quelque chose que j'ai essayé de créer ce matin en pensant que je m'en sortirai mieux, mais dans ce cas j'ai une erreur sur le [Range("$C$1")] et là ça ne marche avec aucune base pour le moment.

C'est pas évident parce que j'ai jamais appris VBA avant et que je m'en sors difficilement avec des bouts de documents que j'essaie de concaténer. Heureusement vous êtes là et mon jeune frère aussi pour me montrer la lumière quand je suis dans l'obscurité.

Vous êtes des  ^^

Calade · Answer

Bonjour,

Envoie la version OLEDB pour la 2ème base pour que l'on puisse comparer.

Je n'ai jamais utiliser ce genre de choses mais ça ne coûte rien d'essayer.


Calade

ffs69 · Answer

Coucou et merci encore,

Alors la requête pour les bases suivantes, j'ai juste adapté les noms, je suis pas sûr ça se fasse comme ça, pit-être il faudrait changer par les adresses ip à un moment mais je sais pas où et comment.

Pour la base suivante j'ai donc écrit comme ça :


Dim strSQLp1 As String

strSQLp1 = "
strSQLp1 = strSQLp1 & ""...

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=""DSN=pr****es;Description=PR****ES;UID=Administrateur;APP=Micr" _
        , "osoft Office 2003;WSID=GFPCORDIRPO01;DATABASE=PR****ES"""), Destination:= _
        Range("$C$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = strSQLp1
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = _
        "Tableau_SQL_Queries___PR****ES__192.168.*.*____9.16.2.x1"
        .Refresh BackgroundQuery:=False
    End With


A noter que les *** correspondent à d'autres lettres que l'on ne distingue pas bien dans cet exemple. J'ai change le string et les noms de bdd. Après comme le contenu de la requête est la même je pourrai sûrement reprendre le même string pour alléger le code.

J'ai fait des essais avec 


Dim oDataObject As DataObject
 
Set oDataObject = New DataObject
oDataObject.SetText ""
oDataObject.PutInClipboard
 
Set oDataObject = Nothing


et


Application.CutCopyMode = False


Me disant que ça pouvait être dans le presse papiers mais ça ne change rien.

Je pars à Grenoble rendre un service à une amie de ma maman, je n'aurais pas mon pc, je rentre lundi pour reprendre le sujet et les essais.

Par avance merci.

ffs69 · Answer

Coucou :)

De retour de Grenoble et après une nuit blanche à pencher sur le sujet, j'ai peut-être avancé mais je bloque sur une nouvelle piste.

Etant donné que ça fonctionne avec OLEDB sur une base autant continuer de là en effet.

J'ai refait un peu de ménage dans le code en même temps, j'en suis là :


Dim strSQLr1 As String
Dim RConnect As String

strSQLr1 = ""
strSQLr1 = strSQLr1 & ""...

RConnect = "OLEDB;Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=""DSN=pr***one;Description=PR***ONE;UID=Administrateur;DATABASE=PR***ONE"""

    Range("C1").CurrentRegion.ClearContents
    
    With ActiveSheet.QueryTables.Add(Connection:=RConnect, Destination:=ActiveSheet.Range("C1"))
               .CommandText = strSQLr1
               .Name = "Tableau_SQL_Queries_PR***ONE_192.168.*.*_9.16.2.x1"
               .Refresh BackgroundQuery:=False
    End With


Ce que je demandais pas rapport à l'adresse ip, le fichier excel pourra servir pour collecter des données à partir de 5 bases de données distinctes, en fonction du choix utilisateur, sur des serveurs différents.

Dans la gestion ODBC de windows, j'ai les 5 serveurs référencés qui apparaissent avec les noms comme dans la requête qui fonctionne pour une des bases. Dans le détail ODBC y'a les liens adresses ip du coup je savais pas pour le champ DSN= si ça pouvait être mieux de mettre l'ip plutôt que le nom.

L'erreur ici est la même qu'au début de mes recherches, à la ligne :


.Refresh BackgroundQuery:=False


Erreur d'exécution '1004':
La requête ne s'est pas exécutée ou la table de la base de donnée n'a pas pu être ouverte.

J'ai trouvé sur Internet quelqu'un qui semble avoir rencontré le même problème mais je comprends pas ce qu'il veut dire dans sa solution :

 - I progress ... It's a date record which cause the problem. But I don't see why, query work in SQL*Plus..... :-(

 - GOOODDDD !! I've solve it by convert it in characters.... Sorry....

La version de départ qui fonctionne mais que sur une base c'est :


Dim strSQLa1 As String

strSQLa1 = ""
strSQLa1 = strSQLa1 & ""...

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=""DSN=pr****es;Description=PR****ES;UID=Administrateur;APP=Micr" _
        , "osoft Office 2003;WSID=;DATABASE=PR****ES"""), Destination:= _
        Range("$C$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = strSQLa1
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = _
        "Tableau_SQL_Queries___PR****ES__192.168.*.*____9.16.2.x1"
        .Refresh BackgroundQuery:=False
    End With

Calade · Answer

Bonjour,

Mets-moi le lien de cette solution que j'aille y jeter un oeil.


Calade

ffs69 · Answer

dsl réponse un peu tardive, j'arrête pas d'essayer des trucs mais rien ne marche,

la solution y'a pas grand chose de plus malheureusement : http://social.msdn.microsoft.com/Forums/en/olbasics/thread/585df7e6-f9c2-49ea-9881-676e1a5092b4

si t'arrives à comprendre de quoi il parle quand il dit "I've solve it by convert it in characters" ça peut être énorme.

merci encore

Calade · Answer

OK, j'ai peut-être une piste.
Si j'ai bien compris, dans sa requête il avait un champ Date récupéré comme tel.

Regarde dans ta requête et convertit TOUS tes champs qui ne sont pas du string en caractères (les dates et les numériques) au besoin en les castant (en SQL CAST et CONVERT).

Au besoin poste ta requête qui pose problème.


Calade

ffs69 · Answer

ah enfin merci trop cool

bon pour moi c'était pas un problème de date mais c'était bien dans les requêtes sql que se trouvait le problème, je pensais quand changeant les adresses des bases avec les mêmes requêtes ça fonctionnerait mais en effet dans les requêtes y'a des champs personnalisés propre à chaque base il a suffit de changer et ça marche nickel avec cette version donc si ça peut servir à quelqu'un d'autre :


Dim strSQLa1 As String

strSQLa1 = ""
strSQLa1 = strSQLa1 & ""...

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=""DSN=pr****es;Description=PR****ES;UID=Administrateur;APP=Micr" _
        , "osoft Office 2003;WSID=GFPCORDIRPO01;DATABASE=PR****ES"""), Destination:= _
        Range("$C$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = strSQLa1
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = _
        "Tableau_SQL_Queries___PR****ES__192.168.*.*____9.16.2.x1"
        .Refresh BackgroundQuery:=False
    End With


Merci beaucoup beaucoup Calade pour ton aide régulière

Calade · Answer

Content d'avoir pu t'aider.
Penses à cocher "Réponse acceptée" si ça marche.


Calade

Requête VBA <- SQL, problème probable dans la syntaxe, besoin d'aide SVP.

11 réponses

Votre réponse

Discussions similaires