[Excel2007]Draaitabel op basis van meerdere bestanden

[Paultje3181]

Ik wil met behulp van een macro een Draaitabel maken met een vergelijking tussen twee bestanden.
Bestand 1 is 2013_juli en die wil ik vergelijken met Bestand 2 (2012_juli). Hiervoor wil ik met een macro de gegevens binnen halen

Visual Basic:

Sub Draaitabellen()
'
' Draaitabellen Macro
' Gegeneren Pivottables
'
' Sneltoets: CTRL+SHIFT+P
'
dq = Chr(34)
Dim MyPath As String
Dim MyFileName As String
'The path and file names:
MyPath = "U:\Omzet\" & Cells(2, 1) & "\"
MyFileName = Cells(2, 1) & "_" & Cells(2, 2) & Cells(2, 3) & ".xlsx"
MyConnection = Cells(2, 1) & "_" & Cells(2, 2) & Cells(2, 3)
MySheet = "Draaitabel_" & MyConnection
MyFile = MyPath & MyFileName
Cells(23, 1).Value = MyFile
    Sheets.Add
    ActiveSheet.Name = MySheet
        ActiveWorkbook.Connections.Add dq & MyConnection & dq, "", Array( _
       "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=MyFile;Mode=Share Deny Write;Extended" _
       , _
       " Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine T" _
       , _
       "ype=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New " _
       , _
       "Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Comp" _
       , _
       "act=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False" _
       ), Array("Omzet$"), 3
   ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections(dq & MyConnection & dq), Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:=MySheet & "!R3C1", TableName:="Draaitabel1", _
        DefaultVersion:=xlPivotTableVersion12
    Sheets(MySheet).Select
    Cells(3, 1).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables(1).PivotFields("Af")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables(1).AddDataField ActiveSheet.PivotTables( _
        1).PivotFields("OTC"), "Som van OTC", xlSum
    ActiveSheet.PivotTables(1).AddDataField ActiveSheet.PivotTables( _
        1).PivotFields("Baxter"), "Som van Baxter", xlSum
    ActiveSheet.PivotTables(1).AddDataField ActiveSheet.PivotTables( _
        1).PivotFields("Regulier"), "Som van Regulier", xlSum
    ActiveSheet.PivotTables(1).AddDataField ActiveSheet.PivotTables( _
        1).PivotFields("Niet-WMG"), "Som van Niet-WMG", xlSum
    ActiveSheet.PivotTables(1).AddDataField ActiveSheet.PivotTables( _
        1).PivotFields("Totaal"), "Som van Totaal", xlSum
    With ActiveSheet.PivotTables(1).PivotFields("Jaar")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables(1).PivotFields("Af")
        .PivotItems("MS").Visible = False
        .PivotItems("SA").Visible = False
    End With
    With ActiveSheet.PivotTables(1).PivotFields("Maand")
        .Orientation = xlRowField
        .Position = 3
    End With
End Sub

Ik krijg echter deze draaitabel niet goed gedraaid. Het probleem zit erin dat

code:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections("2013021"), Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="Draaitabel!R3C1", TableName:="Draaitabel1", _
        DefaultVersion:=xlPivotTableVersion12

ActiveWorkbook.Connections("2013021") iets anders moet zijn nl. ActiveWorkbook.Connections("MyConnection") (dus de string MyConnection tussen aanhalingstekens). Dit krijg ik echter niet voor elkaar. Daarna moet ik nr. twee nog importeren en dan de vergelijking maken, maar dit lukt me wel (twee is nl. hetzelfde als 1, maar dan met andere waarden en met draaiformules krijg ik ook wel het verschil tussen die twee)

Ik zal vast iets heel doms over het hoofd zien, maar "" & MyConnection & "" werkt iig niet.
De MyPath, MyFileName en MyConnection geven goede waarden.

[ Voor 8% gewijzigd door Paultje3181 op 14-08-2013 11:25 ]

[Paultje3181]

Iemand enig idee hoe een draaitabel te importeren via een macro?

Heb het nu werkend via een workaround met de ruwe data eerst importeren. Nadeel is dat ik dan max. 3 mnd kan vergelijken. Een half jaar is te veel data. Los van het feit dat de bestanden ongeveer 60MB per stuk zijn...

[Paultje3181]

Nog een kickje dan maar... Echt niemand?

[pedorus]

Het lijkt me dat je van Connections.Add iets terugkrijgt dat je verderop moet gebruiken. Nu gooi je het resultaat weg en weet je niet meer waar je het zoeken moet.

