hulp bij opzetten structuur bestand

[arjoderoon]

graag zou ik advies willen voor de structuur van een data bestand. Ik zal proberen uit te leggen wat ik probeer te bereiken:

ik heb een bestand met een aantal grafische weergaven van de achterliggende data. Deze data is afkomstig uit de Nielsen database.
Om de volledige data beschikbaar te hebben, heb ik nu 3 databases en daardoor ook 3 queries (om het zo maar even te noemen. De nielsen naam is 'range').

Ik heb deze 3 ranges onder elkaar geplakt op 1 sheet. vervolgens heb ik een 'schaduw' blad gemaakt waarin ik in een aantal kolommen nog samengevoegde waarden gecreeerd heb (dus bijvoorbeeld in kolom 2 komen voor iedere rij de waarden uit kolom d en e samengevoegd te staan).
Op basis van dit blad heb ik een aantal draaitabellen.

Nu wil ik eigenlijk hebt volgende:
1. iedere query op zijn eigen worksheet
2. 1 schaduwblad waar alle data van de 3 query bladen, terug komt, zodat ik zoals gezegd 1 bron blad heb voor de draaitabellen en waar ik dus ook die zelf gedefinieerde waarden heb.
3. maar wel zo dat alle data van de drie bladen meegenomen wordt.


nu is het namelijk zo dat met de huidige opzet, als de eerste query 2 regels meer blijkt te bevatten, ik nat ga want dan mis ik data van querie 2.
met als resultaat niet kloppende data in de grafische weergave.

wie kan mij van advies voorzien hoe ik punt 2 en 3 realiseer? (punt 1 is heel simpel namelijk).

 

[pixcel]

Drie tabellen samenvoegen doe je met een Union:
Select * from Tabel1
Union
Select * from Tabel2
Union
Select * from Tabel3
Met een voorbeeldje van wat je hebt en wat je wilt krijg je vast meer reacties.

 

[arjoderoon]

hoi, een voorbeeld bestandje ga ik proberen te maken. alleen omdat er specifieke software in gebruikt wordt, weet ik niet of dat lukt.
voorbeeld: ik heb zelf geprobeerd om de worksheet waar de query zijn data plaatst, om te zetten in een tabel. Dat werkt 1x maar als ik dan de query opnieuw draai is de tabel weg.

dus of de union dan ook werkt weet ik niet.

bestand uploaden via dit forum lukt hier niet. Dus ik heb een link naar het dropbox bestand:
https://www.dropbox.com/s/50fs7glovlxi9ch/voorbeeld helpmij.xlsx

dus ik heb nu 3 query tabbladen, deze worden regelmatig ververst en op het schaduwblad moet alle data uit de 3 tabbladen terecht komen.

 

[pixcel]

Ik zie in je voorbeeld geen externe gegevensverbindingen.
http://office.microsoft.com/nl-nl/e...-externe-gegevens-importeren-HP010089898.aspx

 

[arjoderoon]

dat klopt. ik heb nu in het voorbeeld gewoon even wat data getypt. er is ook geen externe verbinding in het bestand.
Het is lastig uit te leggen omdat het specifieke software is die gemaakt is en die zo gemaakt is dat deze in excel draait.
Deze software haalt de data op uit 3 databases die hier op de schijven staan. De output van deze software is dan in de vorm zoals die in het voorbeeld staat, alleen de software/link naar de software kan ik onmogelijk in het voorbeeld mee nemen (1 omdat het niet werkt en 2 omdat er nog iets zit met vertrouwelijkheid enzo).

Dus vanuit de software kant is er niets aan te doen. Ik gebruik die output ook als ruwe data waarbij ik zelf sowieso een schaduwblad gebruik.

 

[pixcel]

In dat geval: vergeet het union verhaal. Je probleem is dus eigenlijk het samenvoegen van de gegevens op drie werkbladen. Daar circuleren al veel voorbeelden van: zoek/google op "excel samenvoegen werkbladen" of "excel merge sheets".

 

[arjoderoon]

bedankt. Als ik de microsoft tutorial voor werkbladen samenvoegen gebruik en de bereiken opgeef, gebeurt er niets. Ik hou lege cellen.

 

[arjoderoon]

