how can i get READ EXCEL DATA with VBA

[@ndy]

Have an Access application and it must read data from an Excel sheet.
How can i do that??

Thanx

 

[jaf]

Hi,
Well the easist way is in Access do: file>get external data.
You have the option to import the sheet as a table or link to it.
Using named ranges in Excel works best.

 

[valentin]

Hello,
Yes of course !!
Best regards
Valentin

 

[valentin]

Hello,
With DAO technology :


Option Compare Database

Option Explicit



Sub TableLieeAvecExcel()

Dim MaBase As Database

Dim MaTable As TableDef

Set MaBase = CurrentDb

Set MaTable = MaBase.CreateTableDef("TableExcel")

MaTable.Connect = "Excel 5.0;HDR=yes;DATABASE=c:\Mes
Documents\MaBaseExcel.xls"

MaTable.SourceTableName = "Contacts$"

MaBase.TableDefs.Append MaTable

MsgBox "fin"



End Sub


With ADO technology :


Private Sub cmdValider_Click()

Dim Oconn As Connection

Dim rs As Recordset

Dim sngSalaire As Single

Dim strSQL As String

Dim strChemin As String

Dim strNom As String

Dim strPrenom As String

Dim strEmploi As String

Dim strFiliale As String



' instanciation des objets



Set Oconn = New Connection

Set rs = New Recordset



' récupération du salaire saisi , attention il faut convertir en numérique
avec Val

sngSalaire = Val(txtSalaire)



' Préparation de la requête SQL dans la variable
strSQL = "SELECT * FROM tblClient WHERE Salaire > '" & sngSalaire & "'"



' stockage du chemin dans la variable

strChemin = "e:\preparation_cours\Bdd.xls"



' appel du Driver Excel ( technique DSN Less )

Oconn.Open "Driver={Microsoft Excel Driver (*.xls)}; DBQ=" & strChemin



' ouverture du recordset

rs.Open (strSQL), Oconn, adOpenDynamic, adLockPessimistic



' boucle de recuperation des données

Do While Not rs.EOF

strNom = rs.Fields("Nom")

strPrenom = rs.Fields("Prénom")

strEmploi = rs.Fields("Emploi")

strFiliale = rs.Fields("Filiale")

MsgBox strNom & " " & strPrenom & " " & strEmploi & " " & strFiliale

rs.MoveNext

Loop

MsgBox "Fin"

Oconn.Close

Set Oconn = Nothing

Set rs = Nothing

End Sub



Best regards
Valentin