Nouveau xls / SQL

Version imprimable

Voir 40 message(s) de cette discussion en une page

Nouveau xls / SQL

Bonjour,
(Je suis encore debutant sur VBA Excell, je m'en sors pas mal sur VB-Access)
Je voudrais vous demander une aide svp par rapport à ce code :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Private Sub valider_Click() Dim k As Integer With Sheets("Feuil2") Columns("A:F").ClearContents End With Set rst = db.OpenRecordset("SELECT [table].[id], [table].[Name], [table].[PrName], [table].[Start Date], [table].[End Date] FROM [table];") k = 0 Do k = k + 1 If k > 100 Then Exit Do With Sheets("Feuil2") .Cells(k, 1) = rst.Fields("id").Value .Cells(k, 2) = rst.Fields("Name").Value .Cells(k, 3) = rst.Fields("PrName").Value .Cells(k, 4) = rst.Fields("Start Date").Value .Cells(k, 5) = rst.Fields("End Date").Value End With rst.MoveNext Loop Until rst.EOF rst.Close End Sub
Actuellement, tt est OK, ce code implement la feuil2 du meme fichier par la requete SQL, en connexion avec une BD (code sur module, aucun soucis de ce coté la aussi).
La requete est apte à recevoir une clause Where.
Mais je sens que ce code n'est pas ... assez flexible; du coup, je voudrais savoir si possible comment :

Créer un nouveau fichier Excel(une seule feuille) en mode lecture seul (modification impossible, enregistrement possible) implementé par la requete (et qu'il l'ouvre entre temps).
Sur le nouveau fichier Excel, mettre en premiere colonne les noms des colonnes de la requete SQL (coloré et figé).

Voila ! Merci d'avance!

Bonjour,

Un exemple de code pour insérer des données dans un nouveau classeur et l'enregistrer en proposant l'ouverture en lecteur seule

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Dim Cnn As New ADODB.Connection
Dim Rec As New ADODB.Recordset
 
Dim i As Integer
 
Cnn.Open "DSN=Nom;UID=User;PWD=Password"
 
Rec.Open "select * from MaTable", Cnn, adOpenKeyset, adLockReadOnly
 
Dim wrk As Workbook
Set wrk = Application.Workbooks.Add
 
'insert les en-tetes
For i = 1 To Rec.Fields.Count
    wrk.Sheets(1).Cells(1, i).Value = Rec.Fields(i - 1).Name
Next i
 
'Ajout des données à partir de A2
wrk.Sheets(1).Range("A2").CopyFromRecordset Rec
 
'Enregistrement en lecture seule
wrk.SaveAs "c:\Test.xlsx", XlFileFormat.xlWorkbookDefault, , , True
 
 
Rec.Close
Set Rec = Nothing
Cnn.Close
Set Cnn = Nothing

12/03/2013, 14h19
aminnio

Bonjour,
Tt dabors, je vous remercie pour votre reponse.
Ce que vous me proposez ici, c'est d'utiliser une connexion ADO à la BD. Or, je n'ai pour l'instant pas de connaissance sur cette technologie.
Je ne sais pas où specifier le chemin de la BD, ni comment manipuler les données. Je testerais ceci et verrais si je m'en sors avec.

Cordialement.

1 pièce(s) jointe(s)

Ta plus besoin de te prendre la tête avec ADODB

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Set con = New ADODBRD   'Class ADODB
'1  - ODBC
'2  - ORACLE
'3  - ACCESS 2010
'4  - ACCESS 2000
'5  - ACCESS 97
'6  - SQL SERVER
'7  - SQL SERVER 2005 Express
'9  - SQLite
'10 - SQLite3
con.TYPEBASE = 4 '4 - ACCESS 2000
con.Fichier = "C:\MonRep\MaBase.mdb"
 
 
 
 SQL = "SELECT * FROM table;"
 Set RS = con.OpenRecordSet(SQL)

12/03/2013, 14h56
jfontaine

Peu importe la méthode de recherche des données, ce qui est interressant pour toi, c'est comment on créait un nouveau classeur pour les mettre dedans.
La méthode d'insertion ici n'utilise pas de boucle mais CopyFormRecordset qui insère les données d'un seul bloc (Beaucoup plus rapide)
12/03/2013, 15h14
Invité

Je suis d’accords, sauf qu’il souhaite récupérer que 100 lignes, appart rajouter select top 100 ???

Citation:

Code:

If k > 100 Then Exit Do
12/03/2013, 15h44
aminnio
Je sais qu'on s'eloigne un peu de la rubrique Excel, mais je voudrais jsute en savoir plus sur la technologie ADO, car je sais qu'elle s'avere beaucoup plus efficace que DAO.

Dans un premier temps, la connexion :
Code:

1 2 3 4 5 6 7 8 9 10 '1 - ODBC '2 - ORACLE '3 - ACCESS 2010 '4 - ACCESS 2000 '5 - ACCESS 97 '6 - SQL SERVER '7 - SQL SERVER 2005 Express '9 - SQLite '10 - SQLite3
Pour Access 2003 on choisi laquelle? 2000 meme?

Dans un second temps, la librairie? J'en ai choisi pas mal, mais "apparement" VBA ne reconnait pas encore quand je declare ADO.recordset (quand je mets . apres ADO, il n'affiche pas la liste des choix...). [Resolu] il fallait juste que je modifie la priorité ...

