Keuze uit combobox => Keuze in excel pivot

[Michiel Karel]

Hallo allemaal!

ik heb op m'n formulier een combobox zitten met klantnamen.

Wanneer een gebruiker van m'n database op een knop klikt wordt er een excel export gemaakt met een pivot daarin.

De pivot is gebaseerd op een query met daarin alle gegevens.

De situatie is nu dat de gebruiker in die pivot in excel direct alle klanten ziet staan.
Nu kan ik wel met een macro er voor zorgen dat er maar 1 of hoeveel dan ook getoond worden, maar dan is het niet dynamisch en dat wil ik nu juist.

Ik wil het dus zo hebben dat de gebruiker in access de klant selecteerd in de combobox, en er in excel (via de exportmethode die ik heb ontwikkeld) direct gewerkt wordt met de data behorende bij die klant.

Nu heb ik in de query als criteria geplaatst bij de klantnaam : "Forms![formuliernaam]![comboboxnaam]" gezet.

als ik die query in access uitvoer is er geen vuiltje aan de lucht en laat hij keurig de records zien die bij die klant horen.

Echter als ik de excel export uitvoer (loadpivot methode), krijg ik de melding: error too few parameters: expected 1.

Helphelphelp. Het zou heel fijn zijn als iemand mij hier mee uit de brand zou kunnen helpen.

Alvast bedankt!

Michiel

 

[Tardis]

Hallo,

de loadpivot methode ken ik niet.
Van waaruit gebruik je die, vanuit Access?

Probleem zit 'm in je verwijzing naar je control in je formulier.
Wat je zou kunnen doen is:

- je query ombouwen naar een toevoegquery
- queryresultaat wegschrijven naar een tijdelijke tabel
- deze tabel gebruiken voor je export naar Excel

Groet,

Tardis

 

[Michiel Karel]

ik heb die loadpivot methode in VBA ontwikkeld, aangezien er in Access 2003 niet de mogelijkheid bestaat een pivot te exporteren naar een vaste excel file.

die methode die jij omschrijft had ik hiervoor al toegepast .
afterupdate activeerde in dat geval een create table query, die een tabel steeds overschreef zodat er maar 1 waarde in bleef staan. Deze waarde werd vervolgens met een databasequery naar binnen gehaald in de Excel file.

Maar ik wil mijn excel file leeg houden qua VBA code en de laadtijden zo beperkt mogelijk.
Het zou mooi zijn als ik vanuit access alles aan zou kunnen sturen. Maar daarbij geldt wel als voorwaarde dat ik zo min mogelijk tabellen / queries / macro's en andere elementen erin wil hebben zodat hij zoveel mogelijk ******proof wordt. Dit project is namelijk mijn afstudeeropdracht en ik wil geen ruimte voor beschadigingen / vervuiling achter laten.

De opbouw van de pivot en vormgeving van de verdere excelfile gebeurd allemaal vanuit Access. (opgebouwd in excel, vba code gemodificeerd en in access geknald).

 

[Tardis]

Hallo,

een andere aanpak dan het werken met een tijdelijke tabel zie ik niet.
Je beweegredenen om daar vanaf te stappen kan ik totaal niet volgen.

Groet,

Tardis

 

[Michiel Karel]

nou zoals ik al heb vermeld wil ik zo min mogelijk extra tabellen en queries in mijn database. Dit om hem ******proof en klein (en daarmee snel) te houden.

Als ik dit via een VBA code zou kunnen oplossen heb ik een vrij snelle en compacte oplossing gevonden die de gebruiker niet ziet en kan beschadigen. Dat is mijn beweegreden .

Als er echt geen alternatief is, is wat ik heb gedaan en wat jij hebt voorgesteld natuurlijk altijd een optie, maar ik vind dat het niet de schoonheidsprijs verdiend.

 

[Tardis]

Michiel,

ik begrijp dat je bepaalde redenen hebt, alleen lijken me die geen hout te snijden
Je kan in VBA een tabel creeren en ook weer verwijderen.
Dat is wat ik bedoel met een tijdelijke tabel, is een heel gewone aanpak in dit soort gevallen.