ik heb vanuit een ander forum een stuk vba gekopieerd en wat aangepast.

daarmee wordt nu de data uit de drie sheets op 1 sheet gekopieerd.

ik heb de kolommen opgegeven, dat werkt. Regels ga ik nog even checken maar ik geloof dat dat het probleem niet is.
Alleen: hoe krijg ik de macro zover dat deze uit in van de drie genoemde sheets de kolom headers ophaald?

ik heb nu:

ub CopyPaste()

Application.ScreenUpdating = False
Sheetnames = Array("cdt bhw", "cdt bh1", "cdt bh2")

With Sheets("Samenvoeging")
    Header = Sheets("cdt bh2").Range("A1").EntireRow
    .Range(.Range("A2"), .Cells(.Rows.Count, 1).End(xlUp)).ClearContents
    Sheets("cdt bh2").Range("A1").EntireRow = Header
End With

For i = LBound(Sheetnames) To UBound(Sheetnames)
    With Sheets(Sheetnames(i))
        .Range(.Range("L2"), .Cells(.Rows.Count, 1).End(xlUp)).Copy Sheets("Samenvoeging").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    End With
Next i
Application.ScreenUpdating = True

End Sub

Dat werkt maar alleen de kolom headers worden niet op gehaald. Deze staan in rij 1 van de genoemde tabbladen en zijn telkens gelijk.

plus dat ik de alles pas vanaf kolom C geplaatst wil hebben. Kolom A en B wil ik zelf gebruiken voor het creeeren van unieke variabelen en die moeten niet overschreven worden door de macro. Ik heb al wat zitten testen met offset en row count in het laatste deel maar ik krijg het nog niet voor elkaar.

 

[pixcel]

Als je het helpers gemakkelijk maakt krijg je een grotere kans op een reactie. Maak een klein voorbeeldbestandje dat je gewoon hier kunt neerzetten, met drie bronsheets met een paar rijen gegevens en een doelsheet, zet je code erin en geef aan waar het fout gaat.
Als je je voorbeeld maakt met tabellen (Invoegen > tabel) kan ik je er bij helpen, anders is er wellicht iemand anders.

 

[arjoderoon]

ik heb het bestand nu uitgekleed van de data. Het gaat mij nu om de macro die de data vanaf de drie tabbladen na het tabblad samenvoeging kopieert.

