Excel TableDestination As String?
 

I've recorded an Excel macro which successfully creates a new pivot
table on mySheet.xls. For example:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:="YIS!R2C1:R15C26").CreatePivotTable
TableDestination:="[mySheet.xls] YIS!R18C1", TableName:=PivotTable15,
DefaultVersion:=xlPivotTableVersion10

I need to be able to dynamically change the TableDestination value
from mySheet.xls to a string variable as my macro will be creating
pivot tables across multiple workbooks.

I've tried turning this into one big string using Chr$(34) for the
double quotes but can't seem to get this working correctly. Any
suggestions are welcomed!

Excel TableDestination As String?
 

Try the following.

Dim strDestWB As String
Dim strSourceWB As String


'Edit the following 2 lines to match your workbook names.
'Note that the workbooks must be saved and must be open
'before the code will work.

strSourceWB = "Book1Source.xls"
strDestWB = "Book2pivot.xls"

Workbooks(strDestWB).PivotCaches.Add(SourceType:=x lDatabase, _
SourceData:="[" & strSourceWB & "]" & "YIS!R2C1:R15C26") _
..CreatePivotTable TableDestination:= _
"[" & strDestWB & "]" & "YIS!R18C1", _
TableName:=PivotTable15, _
DefaultVersion:=xlPivotTableVersion10
--
Regards,

OssieMac


"gstoa" wrote:

I've recorded an Excel macro which successfully creates a new pivot
table on mySheet.xls. For example:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:="YIS!R2C1:R15C26").CreatePivotTable
TableDestination:="[mySheet.xls] YIS!R18C1", TableName:=PivotTable15,
DefaultVersion:=xlPivotTableVersion10

I need to be able to dynamically change the TableDestination value
from mySheet.xls to a string variable as my macro will be creating
pivot tables across multiple workbooks.

I've tried turning this into one big string using Chr$(34) for the
double quotes but can't seem to get this working correctly. Any
suggestions are welcomed!