Making a Pivot Table from data of a Close sheet visa Button(Macro)

Hello

I have two sheets
(1) Sheet A has the the data
(2) Sheet B has a Button (w/ macro) that gets the data from Sheet A
pivoted. (Make Pivot Table of Data from Sheet A)

Working:
The button on Sheet B works fine when Sheet A is open and pivot the
data the way I want.

Issue:
But when sheet A is closed is closed, it gives the Error reading

"Cannot Open Pivot Table Source File <filename>"

[Exact Error: http://img820.imageshack.us/i/image001x.png/]

Can I somehow edit the macro in a way that it can Pivot the data even
when Sheet A is closed.

The VBA code for marco is given below.

Thanks & Regards,
- Nano

----------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------
VBA Code:


Sub GetDataOOW()
'
' GetDataOOW Macro
' Macro recorded 8/2/2010 by MHR
'

'
Range("A7").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
"'[Open-Orders.XLS]Open-Orders'!
R1C2:R2036C12").CreatePivotTable _
TableDestination:= _
"'[Clover sales and SOH 3PL1 - 3PL2 (TEMPLATE).xls]Open Orders
Work'!R7C1", _
TableName:="PivotTable8",
DefaultVersion:=xlPivotTableVersion10
With
ActiveSheet.PivotTables("PivotTable8").PivotFields("Material")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable8").AddDataField
ActiveSheet.PivotTables( _
"PivotTable8").PivotFields(" Open Qty."), "Count of Open
Qty.", xlCount
ActiveSheet.PivotTables("PivotTable8").PivotFields("Count of Open
Qty."). _
Function = xlSum
End Sub


----------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------

Open the source file at the beginning of the macro:

varSourceFileName = "C:\Documents and Settings\Clover sales and SOH 3PL1 - 3PL2 (TEMPLATE).xls"
Application.ScreenUpdating = False
Workbooks.Open Filename:=varSourceFileName

And close it again when your pivot table has been updated:

varSourceFileName.Close False
Application.ScreenUpdating = True

Thanks for the reply.

I have tried opening the file but it gives me the error in VB. May be I have to change the code of the macro as well.

I have attached the error with the post.

Hi

There was no attachment to your post.

Did you change the following line to the actual location of your file?

"C:\Documents and Settings\Clover sales and SOH 3PL1 - 3PL2 (TEMPLATE).xls"