Add a new Pivot Table with an Existing Connection using VBA

[KrisW77]

I'm trying to create a new Pivot Table that has a connection to an existing External Data connection.
I've tried using the Macro Recorder, but running the sub doesn't work it jut errors wih

Run-time error '1004':
[Microsoft][ODBC Excel Driver] The connection for viewing your linked Microsoft Excel worksheet was lost.

I guess that the recorder isn't recording some important stage in the process.

The recorded code is

ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
       ActiveWorkbook.Connections("Query from Drill Sheet Database"), Version:= _
       xlPivotTableVersion14).CreatePivotTable TableDestination:="Sheet1!R1C1", _
       Tablename:="PivotTable1", DefaultVersion:=xlPivotTableVersion14

Can anyone advise what the code shoud be?

If it makes a difference, the External Connection is to a table in another Excel workbook.
(The connection has been created by VBA, and can post the code if it helps)

Also, can I omit the DefaultVersion so that it will be created with the version appropriate to the Excel version?
The code will likely be run on Excel 2007 onwards, but I wont know which.
Or will I need to discover the xcel version and then select the correct pivottableversions

Thanks

Kris

PS I did post this on Microsoft Answers, but the answer was to ask on another forum!!

 

[KrisW77]

I've done some more investigating and found that the code works fine when I don't have the source file open.

However, the source file is where all of my VBA code sits (the data for the connection is on just one sheet of the workbook). This way the new file with the pivot table and connection didn't have to be a macro-enabled workbook.
I realise I could just put the code in another workbook, but it would be far neater to have it within the source file.

Can anyone advise how the code below can be modified to allow this?
It errors at the section in bold, giving the "[Microsoft][ODBC Excel Driver]" message

Thanks

Kris

Sub Create_External_Link()
Dim cur_Path As String, cur_File As String, new_File As String
Dim conn_name As String, pvt_Name As String, pvt_Dest As String
Dim msg_File As Integer

'Create New Blank Workbook - Named Drill Sheet Connection with Date/Time Stamp
cur_File = ThisWorkbook.FullName
cur_Path = ThisWorkbook.Path
new_File = "Drill Sheet Connection - " & WorksheetFunction.Text(Now(), "yyyymmdd hh-mm-ss")
msg_File = MsgBox("Please Confirm Creation of Linked Workbook" & Chr(13) _
                    & new_File & Chr(13) _
                    & "File can be renamed once created.", vbOKCancel, "CONFIRM LINKED WORKBOOK")
If Not msg_File = vbOK Then Exit Sub
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=cur_Path & "\" & new_File & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

'Create Connection in New Workbook
conn_name = "Query from Drill Sheet Database"
Workbooks(new_File).Connections.Add conn_name, "" _
    , Array("ODBC;DBQ=", cur_File, ";" _
    , "DefaultDir=", cur_Path, ";" _
    , "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
    , "MaxBufferSize=2048;" _
    , "MaxScanRows=8;" _
    , "PageTimeout=50;" _
    , "ReadOnly=1;" _
    ), _
    Array("SELECT * " & Chr(13) & "" & Chr(10) & "FROM `Data$` `Data$`"), 2
    
'Create Pivot Table with Connection in Sheet(1) of New Workbook
pvt_Name = "pvt_Linked"
pvt_Dest = Workbooks(new_File).Sheets(1).Name & "!R1C1"
[B]ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal _
    , SourceData:=ActiveWorkbook.Connections(conn_name)) _
    .CreatePivotTable TableDestination:="Sheet1!R1C1" _
        , TableName:=pvt_Name[/B]    

'Sets Refresh as PivotCache Create Removes Refresh Settings
With ActiveWorkbook.Connections(conn_name).ODBCConnection
    .BackgroundQuery = True
    .RefreshOnFileOpen = True
End With

'Format Pivot Table with Most Useful Fields

End Sub