Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11

[VBA] TDB avec Excel 2007

3 réponses
Avatar
jean-francois LEGRAS
Re-bonjour à vous,


Bon j'attends ma réponse par rapport à la possibilité de résoudre le pb des
65000 lignes sans passer sur Excel 2007, mais je crais qu'elle soit
négative, donc j'anticipe... :-)


Je coince dans un premier temps sur la "traduction" (ou l'adaptation plutôt)
des codes VB pour faire des TDB.

Voici les 2 codes fonctionnant avec Excel 2003 et que je souhaiterais faire
fonctionner avec Excel 2007 (bugs pour l'instant) :


1er code :


"


Set MonTCD1 = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=[stats!A1:B2]).CreatePivotTable(TableDestination:=Sheets("onglet1").Cells(3,
1), TableName:="Tableau croisé dynamique",
DefaultVersion:=xlPivotTableVersion10)

With MonTCD1

.SourceData = [stats!A1].CurrentRegion.Address(, , xlR1C1, True)
.AddFields RowFields:="Livré"

With .PivotFields("Net"): .Orientation = xlDataField: .Position = 1:
.Function = xlSum: End With
With .PivotFields("Rubrique"): .Orientation = xlColumnField: .Position =
1: End With

End With


Dim p As PivotItem
With MonTCD1.PivotFields("Rubrique")
For Each p In .PivotItems
p.Visible = True
Next p
For Each p In .PivotItems
If p.Value = "016 PROD SCOLAIR/EDUCAT " Or p.Value = "017 LOISIRS
CREAT/JEUX " Then p.Visible = False
Next p
End With


"




2ème code :

"

Dim MonTCD1 As PivotTable

Sheets("livré").Cells.Delete

Set MonTCD1 = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=[hiérarchie!A1:B2]).CreatePivotTable(TableDestination:=Sheets("livré").Cells(3,
1), TableName:="Tableau croisé dynamique6", DefaultVersion:=xlTable4)

With MonTCD1


.Format xlTable4
.SourceData = [hiérarchie!A1].CurrentRegion.Address(, , xlR1C1, True)
.AddFields ColumnFields:="Données"
.AddFields RowFields:="Livré "


With .PivotFields("total 2007"): .Orientation = xlDataField: .Position =
1: .Function = xlSum: End With
With .PivotFields("IT 2007"): .Orientation = xlDataField: .Position = 2:
.Function = xlSum: End With
With .PivotFields("papier 2007"): .Orientation = xlDataField: .Position
= 3: .Function = xlSum: End With
With .PivotFields("GOP 2007"): .Orientation = xlDataField: .Position =
4: .Function = xlSum: End With
With .PivotFields("mobilier 2007"): .Orientation = xlDataField:
.Position = 5: .Function = xlSum: End With
With .PivotFields("hygiène 2007"): .Orientation = xlDataField: .Position
= 6: .Function = xlSum: End With
With .PivotFields("MC 2007 OUI"): .Orientation = xlDataField: .Position
= 7: .Function = xlSum: End With
With .PivotFields("CAT 2007 OUI"): .Orientation = xlDataField: .Position
= 8: .Function = xlSum: End With
With .PivotFields("MEDIA"): .Orientation = xlDataField: .Position = 9:
.Function = xlSum: End With



End With


"


Auriez-vous la solution chers amis ?


Merci beaucoup d'avance !

Slts
JF

3 réponses

Avatar
jean-francois LEGRAS
Re,


J'ai a priori trouvé une solution pour le 1er code (seulement)

Tout d'abord, ca ne pouvait pas fonctionner parce que j'injectais justement
des fichiers de plus de 65000 lignes et que j'avais des formules de type
Range("A65535").End(xlUp).Offset(0, 190).Select. (au lieu de "A1000000"), ce
qui supprimait les "étiquettes" (je crois qu'on appelle ça comme ça) des
fichiers sources et donc rendaient impossible la construction d'un TDB
(j'espère être clair... jamais facile en informatique...).

Ensuite, j'ai modifié le code comme suit :


"

Set MonTCD1 = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=[stats!A1:B2]).CreatePivotTable(TableDestination:=Sheets("onglet1").Cells(3,
1), TableName:="Tableau croisé dynamique")

... etc...

"

J'ai donc juste supprimé la dernière partie
"DefaultVersion:=xlPivotTableVersion10"

J'espère tomber sur mes pates à la fin... En tous cas, ca ne bloque plus.


En revanche, ca buggue toujours pour le 2ème code, pour rappel :



"

Set MonTCD1 = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=[hiérarchie!A1:B2]).CreatePivotTable(TableDestination:=Sheets("livré").Cells(3,
1), TableName:="Tableau croisé dynamique6", DefaultVersion:=xlTable4)