Groet,

Tardis

 

[Michiel Karel]

hmm ik zie net dat ik gisteren een stuk heb getypt wat op een ander aspect van mijn database slaat . foutje! Dat van die ene record in een tabel laten opslaan middels die create-table-query kan je vergeten.

Maar ik zal eens kijken naar je aanwijzingen, misschien dat ik er meer mee kan, je hoort later op de dag wel van me middels dit topic.

Thnx

 

[Michiel Karel]

het lijkt te gaan lukken! alleen gebruik ik een create table query ipv een toevoegquery.

 

[Tardis]

OK, zal met je meeduimen

Laat even weten of het gelukt is.

Groet,

Tardis

 

[Michiel Karel]

het is gelukt! waarschijnlijk heeft het duimen geholpen

nu moet ik alleen nog de waarde van een tekstbox zien te exporteren naar excel.
enig idee hoe me dat gaat lukken? zelfde manier?

 

[Tardis]

Hallo,

waarde van je textbox zou ik op dezelfde manier aanpakken.
Als je VBA code start vanuit een geopend formulier, kun je de waarde van een control op je formulier doorgeven aan een variabele.
Variabele definieer je dan in VBA.

Zoiets als

Dim strMyTextbox as String

strMyTextBox = Me.txtTextbox

Groet,

Tardis

 

[Michiel Karel]

Dim strMyTextbox as String

strMyTextBox = Me.txtTextbox

en dan volgt daarna denk ik:

with oaw.activesheet.range("a1")
.formula = me.txtTextbox.value

?????
(ik gebruik dat oaw.activesheet omdat ik met die loadpivot methode werk, anders loopt alles in de soep )

bedankt voor je hulp dat waardeer ik zeer!

 

[Michiel Karel]

Dim strMyTextbox As String
strMyTextbox = txtCurrentUser

With oAw.ActiveSheet.Range("a1")
.Formula = Me.txtCurrentUser.Value
End With

dit werkt niet .

een query maken die een temp-table aanmaakt gaat ook niet werken, aangezien ik niet een query kan maken die op geen tabel/query is gebaseerd. ik kan dus niet met een query de waarde van een textbox extracten en in een tabel zetten. het zal dus toch met vba moeten denk ik.

 

[Tardis]

Hallo Michael,

je zult toch even wat meer info moeten geven.

a) ik weet niet wat je wilt doen
b) ik ken je code niet
c) "werkt niet" is vrij vaag

Groet,

Tardis

 

[Michiel Karel]

a: ik wil de waarde van een textbox exporteren naar een cel in een xls
b: werkt niet => compileerfout: kan de methode of het gegevenslid niet vinden
c:

Option Compare Database
Dim sBasePath As String
Dim sExcelTemplateFile As String
Dim sDbname As String
Dim oAw As Excel.Workbook
Dim oApp As Excel.Application
Private Sub LoadPivot(sPivotTable As String)
With oAw.PivotCaches.Add(SourceType:=xlExternal)
.Connection = "ODBC;DSN=MS Access-Database;DBQ=" & sBasePath & sDbname & ".mdb;DefaultDir=" & sBasePath & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
.CommandType = xlCmdSql
.CommandText = "SELECT * FROM `" & sBasePath & sDbname & "`." & sPivotTable & " " & sPivotTable
.CreatePivotTable TableDestination:=oAw.ActiveSheet.Range("A8"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
End With
End Sub

Private Sub cmd_GR_GR_CP_Click()

'Change Paths Here
sBasePath = "Q:\Operations\Chainware_Reporting\"
sExcelTemplateFile = "Mainfile.xls"
sDbname = "Reporting"

' Initialize Vars
Set oApp = New Excel.Application
oApp.Visible = True
Set oAw = oApp.Workbooks.Open(sBasePath & sExcelTemplateFile)

'Link Excel to Access Datasource based on selection
LoadPivot (cbo_GR_GR_custom)

Select Case cbo_GR_GR_custom

Case "00_Spec_Basic_Shipment_Data"

' Set Row Fields
'blank => custom pivot

'set column fields
'blank => custom pivot

'set Data fields
'blank => custom pivot

'design layout
'blank => custom pivot

Dim strMyTextbox As String
strMyTextbox = txtCurrentUser

With oAw.ActiveSheet.Range("a1")
.Formula = Me.txtCurrentUser.Value
End With


' Set Title
With oAw.ActiveSheet.Range("A5")
.Formula = "Custom Report"
End With

oAw.ActiveSheet.Range("a1").Select

' Save file
Dim TheUser As String
TheUser = Environ("UserName")

oAw.SaveAs Filename:= _
"c:\documents and settings\" & CStr(TheUser) & "\My Documents\" & oAw.ActiveSheet.Range("A5").Value & "_" & Format(Now, "YYYYmmDDhhmm") & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

' Clean up Old Vars
oAw.Close
Set oAw = Nothing
oApp.Quit
Set oApp = Nothing
End Select
End Sub

 

[Tardis]

Hallo Michiel,

ik heb een uigeklede versie van je code uitgeprobeerd (zonder het pivot gedeelte).
Werkt op zich prima.

Als je je code aanroept, moet dat gebeuren op het moment dat je formulier nog openstaan (je code roep je dus aan via een handeling vanuit je formulier).
Op dat moment geef je in de code de waarde van je control in je formulier door aan een variabele.
Jouw stukje code waarin je dat doet klopt niet.
Wijzig dit

Dim strMyTextbox As String
strMyTextbox = txtCurrentUser

With oAw.ActiveSheet.Range("a1")
.Formula = Me.txtCurrentUser.Value
End With

in

Dim strMyTextbox As String
strMyTextbox = txtCurrentUser

With oAw.ActiveSheet.Range("a1")
.Formula = strMyTextbox
End With

PS definieer variabelen direct na het declareren van je procedure (sub of function).
Dat leest wat makkelijker

Groet,

Tardis

 

[Michiel Karel]

Bedankt voor je hulp, maar ik ben nog niet waar ik zijn wil.

Ik heb de code die je me hebt aangereikt geïmplementeerd in m'n code en ik kan de excel export nu zonder problemen uitvoeren.

Echter toont hij geen waarde in cel a1, ondanks dat de waarde van de textbox op KarelM staat. Er zou dus in cel a1 KarelM moeten komen te staan.

Dit komt omdat ik de code achter een knop heb die zich in een geopend subformulier bevindt, terwijl de txtbox zich in het hoofdformulier bevindt. Zou het mogelijk zijn een verwijzing te maken naar het hoofdformulier waar de textbox zich bevindt?

Dit had ik er natuurlijk meteen bij moeten zeggen, maar ik was me er niet van bewust dat dit er toe zou doen . Voor de rest werkt je code prima trouwens!

PS: wat vind je van mijn ontwikkelde VBA code om een pivot te exporteren?! Aangezien je niet bekend was met deze methode ben ik wel benieuwd wat je er van denkt

 

[Michiel Karel]

ik heb het vermoeden dat ik de naam van het hoofdformulier voor de naam van de textbox moet zetten bij het definiëren.

echter begint de naam van het hoofdform met een getal, en dat schijnt vba niet lekker te pakken, hij zet de verwijzing direct in het rood.

 

[Tardis]

Hallo Michiel,

hier kun je vinden hoe je verwijst naar controls op en vanuit hoofd- en subformulieren

http://www.mvps.org/access/forms/frm0031.htm

Access Help geeft ook info, even zoeken op Forms.

Je code lijkt me er netjes uit te zien.
Veel kan ik daar niet over zeggen, VBA is niet mijn vak

Groet,

Tardis

 

[Michiel Karel]

Access help biedt maar weinig uitkomst bij dit soort specifieke gevallen, ik kan de helpfunctie inmiddels dromen, maar ben er niet zo van gecharmeerd.

ervaren accessgebruikers op het web zijn 9 vd 10x veel waardevoller als hulpbron.

ik ga direct je link bekijken en zal hier even laten weten in hoeverre ik er in ben geslaagd het vraagstuk op te lossen.