Dans un troisieme temps ! pour repondre à rdurupt.
>rdurupt : je ne souhaite pas recuperer que 100 ligne, je souhaite recuperer le resultat de la requete tt court. Juste pour faire des testes, j'ai recuperé que 100 lignes histoire de ...
>rdurupt : aussi, le fichier .cls correspond à quoi ... ?

Donc je reviens à ma question de depart :
Je voudrais créer un nouveau fichier, nomé, avec une seule feuille ou y'aurait le resultat de la requete, chaque entete de la colonne aura le nom de la colonne de la requete, et que ce fichier soit en mode LECTURE SEULE, modification impossible et enregistrement possible ! (pour l'instant, je suis encore sur du DAO )

Comme j'ai dis, j'ai un peu bidouillé ceci avec quelques code sur internet, mais c'est "bidouillé", j'ai pas vraiment saisi les fonctionnalité exacte de chaque fragment de code (pas envie de faire du copier coller à l'aveuglette).

Donc si vous avez des propositions !
Voila ! Merci :) et desolé si c'est trop demandé.
12/03/2013, 15h59
Invité

1 pièce(s) jointe(s)

avec la solution de jfontaine
'4 - ACCESS 2000
Code:

1 2 3 4 5 'Ajout des données à partir de A2 wrk.Sheets(1).Range("A2").CopyFromRecordset Rec 'Enregistrement en lecture seule wrk.SaveAs "c:\Test.xlsx", XlFileFormat.xlWorkbookDefault, , , True
le ficher cls c’est une class on l’utilise comme une variable sauf qu’il l’instruction New elle possède des propriétés (Variables) et des méthode (Fonctions, Procédures) c’est une variable intelligente !

Il faut faire un clic droit sur ton projet vb et importer un fichier puis sélectionner le fichier cls

>rdurupt

Je sais qu'on s'eloigne de plus en plus du sujet originale.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 Public cnx As ADODB.Connection Public Sub connexion() 'Déclaration de la variable de connexion Set cnx = New ADODB.Connection 'Définition du pilote de connexion cnx.Provider = "Microsoft.Jet.Oledb.3.51" 'Définition de la chaîne de connexion cnx.ConnectionString = "chemin\bd.mdb" 'Ouverture de la base de données cnx.Open End Sub
J'utilise ceci dans le module du projet.
J'ai une erreur au niveau de cnx.open, qui m'affiche : 3706, impossible de trouver fournisseur.
Je suis sur que c'est en rapport avec le cnx.provider.
En sachant que la librairie ActiveX est bien coché .

12/03/2013, 16h35
Invité

Code:

GenereCSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Fichier & ";Persist Security Info=False"

"Microsoft.Jet.Oledb.3.51" c’est Access97

Test la manip que j’ai dit plus haut !

Et avec ce code la :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Public Sub conx() Set con = New ADODBRD 'Class ADODB '1 - ODBC '2 - ORACLE '3 - ACCESS 2010 '4 - ACCESS 2000 '5 - ACCESS 97 '6 - SQL SERVER '7 - SQL SERVER 2005 Express '9 - SQLite '10 - SQLite3 con.TYPEBASE = 4 '4 - ACCESS 2000 con.Fichier = "C:\Users\chlyeh\Desktop\HENKEL\Export\Final\bdxls.mdb" End Sub
j'ai une erreur : 3709, impossible d'utiliser cette connexion pour effectuer cette operation. Elle est fermé ou non valide dans ce contexte.

En fait, l'idée est comme suit :
Un module ou il y aurait une procedure qui etablirait le connexion avec la BD une bonne fois pour toute.
Dans l'userform, il suffit d'appeller la procedure d'etablissement de la connexion une fois, et de la on fait appelle aux requetes qu'on souhaite.

La je tombe sur le probleme de connexion ADO, j'imagine meme pas ce que ça donnerait quand je voudrais faire des fichiers excel sous requete sql ..

Merci infiniment pour ce temps que vous m'attribuez en tt cas !

12/03/2013, 16h59
Invité

Tu déclares ta variable dans une sub et tu l’utilise dans une autre ?
Les variables dans un sub non de vie que le temps de déroulement du code !
Si tu veux que ta variable persiste il faut la déclaré comme public dans les premières lignes d’un module

Code:

dim Con as ADODBRD

Bon, j'explique où j'en suis, comme ça, chaque post sera utile.