With MonTCD1


.Format xlTable4
.SourceData = [hiérarchie!A1].CurrentRegion.Address(, , xlR1C1, True)
.AddFields ColumnFields:="Données"
.AddFields RowFields:="Livré "


With .PivotFields("total 2007"): .Orientation = xlDataField: .Position =
1: .Function = xlSum: End With
With .PivotFields("IT 2007"): .Orientation = xlDataField: .Position = 2:
.Function = xlSum: End With
With .PivotFields("papier 2007"): .Orientation = xlDataField: .Position
= 3: .Function = xlSum: End With
With .PivotFields("GOP 2007"): .Orientation = xlDataField: .Position =
4: .Function = xlSum: End With
With .PivotFields("mobilier 2007"): .Orientation = xlDataField:
.Position = 5: .Function = xlSum: End With
With .PivotFields("hygiène 2007"): .Orientation = xlDataField: .Position
= 6: .Function = xlSum: End With
With .PivotFields("MC 2007 OUI"): .Orientation = xlDataField: .Position
= 7: .Function = xlSum: End With
With .PivotFields("CAT 2007 OUI"): .Orientation = xlDataField: .Position
= 8: .Function = xlSum: End With
With .PivotFields("MEDIA"): .Orientation = xlDataField: .Position = 9:
.Function = xlSum: End With


End With


"



Merci d'avance !

Slts
JF


"jean-francois LEGRAS" a écrit dans le message de news:

Re-bonjour à vous,


Bon j'attends ma réponse par rapport à la possibilité de résoudre le pb
des 65000 lignes sans passer sur Excel 2007, mais je crais qu'elle soit
négative, donc j'anticipe... :-)


Je coince dans un premier temps sur la "traduction" (ou l'adaptation
plutôt) des codes VB pour faire des TDB.

Voici les 2 codes fonctionnant avec Excel 2003 et que je souhaiterais
faire fonctionner avec Excel 2007 (bugs pour l'instant) :


1er code :


"


Set MonTCD1 = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=[stats!A1:B2]).CreatePivotTable(TableDestination:=Sheets("onglet1").Cells(3,
1), TableName:="Tableau croisé dynamique",
DefaultVersion:=xlPivotTableVersion10)

With MonTCD1

.SourceData = [stats!A1].CurrentRegion.Address(, , xlR1C1, True)
.AddFields RowFields:="Livré"

With .PivotFields("Net"): .Orientation = xlDataField: .Position = 1:
.Function = xlSum: End With
With .PivotFields("Rubrique"): .Orientation = xlColumnField: .Position
= 1: End With

End With


Dim p As PivotItem
With MonTCD1.PivotFields("Rubrique")
For Each p In .PivotItems
p.Visible = True
Next p
For Each p In .PivotItems
If p.Value = "016 PROD SCOLAIR/EDUCAT " Or p.Value = "017 LOISIRS
CREAT/JEUX " Then p.Visible = False
Next p
End With


"




2ème code :

"

Dim MonTCD1 As PivotTable

Sheets("livré").Cells.Delete

Set MonTCD1 = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=[hiérarchie!A1:B2]).CreatePivotTable(TableDestination:=Sheets("livré").Cells(3,
1), TableName:="Tableau croisé dynamique6", DefaultVersion:=xlTable4)

With MonTCD1


.Format xlTable4
.SourceData = [hiérarchie!A1].CurrentRegion.Address(, , xlR1C1, True)
.AddFields ColumnFields:="Données"
.AddFields RowFields:="Livré "