Verder kan [code=vb] en goede inspringing en uitleg wat je nu eigenlijk aan het doen bent wel eens helpen.

[Paultje3181]

Ik gebruikte code=vba ipv vb. Sorry hiervoor. Wat ik doe is gebaseerd op een opgenomen macro, die ik probeer aan te passen. Ik heb een excel-file waarin ik maand en jaar aan kan geven (cells(2,1) en cells(2,2). Deze zijn in de naam verwerkt, waardoor ik MyPath & MyFilename aan kan roepen. Deze wil ik als basis gebruiken voor een draaitabel, die in de rest van de macro gedefinieerd wordt.

Er moet een connection gemaakt worden, maar hier gaat het ergens mis. Ik ben helaas geen macro-guru, dus snap niet hoe ik het kan fixen. Vandaar dat ik hier een hint hoop te krijgen.

[pedorus]

Laat ik het anders zeggen. Je gebruikt nu WorkBook.Connection.Add als procedure, maar (zeer waarschijnlijk) is dit een functie. Dus je kan iets als dit doen:

Visual Basic:

    Dim wbc As WorkbookConnection
    Set wbc = WorkBook.Connection.Add([...])
[...]SourceData:= wbc, [...]

[jjust]

Vooropgesteld ik ben ook geen macro goeroe dus als ik domme dingen zeg kan je het negeren.


Bij het opzetten van de verbinding doe je

Visual Basic:

Workbooks("GenererenVergelijking.xlsx").Connections.Add "" & MyConnection, "", Array .....

misschien moet je dit herhalen bij

Visual Basic:

ActiveWorkbook.Connections("2013021"), Version:=xlPivotTableVersion12). 

dus

Visual Basic:

ActiveWorkbook.Connections.Add "" & MyConnection, "", Version:=xlPivotTableVersion12). 

Trouwens als MyConnection wordt gedefinieerd jaar_maand1 maar hardcopy staat er "2013021"
Is dit een typo?

[Paultje3181]

Nee, dat is een van de probleempunten...

[Lustucru]

Om even te focussen op het toevoegen van primes of dubbel quotes aan strings:

Meestal volstaat ook een enkele quote. Zoniet, hieronder een overzicht van hoe je quotes opneemt in een variabele

Visual Basic:

Sub quotes()
    Dim var As String
    Dim dq As String
    
    var = "De tekst"
    dq = Chr(34)
    
    Debug.Print var
    Debug.Print "'" & var & "'"
    Debug.Print """" & var & """"
    Debug.Print Chr(34) & var & Chr(34)
    Debug.Print dq & var & dq

End Sub

[jjust]

Paultje3181 schreef op dinsdag 13 augustus 2013 @ 23:16:
Nee, dat is een van de probleempunten...

Volgens mij heb je de datum nu hardcopy in je code staan toch? Dit moet een variabele worden maar in deze code staat het hardcopy. Ik vraag me af waarom je daar de datum niet met een underscore hebt gezet terwijl in de variabele die je daarvoor in de plaats wilt zetten wel een underscore zit. Misschien zie ik het helemaal verkeerd maar dat viel mij op.

Daarnaast is het voorbeeld van lustucru handig.

[Paultje3181]

Ik heb nu op basis van jullie opmerkingen de startpost aangepast. Dit is nu mijn actuele code. Als ik deze uitvoer krijg ik een

code:

Fout 1004: De Microsoft Office-Access Database Engine kan het object Omzet$ niet vinden. Zorg ervoor dat het object bestaat en dat u de naam en het pad correct hebt gespeld.

Ik heb met Cells.Value de MyPath en MyFileName geprint en deze zijn goed. Ik heb dus geen idee waarom deze code niet werkt.

Edit:
De fout zit in het stukje

Visual Basic:

       ActiveWorkbook.Connections.Add dq & MyConnection & dq, "", Array( _
       "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=MyFile;Mode=Share Deny Write;Extended" _

Het lijkt erop dat Data Source=MyFile letterlijk gelezen wordt. Ik moet dus MyFile uit de array halen.

Gelukt: Data Source=" & MyFile & ";Mode
Door het hier op GoT te zetten zag ik dit... Stom dat code validatie en kleurtjes niet in VBA editor zitten... Maakt het leven wel makkelijker...

[ Voor 41% gewijzigd door Paultje3181 op 14-08-2013 11:31 ]

[jjust]

Top dat je het hebt gevonden. Daar kan je lang naar zoeken.