Add a new Pivot Table with an Existing Connection using VBA

KrisW77

New Member
Joined
Feb 1, 2014
Messages
34
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
Code:
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!!
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

KrisW77

New Member
Joined
Feb 1, 2014
Messages
34
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,466
Members
414,069
Latest member
StudExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top