With .PivotFields("total 2007"): .Orientation = xlDataField: .Position
= 1: .Function = xlSum: End With
With .PivotFields("IT 2007"): .Orientation = xlDataField: .Position =
2: .Function = xlSum: End With
With .PivotFields("papier 2007"): .Orientation = xlDataField: .Position
= 3: .Function = xlSum: End With
With .PivotFields("GOP 2007"): .Orientation = xlDataField: .Position =
4: .Function = xlSum: End With
With .PivotFields("mobilier 2007"): .Orientation = xlDataField:
.Position = 5: .Function = xlSum: End With
With .PivotFields("hygiène 2007"): .Orientation = xlDataField:
.Position = 6: .Function = xlSum: End With
With .PivotFields("MC 2007 OUI"): .Orientation = xlDataField: .Position
= 7: .Function = xlSum: End With
With .PivotFields("CAT 2007 OUI"): .Orientation = xlDataField:
.Position = 8: .Function = xlSum: End With
With .PivotFields("MEDIA"): .Orientation = xlDataField: .Position = 9:
.Function = xlSum: End With



End With


"


Auriez-vous la solution chers amis ?


Merci beaucoup d'avance !

Slts
JF




Avatar
jean-francois LEGRAS
Bon pour le 2ème code, je crois que le problème vient du fait que je suis
sur la version anglaise d'Excel.


J'ai remplacé "données" par data, et ca a l'air de fonctionner en supprimant
aussi "DefaultVersion:=xlTable4" (qu'il ne reconnait pas apparemment).

Je fais un essai demain et je vous tiens au courant...

Slts
JF




"jean-francois LEGRAS" a écrit dans le message de news:
%
Re,


J'ai a priori trouvé une solution pour le 1er code (seulement)

Tout d'abord, ca ne pouvait pas fonctionner parce que j'injectais
justement des fichiers de plus de 65000 lignes et que j'avais des formules
de type Range("A65535").End(xlUp).Offset(0, 190).Select. (au lieu de
"A1000000"), ce qui supprimait les "étiquettes" (je crois qu'on appelle ça
comme ça) des fichiers sources et donc rendaient impossible la
construction d'un TDB (j'espère être clair... jamais facile en
informatique...).

Ensuite, j'ai modifié le code comme suit :


"

Set MonTCD1 = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=[stats!A1:B2]).CreatePivotTable(TableDestination:=Sheets("onglet1").Cells(3,
1), TableName:="Tableau croisé dynamique")

... etc...

"

J'ai donc juste supprimé la dernière partie
"DefaultVersion:=xlPivotTableVersion10"

J'espère tomber sur mes pates à la fin... En tous cas, ca ne bloque plus.


En revanche, ca buggue toujours pour le 2ème code, pour rappel :



"

Set MonTCD1 = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=[hiérarchie!A1:B2]).CreatePivotTable(TableDestination:=Sheets("livré").Cells(3,
1), TableName:="Tableau croisé dynamique6", DefaultVersion:=xlTable4)

With MonTCD1


.Format xlTable4
.SourceData = [hiérarchie!A1].CurrentRegion.Address(, , xlR1C1, True)
.AddFields ColumnFields:="Données"
.AddFields RowFields:="Livré "


With .PivotFields("total 2007"): .Orientation = xlDataField: .Position
= 1: .Function = xlSum: End With
With .PivotFields("IT 2007"): .Orientation = xlDataField: .Position =
2: .Function = xlSum: End With
With .PivotFields("papier 2007"): .Orientation = xlDataField: .Position
= 3: .Function = xlSum: End With
With .PivotFields("GOP 2007"): .Orientation = xlDataField: .Position =
4: .Function = xlSum: End With
With .PivotFields("mobilier 2007"): .Orientation = xlDataField:
.Position = 5: .Function = xlSum: End With
With .PivotFields("hygiène 2007"): .Orientation = xlDataField:
.Position = 6: .Function = xlSum: End With
With .PivotFields("MC 2007 OUI"): .Orientation = xlDataField: .Position
= 7: .Function = xlSum: End With
With .PivotFields("CAT 2007 OUI"): .Orientation = xlDataField:
.Position = 8: .Function = xlSum: End With
With .PivotFields("MEDIA"): .Orientation = xlDataField: .Position = 9:
.Function = xlSum: End With


End With


"



Merci d'avance !

Slts
JF


"jean-francois LEGRAS" a écrit dans le message de
news:
Re-bonjour à vous,


