data uit access 2007 in listbox excel 2007

[Norbertus]

Beste lezers,

ik heb de vraag al eens eerder op dit forum geplaatst, maar nooit een goed werkende applicatie van kunnen maken. Misschien zijn er lezers die mij nu wel goed kunnen helpen.


-------------------
Ik gebruik Office 2007..

Ik wil graag in een userform van excell een listbox laten vullen met data uit een tabel van access.

Dit moeten meerdere kolommen kunnen zijn..
-------------------

Hoe krijg ik dit voorelkaar.

groeten, norbertus van der lei

 

[Finch]

Van de namiddag zal ik wat voorbeeldcode posten.

 

[Finch]

Hieronder wat voorbeeldcode:

Wat je nog dient te veranderen (=aanpassen aan jouw situatie):

* var. globDBPath aanpassen
* SQL statement aanpassen
* verwijzingen naar het userform en listbox aanpassen

(regels met vele uitroeptekens voor moet je aanpassen)

Je moet ook de verwijzing naar "Microsoft ActiveX Data Objects 6.x Library" binnen VBA leggen. Dat doe je door in de VBE Extras > Verwijzingen te kiezen en dan die aan te vinken en OK te drukken.

Option Explicit
'Constante voor Database connection string

'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
Private Const glob_DBPath = "C:\Users\USERNAME\Documents\test.accdb"
Private Const glob_sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & glob_DBPath & ";Persist Security Info=False;"


'--------------------------------------------------------------------------------------
'DIT NOG DOEN:

'het path in de variabele glob_DBPath moet je wijzigen naar het path naar jouw bestand
'het SQL statement in de variabele strSQL moet je wijzigen naar jouw situatie
'Verwijzingen naar het userform en de listbox aanpassen aan jouw situatie
'--------------------------------------------------------------------------------------
'---------------------------------------------------------------------------------
'!!Verwijzing naar Microsoft ActiveX Data Objects 6.x Library moet aanwezig zijn!!
'---------------------------------------------------------------------------------


Private Sub RetrieveRecordset()
'geen errorhandling aanwezig !
'oppassen met datums en OLE objecten
Dim strSQL As String
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rcArray As Variant
Dim lCols As Long

'SQL statement
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
strSQL = "SELECT tblTest.OmschrijvingID, tblTest.Omschrijving " & _
"FROM tblTest;"

'Open connection
cnt.Open glob_sConnect

'Open recordset
rst.Open strSQL, cnt

'Zet recordset in een array
rcArray = rst.GetRows

'clean up connection
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

'Bepaal aantal kolommen
lCols = UBound(rcArray, 1) + 1

'Listbox manipuleren en vullen met data
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
UserForm1.lstTest.ColumnCount = lCols

'Transponeren van rc is nodig anders foutieve uitvoer
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
UserForm1.lstTest.List = Application.WorksheetFunction.Transpose(rcArray)
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
UserForm1.Show

End Sub

 

[petero23]

foutmelding in routine

Hieronder wat voorbeeldcode:

Wat je nog dient te veranderen (=aanpassen aan jouw situatie):

* var. globDBPath aanpassen
* SQL statement aanpassen
* verwijzingen naar het userform en listbox aanpassen

(regels met vele uitroeptekens voor moet je aanpassen)

Je moet ook de verwijzing naar "Microsoft ActiveX Data Objects 6.x Library" binnen VBA leggen. Dat doe je door in de VBE Extras > Verwijzingen te kiezen en dan die aan te vinken en OK te drukken.

....
'Transponeren van rc is nodig anders foutieve uitvoer
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
UserForm1.lstTest.List = Application.WorksheetFunction.Transpose(rcArray)
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
UserForm1.Show

End Sub
[/CODE]

Beste Finch,

Dit is precies het stukje routine wat ik zocht, alleen krijg ik foutmelding "Typen komen niet met elkaar overeen" op de regel met Application.WorksheetFunction.Transpose(rcArray). rcArray is gevuld, dus daar kan het niet aan liggen.
Weet jij hoe ik dit kan oplossen?

PS "Microsoft ActiveX Data Objects 6.x Library" kon ik niet vinden, "Microsoft ActiveX Data Objects 2.8 Library" wel. kan het daaraan liggen?

PeterO23

 

[snb]

Koppel met een Databasequery een Accesstabel aan een lokatie in een werkblad. (menubalk/Data/Externe gegevens/Databasequery)

Stel dat die koppeling wordt gelegd in werkblad 'koppeling' in cel A1, dan stel je in een userform met een listbox1 het volgende in:

Private Sub Userform_Initialize()
  listbox1.list=sheets("koppeling").range("A1").currentregion
End Sub

 

[petero23]

Oplossing gevonden

Beste SNB,

Bedankt voor de snelle reactie, maar het probleem zat in het feit dat ik lege velden ophaalde en deze lege velden probeerde te transponeren.
Dus een voorwaarde voor deze routine is dat de velden in de tabel, die je wil uitlezen met de strSQL, volledig gevuld moeten zijn (dus voor die velden is een waarde vereist).