Pivot Table Variable SourceType/Data

[pvo2]

Hi,

I'm trying to create a macro which has an openfiledialog that allows the user to select the appropriate excel file and have the pivot creating macro run off that file.

However, I can't get the macro to work because the SourceData = sFile doesn't work.

I wish to create a variable which links to an openfiledialog that will ultimately feed into the pivot table cache.

Can anyone help me with this?

Thank you.

Sub GLRecoPivot2()
'
' GLRecoPivot2 Macro
' Macro recorded 31/03/2008 by pvo2
'

'
Dim sFile As String
sFile = Application.GetOpenFilename("Text Files Only (*.txt),*.txt,All Files (*.*),*.*", 1, "Select File To Open")

Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
sFile).CreatePivotTable _
TableDestination:="", TableName:="PivotTable4", DefaultVersion:= _
xlPivotTableVersion10ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable4").PivotFields ("ID")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields ("SRC Value MR Sum")
.Orientation = xlRowField
.Position = 2
End With
Range("B4").Select
ActiveSheet.PivotTables("PivotTable4").PivotFields ("SRC Value MR Sum"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable4").AddDataFiel d ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("SRC Value MR Sum"), "Count of SRC Value MR Sum", _
xlCount
ActiveSheet.PivotTables("PivotTable4").AddDataFiel d ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("RDW Value MS Sum"), "Count of RDW Value MS Sum", _
xlCount
Range("B3").Select
With ActiveSheet.PivotTables("PivotTable4").DataPivotFi eld
.Orientation = xlColumnField
.Position = 1
End With
Range("B4").Select
ActiveSheet.PivotTables("PivotTable4").PivotFields ("Count of SRC Value MR Sum") _
.Function = xlSum
Range("C4").Select
ActiveSheet.PivotTables("PivotTable4").PivotFields ("Count of RDW Value MS Sum") _
.Function = xlSum
End Sub

[T-J]

Hi,

I'm trying to create a macro which has an openfiledialog that allows the user to select the appropriate excel file and have the pivot creating macro run off that file.

However, I can't get the macro to work because the SourceData = sFile doesn't work.

I wish to create a variable which links to an openfiledialog that will ultimately feed into the pivot table cache.

Open the file first (GetOpenFilename doesn't open a file), find the used range and then set the range to the pivotcache sourcedata argument.

Please Login or Register  to view this content.