Bon j'attends ma réponse par rapport à la possibilité de résoudre le pb
des 65000 lignes sans passer sur Excel 2007, mais je crais qu'elle soit
négative, donc j'anticipe... :-)


Je coince dans un premier temps sur la "traduction" (ou l'adaptation
plutôt) des codes VB pour faire des TDB.

Voici les 2 codes fonctionnant avec Excel 2003 et que je souhaiterais
faire fonctionner avec Excel 2007 (bugs pour l'instant) :


1er code :


"


Set MonTCD1 = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=[stats!A1:B2]).CreatePivotTable(TableDestination:=Sheets("onglet1").Cells(3,
1), TableName:="Tableau croisé dynamique",
DefaultVersion:=xlPivotTableVersion10)

With MonTCD1

.SourceData = [stats!A1].CurrentRegion.Address(, , xlR1C1, True)
.AddFields RowFields:="Livré"

With .PivotFields("Net"): .Orientation = xlDataField: .Position = 1:
.Function = xlSum: End With
With .PivotFields("Rubrique"): .Orientation = xlColumnField: .Position
= 1: End With

End With


Dim p As PivotItem
With MonTCD1.PivotFields("Rubrique")
For Each p In .PivotItems
p.Visible = True
Next p
For Each p In .PivotItems
If p.Value = "016 PROD SCOLAIR/EDUCAT " Or p.Value = "017 LOISIRS
CREAT/JEUX " Then p.Visible = False
Next p
End With


"




2ème code :

"

Dim MonTCD1 As PivotTable

Sheets("livré").Cells.Delete

Set MonTCD1 = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=[hiérarchie!A1:B2]).CreatePivotTable(TableDestination:=Sheets("livré").Cells(3,
1), TableName:="Tableau croisé dynamique6", DefaultVersion:=xlTable4)

With MonTCD1


.Format xlTable4
.SourceData = [hiérarchie!A1].CurrentRegion.Address(, , xlR1C1, True)
.AddFields ColumnFields:="Données"
.AddFields RowFields:="Livré "


With .PivotFields("total 2007"): .Orientation = xlDataField: .Position
= 1: .Function = xlSum: End With
With .PivotFields("IT 2007"): .Orientation = xlDataField: .Position =
2: .Function = xlSum: End With
With .PivotFields("papier 2007"): .Orientation = xlDataField:
.Position = 3: .Function = xlSum: End With
With .PivotFields("GOP 2007"): .Orientation = xlDataField: .Position =
4: .Function = xlSum: End With
With .PivotFields("mobilier 2007"): .Orientation = xlDataField:
.Position = 5: .Function = xlSum: End With
With .PivotFields("hygiène 2007"): .Orientation = xlDataField:
.Position = 6: .Function = xlSum: End With
With .PivotFields("MC 2007 OUI"): .Orientation = xlDataField:
.Position = 7: .Function = xlSum: End With
With .PivotFields("CAT 2007 OUI"): .Orientation = xlDataField:
.Position = 8: .Function = xlSum: End With
With .PivotFields("MEDIA"): .Orientation = xlDataField: .Position = 9:
.Function = xlSum: End With



End With


"


Auriez-vous la solution chers amis ?


Merci beaucoup d'avance !

Slts
JF








Avatar
isabelle
bonjour JF,

j'aepere que cela pourra t'aider car je n'ai pas ex2007,
ces deux exemples ont été trouver ici :
http://msdn.microsoft.com/en-us/library/bb223299.aspx
sous la rubrique : PivotCache.CreatePivotTable Method

isabelle


With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = _
"OLEDB;Provider=MSOLAP;Location=srvdata;Initial Catalog=National"
.CommandType = xlCmdCube
.CommandText = Array("Sales")
.MaintainConnection = True
.CreatePivotTable TableDestination:=Range("A3"), _
TableName:= "PivotTable1"
End With
With ActiveSheet.PivotTables("PivotTable1")
.SmallGrid = False
.PivotCache.RefreshPeriod = 0
With .CubeFields("[state]")
.Orientation = xlColumnField
.Position = 1
End With
With .CubeFields("[Measures].[Count Of au_id]")
.Orientation = xlDataField
.Position = 1
End With
End With

This example creates a new PivotTable cache using an ADO connection to
Microsoft Jet,
and then it creates a new PivotTable report based on the cache, at cell
A3 on the active worksheet.

