Excel TableDestination As String?

G

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!
 
O

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top