Donc dans un VBAProject, j'ai un module ou il y'a :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Option Explicit Public rst As New ADODB.Recordset Public rst1 As New ADODB.Recordset Public strSQL As String Public cnx As ADODB.Connection Public con As ADODB.Connection 'Le code que tu m'a conseillé d'oublier 'Public Sub Connexion() ''Déclaration de la variable de connexion 'Set cnx = New ADODB.Connection ''Définition du pilote de connexion 'cnx.Provider = "Microsoft.Jet.Oledb.3.51" ''Définition de la chaîne de connexion 'cnx.ConnectionString = "chemin\bd.mdb" ''Ouverture de la base de données 'cnx.Open 'End Sub Public Sub conx() Set con = New ADODBRD 'Class ADODB con.TYPEBASE = 4 '4 - ACCESS 2000 con.Fichier = "chemin\bd.mdb" End Sub
Avec comme importé le fichier que tu m'avais donnée.

J'ai une feuille ou il y'a un userform, dont le code est comme suit :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 Private Sub UserForm_Initialize() conx rst.Open "SELECT table.[Name] FROM table;" Do comboBox.AddItem rst.Fields("Name").Value rst.MoveNext Loop Until rst.EOF rst.Close rst.Close Set rst = Nothing End Sub
Au lancement de l'userform, connexion à la BD, execution de la requete, remplissage du comboBox avec les resultats de la requete.

Avec la technologie DAO, tout fonctionnait à merveille.
Sur ADO, j'ai suivi tes consignes, mais j'ai une erreur :
-------------------------------------------------------------------------
Erreur d'execution '3709'
Impossible d'utiliser cette connexion pour effectuer cette operation. Elle est fermée ou non valide dans ce contexte.
-------------------------------------------------------------------------

13/03/2013, 10h43
Invité

1 pièce(s) jointe(s)

Bonjour,
Test le fichier Excel que je joins

Bonjour,
Merci pour tout cet effort que vous fournissez pour moi, c'est vraiment tres sympa de votre part.

Votre code me parait vraiment complet, je le prendrais comme exemple.
Neanmoins, quand j'execute le code, j'ai l'erreur suivante :
Erreur Compilation, membre ou methode de données introuvable.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Sub Test() Dim con As New ADODBRD Dim SQL As String Dim I As Integer Dim Rec Dim Rep As String Rep = ActiveWorkbook.Path con.TYPEBASE = 4 con.Fichier = Rep & "\Test-ADODBRD.mdb" SQL = "SELECT NomPrenomAge.* FROM NomPrenomAge;" Set Rec = con.OpenRecordSet(SQL) Dim wrk As Workbook Set wrk = Application.Workbooks.Add 'insert les en-tetes For I = 1 To Rec.Fields.Count wrk.Sheets(1).Cells(1, I).Value = Rec.Fields(I - 1).Name Next I 'Ajout des données à partir de A2 wrk.Sheets(1).Range("A2").CopyFromRecordset Rec 'Enregistrement en lecture seule wrk.SaveAs Rep & "\Test.xls", XlFileFormat.xlWorkbookDefault, , , True Set Rec = con.CloseRecordSet(RS) con.CloseConnection End Sub
Je n'ai touché à rien, j'ai juste executé le code.
Merci !

13/03/2013, 11h42
Invité

Tu as donc vue un message avec arrêter débugué ?
Click sur débugé et dis moi ou la ligne est surligné en jaune, car chez mois sa fonctionne parfaitement sinon je ne l’aurai pas posté.
13/03/2013, 11h56
aminnio

Bonjour,

Quand je clique sur le bouton : Test
J'ai l'erreur suivante :

Microsoft Visual Basic
Erreur de compilation:
Membre de methode ou de données introuvable

Avec comme encadrée en bleu .xlWorkbookDefault de la procedure Test.
13/03/2013, 12h06
Invité

Pour l’instant on ne va pas s’occuper du bouton.
Vas dans VB [Alt] + [F11] et exécute la macro [F5]
ou [Alt] +[F8] dans Excel
13/03/2013, 12h21
aminnio
Bonjour,

Bon j'ai un peu verifié le code, j'ai juste corrigé le .xlWorkbookDefault par .xlWorkbookNormal; normalement l'erreur est detourné. (sans oublier que je suis sur Excel 03).

Je me retrouve par contre, à chaque execution du code, avec un nouveau Classeur crée (non enregistré), mais sans resultat de la requete ...

Neanmoins, j'ai une autre erreur maintenant à ce niveau la :
Code:

1 2 For I = 1 To Rec.Fields.Count
Erreur 91 , variable objet ou variable de bloc with non definie.

Le recordSet n'est peut-etre pas declaré, j'ai ajouté à la declartion : dim Rec as ADODBRD.Recordset ; Toujours rien .
13/03/2013, 12h39
Invité
Code:

1 2 3 'ADODBRD.Recordset ça n'existe pas c'est une méthode de la class Dim Rec Set Rec = con.OpenRecordSet(SQL)
Positionne toi dans la macro test (c’elle qui se trouve dans le fichier que j’ai envoyé tel quelle), et tape à plusieurs reprise sur la touche [F8] et regarde si la connexion ce fait au besoin retire les commentaires sur les ''MsgBox Err.Description les commentaires c’est le caractère '

Voir 40 message(s) de cette discussion en une page