Excel TableDestination As String?

[gstoa]

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

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
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!

 

[OssieMac]

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:=xlDatabase, _
SourceData:="[" & strSourceWB & "]" & "YIS!R2C1:R15C26") _
..CreatePivotTable TableDestination:= _
"[" & strDestWB & "]" & "YIS!R18C1", _
TableName:=PivotTable15, _
DefaultVersion:=xlPivotTableVersion10