Worksheet Connections

[Milo1984]

Hi,

can you please help me with replacing the worksheet data below to point to the current workbook and worksheet?

It won't only be me running the code.

  Workbooks("Test.xlsm").Connections.Add2 _
        "WorksheetConnection_Test.xlsm!Table1", "", _
        "WORKSHEET;C:\Users\xx\xxxx\Excel1\Test.xlsm" _
        , "Test.xlsm!Table1", 7, True, False
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections( _
        "WorksheetConnection_Test.xlsm!Table1"), Version _
        :=6).CreatePivotTable TableDestination:="Sheet5!R3C1", TableName:= _
        "PivotTable2", DefaultVersion:=6

Many thanks in advance

 

[Milo1984]

Hi, is this possible at all? can I replace the Workbooks with the current names?

 

[Milo1984]

Hi,

sorry to keep asking, but can someone say if it is possible or not, otherwise I will need to find an alternative solution.

to re-iterate. Instead of "test.xlsm" and "

C:\Users\xx\xxxx\Excel1\Test.xlsm​

" I need it to simply reference the current workbook. Is this possible?