Als je de macro uitvoert (naam is copypaste) dan wordt alle data gekopieerd naar de juiste plek. Alleen de kolom titels die in de eerste rij moeten komen krijg ik nog niet op de goede plek (moet vanaf cel C1 en verder naar rechts zodat kolom a en b leeg blijven.

Daarbij ontdekte ik dat per tabblad niet alle regels meegenomen werden. Waarom heb ik zelf nog niet uit kunnen vogelen.
1 voorbeeld: in 1 tabblad staat in regel 91 in kolom A de klantnaam. De regel is verder leeg. Vanaf regel 92 is kolom A leeg voor de regels 92 t/m 101. Maar zijn de kolommmen B t/m Ai gevuld.
(dat is de structuur in de data uitdraai).

de klant in regel 91 is de laatste klant die in die sheet voorkomt, maar die wordt dus vanaf regel 92 niet meergenomen. Zit dat misschien inbesloten in de macro? dat er voor kolom A getest wordt wat de laatst gevulde regel is? hoe pas ik dit aan naar kolom Ai?

ik heb al in het rows.count de 1 aangepast naar 35 maar dan krijg ik geen data op mijn tabblad te zien helaas.er wordt geen data van de tabbladen door gekopieerd naar het blad samenvoeging. (mogelijk door de lege cellen die er tussendoor staan?)

link naar het bestand:
https://www.dropbox.com/s/5ozqku7ubvak4xw/voorbeeld helpmij.xlsx.xlsm

 

[pixcel]

Als je van de vier gegevensbereiken tabellen maakt (Invoegen > Tabel) en er voor zorgt dat de samenvoeg tabel dezelfde header heeft als de andere tabellen dan zou dit wel eens kunnen werken:

Sub MergeSheets()
    Dim i As Long
    Dim v As Variant
    Dim Sheetnames As Variant
    Sheetnames = Array("cdt bhw", "cdt bh1", "cdt bh2")
    Dim objDoel As ListObject
    Set objDoel = Sheets("Samenvoeging").ListObjects(1)
    If objDoel.ListRows.Count > 0 Then objDoel.DataBodyRange.EntireRow.Delete
    For Each v In Sheetnames
        With Worksheets(v).ListObjects(1).DataBodyRange
            objDoel.HeaderRowRange.Offset(objDoel.ListRows.Count + 1).Resize(.Rows.Count) = .Value
        End With
    Next v
End Sub

 

[arjoderoon]

bedankt voor je hulp om mij te helpen met het oplossen hiervan.
ik heb de 3 bereiken omzet naar een tabel. Om te testen of dit blijft bestaan heb ik de query's die de data op de 3 werkbladen schrijft gedraaid en de tabellen zijn er nadien nog steeds. dus ik ga je query proberen, eens kijken of het werkt!

1 vraag: op de 3 werkbladen beginnen de tabellen steeds in cel A1. Kan ik het in de query ook voor elkaar krijgen dat daar de data vanaf cel C1 geplaatst wordt?

(ik kan de bereiken eventueel verplaatsen naar C1 als eerste cel).

[eerste terugkoppeling:]
ik krijg een fout 9 error: het subscript valt buiten bereik

deze regel wordt gemarkeerd:

    Set objDoel = Sheets("samenvoeging").ListObjects(1)

[edit 2]:

ik heb nogmaals de hieronder gepost code geprobeerd:

ub CopyPaste()

Application.ScreenUpdating = False
Sheetnames = Array("cdt bhw", "cdt bh1", "cdt bh2")

With Sheets("Samenvoeging")
    Header = Sheets("cdt bh2").Range("A1").EntireRow
    .Range(.Range("A2"), .Cells(.Rows.Count, 1).End(xlUp)).ClearContents
    Sheets("cdt bh2").Range("A1").EntireRow = Header
End With

For i = LBound(Sheetnames) To UBound(Sheetnames)
    With Sheets(Sheetnames(i))
        .Range(.Range("L2"), .Cells(.Rows.Count, 1).End(xlUp)).Copy Sheets("Samenvoeging").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    End With
Next i
Application.ScreenUpdating = True

End Sub

Deze werkt vlekkeloos, het enige probleem zat hem in de titels toevoegen blijkbaar. dit stuk heb ik er nu uit gehaald dus ik krijg alle data op m'n gewenste sheet. 2 punten die ik nog wel nodig heb zijn:"
-het ophalen van de kolom titels vanuit 1 van de bladen. Deze moeten in eerste rij komen vanaf kolom C
-het blad 'samenvoeging' moet eerst leeg gemaakt worden voor de data opnieuw opgehaald wordt, zodat er niet per abuis nog oude data tussenstaat en mijn rapportage niet meer klopt.

ik heb via andere codes onderstaande samen gesteld (ik geloof direct dat dit efficienter kan):

Sub datawissen()
Dim lastrow As Long
Dim lastcolumn As Long


Application.ScreenUpdating = False
Sheetnames = Array("cdt bhw", "cdt bh1", "cdt bh2")

lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

lastcolumn = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

adres = Sheets("samenvoeging").Cells(lastrow, lastcolumn).Address

Sheets("samenvoeging").Range("C2", adres).ClearContents
  

End Sub

deze werkt super om de data in het blad te wissen. Alleen niet als er geen data in het blad staan.
dan krijg ik een error: fout 91: objectvariabele of blockvariabele with niet ingesteld

en wordt deze lijn gemarkeerd:

lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

wat mist er hier?

ik heb ze nu in een volgorde geplaatst dat eerst de data gewist wordt en daarna de data uit de sheets opgehaald wordt.

daarin moet de code dan wel werken...

 

[pixcel]

Sorry, maar zonder inzage in het systeem dat de gegevens schrijft en zonder inzage in het resultaat daarvan wordt dit me allemaal iets te vaag.
Ik hoop dat een ander je nog kan helpen. Succes!

 

[Haije]

arjo,

met deze code maak je een heel werkblad in één keer leeg (incl opmaak):

Cells.Clear

ook als er geen cellen met inhoud zijn