Visual Basic for Applications
Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command

' Open the connection.
Set cnnConn = New ADODB.Connection
With cnnConn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open "C:perfdaterecord.mdb"
End With

' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = "Select Speed, Pressure, Time From DynoRun"
.CommandType = adCmdText
.Execute
End With

' Open the recordset.
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand

' Create a PivotTable cache and report.
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlExternal)
Set objPivotCache.Recordset = rstRecordset
With objPivotCache
.CreatePivotTable TableDestination:=Range("A3"), _
TableName:="Performance"
End With

With ActiveSheet.PivotTables("Performance")
.SmallGrid = False
With .PivotFields("Pressure")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Speed")
.Orientation = xlColumnField
.Position = 1
End With
With .PivotFields("Time")
.Orientation = xlDataField
.Position = 1
End With
End With

' Close the connections and clean up.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordSet = Nothing
Set cnnConn = Nothing




jean-francois LEGRAS a écrit :
Re-bonjour à vous,


Bon j'attends ma réponse par rapport à la possibilité de résoudre le pb des
65000 lignes sans passer sur Excel 2007, mais je crais qu'elle soit
négative, donc j'anticipe... :-)


Je coince dans un premier temps sur la "traduction" (ou l'adaptation plutôt)
des codes VB pour faire des TDB.

Voici les 2 codes fonctionnant avec Excel 2003 et que je souhaiterais faire
fonctionner avec Excel 2007 (bugs pour l'instant) :


1er code :


"


Set MonTCD1 = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=[stats!A1:B2]).CreatePivotTable(TableDestination:=Sheets("onglet1").Cells(3,
1), TableName:="Tableau croisé dynamique",
DefaultVersion:=xlPivotTableVersion10)

With MonTCD1

.SourceData = [stats!A1].CurrentRegion.Address(, , xlR1C1, True)
.AddFields RowFields:="Livré"

With .PivotFields("Net"): .Orientation = xlDataField: .Position = 1:
.Function = xlSum: End With
With .PivotFields("Rubrique"): .Orientation = xlColumnField: .Position =
1: End With

End With


Dim p As PivotItem
With MonTCD1.PivotFields("Rubrique")
For Each p In .PivotItems
p.Visible = True
Next p
For Each p In .PivotItems
If p.Value = "016 PROD SCOLAIR/EDUCAT " Or p.Value = "017 LOISIRS
CREAT/JEUX " Then p.Visible = False
Next p
End With


"




2ème code :

"

Dim MonTCD1 As PivotTable

Sheets("livré").Cells.Delete

Set MonTCD1 = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=[hiérarchie!A1:B2]).CreatePivotTable(TableDestination:=Sheets("livré").Cells(3,
1), TableName:="Tableau croisé dynamique6", DefaultVersion:=xlTable4)

With MonTCD1


.Format xlTable4
.SourceData = [hiérarchie!A1].CurrentRegion.Address(, , xlR1C1, True)
.AddFields ColumnFields:="Données"
.AddFields RowFields:="Livré "


With .PivotFields("total 2007"): .Orientation = xlDataField: .Position =
1: .Function = xlSum: End With
With .PivotFields("IT 2007"): .Orientation = xlDataField: .Position = 2:
.Function = xlSum: End With
With .PivotFields("papier 2007"): .Orientation = xlDataField: .Position
= 3: .Function = xlSum: End With
With .PivotFields("GOP 2007"): .Orientation = xlDataField: .Position =
4: .Function = xlSum: End With
With .PivotFields("mobilier 2007"): .Orientation = xlDataField:
.Position = 5: .Function = xlSum: End With
With .PivotFields("hygiène 2007"): .Orientation = xlDataField: .Position
= 6: .Function = xlSum: End With
With .PivotFields("MC 2007 OUI"): .Orientation = xlDataField: .Position
= 7: .Function = xlSum: End With
With .PivotFields("CAT 2007 OUI"): .Orientation = xlDataField: .Position
= 8: .Function = xlSum: End With
With .PivotFields("MEDIA"): .Orientation = xlDataField: .Position = 9:
.Function = xlSum: End With



End With


"


Auriez-vous la solution chers amis ?


Merci beaucoup d'avance !

Slts
JF