Pivot Table Update

[Geoff(UK)]

Pivot Table update

(Apologies to all who have just read this in the Programming Forum, bu
I am new and I am still feeling my way around ..... I also need an
answer ASAP!!)

Problem:

I have inherited an Access database part of which takes 4 text file
and then uses the data to update some Pivot tables

The code seems to work but comes to an abrupt halt with a Run Tim
Error 1004, "Applications-defined or Object-defined error". On clickin
the 'Debug' key, the following line is highlighted:

ActivityFile.PivotCaches(i).SourceData = "'Data '!" & DataArea

in the " for i = 1" loop shown below



Sub UpdatePivotTable(excelApp As Object, DataName As String, filenam
As String)

Dim ActivityFile, TextImport As Variant
Dim i As Integer
Dim DataArea As String

'Open text File and call Name TextImport
excelApp.workbooks.OpenText DataName, 2, 1, 1, 1, False, True, False
True, False, False
Set TextImport = excelApp.activeworkbook

'Open Activity Date File and call ActivityFile
excelApp.workbooks.Open filename:=filename
Set ActivityFile = excelApp.activeworkbook

ActivityFile.Sheets("Data").Visible = True

'Copy Data from TextImport to ActivityFile
ActivityFile.Sheets("Data").Activate
ActivityFile.Sheets("Data").range("A2"
ActivityFile.Sheets("Data").range("A2").SpecialCells(11).Address).Select
ActivityFile.Sheets("Data").range("A2"
ActivityFile.Sheets("Data").range("A2").SpecialCells(11).Address).Clear

TextImport.Sheets(1).range(TextImport.Sheets(1).ra nge("A1")
TextImport.Sheets(1).range("A1").SpecialCells(11)).Copy

ActivityFile.Sheets("Data").range("A1").Select
ActivityFile.Sheets("Data").Paste
ActivityFile.Sheets("Data").Activate
DataArea = excelApp.Selection.Address(, , -4150
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

If excelApp.Selection.Rows.Count < 2 Then
MsgBox "No Data imported to spreadsheet " & filename & ". There was d
data exported by the query check the dates you have run the reports o
and weather database is updadate.", vbOKOnly
ActivityFile.Close
TextImport.Close
Exit Sub
End If

'Update Pivot Table


******** ERROR OCCURS HERE IN THIS LOOP **************

For i = 1 To ActivityFile.PivotCaches.Count
ActivityFile.PivotCaches(i).SourceData = "'Data '!" & DataArea
ActivityFile.PivotCaches(i).Refresh
Next

ActivityFile.Sheets("Data").Visible = False

'Remove Pivot Toolbar
excelApp.CommandBars("PivotTable").Visible = False

'Save and Close down open workbooks
ActivityFile.Save
ActivityFile.Close
TextImport.Close
End Sub

Any ideas, please? I'm not an Excel person so I am fairly clueles
...........

Geof

 

[Geoff(UK)]

With regard to this problem .

when Access hangs with the error, a worksheet called "Data" is bein
displayed on the screen. At th bottom of the sheet (in the grey bar
is a message

"Select destination and press ENTER or choose Paste"

If I close this worksheet, the "Text" data in worksheet format i
displayed with all the data cells surrounded by a flickering dotte
line (presumably from a select all/copy operation)

So it looks as though we're trying to update the Pivot Tables befor
the text data has been imported into w/s "Data"?

How do I get around this?

TIA

Geof