Excel VBA Database Query

[NogGeenGuru]

Goedemiddag formuleden,

Ik heb een workbook waarin ik urenregistraties uitlees naar horizontale "Urenlijnen" bestaande uit 4 kolomen (wie, wat, wanneer en uren).

nu wil ik hier graag analyses op los laten.
Normaal gesproken werk ik met SQL server en maak ik gebruik van select statements om de tabellen zo klein mogelijk te maken.
Nu wil ik voor dit project ook select statements gebruiken.
De urenlijnen zijn er erg veel dus een loop van begin tot eind zal de macro erg traag maken.

Ik heb al gekeken naar datatable objecten maar ik kom er niet helemaal uit hoe ik deze moet gebruiken.

is er een mogelijkheid om SELECT...FROM...WHERE te gebruiken in vba als de data uit een worksheet moet komen?

Gr, Marijn

 

[Peenvogel]

Als je een voorbeeldbestandje plaatst met de uitleg wat je precies wilt,
zullen de excel-goeroes je waarschijnlijk wel willen helpen.
Groetjes, Peenvogel.

 

[OctaFish]

Maar je kunt inderdaad een recordset maken op basis van een database tabel. Dus graag een voorbeeldje!

 

[NogGeenGuru]

Ik heb een voorbeeldbestandje bijgevoegd.

net zoals in het echte bestand staan in de kolommen foreign keys, Datums en Minuten.
De kolom namen moeten verder voor zich spreken denk ik.

Mijn huidige macro maakt gebruik van een For Loop die door alle regels heen loopt.
De eerste week was dit prima maar aangezien er een paar honderd regels per week bij komen wordt dit erg snel traag.

Ik wil graag verschillende datatables maken maar als iemand er eentje voor kan doen dan zou ik de rest zelf moeten kunnen maken.

als iemand mij kan laten zien hoe ik een datatabel kan maken van:
alle kolommen waarvoor geldt: Datum => IngevuldeDatum EN Datum < IngevuldeDatum + 7

en dan vervolgens alle overgebleven minuten bij elkaar optelt.

Aan de hand daarvan kan ik de rest dan zelf wel invullen.

Ik wil de IngevuldeDatum graag invullen in een inputbox.

Ik hoop hiermee voldoende informatie te hebben verschaft.

Alvast bedankt!

 

[snb]

Waarom geen voorbeeld van het gewenste resultaat ?

Een draaitabel lijkt mij voor jou de oplossing.

 

[NogGeenGuru]

Beste Snb,

Ik heb geen gewenst resultaat gemaakt omdat ik het gewenste resultaat niet wil kunnen zien.
Ik wil idd een soort van draaitabel maken maar dan een draaitabel die niet op een worksheet staat maar in een soort variabele.

Wat ik dus eigenlijk wil is:

Dim tb as Tabel
Dim ws as worksheet
set ws = activeworkbook.worksheets("Urenlijnen")

First_Reg = 2
First_Kol = 1
Last_Reg = ws.cells(rows.count,1).end(xlup).row
Last_Kol = ws.cells(1,columns.count).end(xltoleft).column

set tb = SELECT * FROM ws.range(cells(First_Reg, First_Kol),cells(Last_Reg,Last_kol)) WHERE <conditie>

Maar dan als een werkende macro

en dat ik dan berekeningen kan maken met de waardes die zich bevinden in tb.
zoals bijv. alle waardes optellen uit een kolom van tb.

is dit mogelijk?

 

[snb]

Ja hoor.

Maak het ons helpers gemakkelijk en beschrijf dan wel eerst het gewenste resultaat. Later kunnen we er wel voor zorgen dat er 'niets' te zien is.

 

[OctaFish]

Laten we eens beginnen met TS te helpen met zijn vraag; wellicht dat hij dan zelf wel de juiste antwoorden kan vinden.
Hier een voorbeeldje van hoe je een range in een recordset zet, en die recordset vervolgens filtert. Twee varianten: Early Binding en Late Binding. Voor de eerste variant moet je de ADO bibliotheek aanzetten.

Sub ADO_tabel()
Dim xlXML As Object
Dim rst As ADODB.Recordset
''Dim rst As Object
Dim fld As Object
Dim rng As Range
Dim rst2 As Object
Dim strSQL As String

        Set rng = Cells(1, 1).CurrentRegion
''        Set rst = New ADODB.Recordset
        Set rst = CreateObject("ADODB.Recordset")
        Set xlXML = CreateObject("MSXML2.DOMDocument")
        xlXML.LoadXML rng.Value(xlRangeValueMSPersistXML)
        rst.Open xlXML
        MsgBox rst.RecordCount
        rst.Filter = "[WerknemerNR] = 'W14'"
        MsgBox rst.RecordCount
End Sub

 

[jkpieterse]

Excel is geen SQL server. Beschrijf beter wat je wilt bereiken en dan kunnen wij eens bedenken hoe dat in Excel het beste gaat. NB: welke Excel versie gebruik je?

 

[NogGeenGuru]

Beste OctaFish,

Het maken en bewerken van een dataset is inderdaad wat ik probeer te bereiken.
Bedankt voor jouw voorbeeld!

Maar als ik jouw macro uitvoer dan krijg ik de volgende compileerfout: Een door de gebruiker gedefinieerd gegevenstype is niet gedefinieerd.

bij deze regel:

Dim rst As ADODB.Recordset

Weet jij ook hoe ik dit kan verhelpen?

 

[jkpieterse]

Waarom niet met advanced filter? Verplaats je tabel een rij of drie omlaag
Kopieer de eerste drie cellen (koprij meegeteld) van de datum kolom en plak die boven je tabel
Vul in A2 de gewenste start datum in
Vul in A3 =A2+7
Klik Gegevens, Filter, geavanceerd
Stel de juiste bereiken in en klik OK.