Having looked through the VBA i have noticed the SourceType is xlExternal.
This is the VBA i am using to update the pivot tables:
Sheets(Sheets(x).Name).Range("AQ5").Select
Sheets(Sheets(x).Name).PivotTableWizard SourceType:=xlExternal, SourceData:=Array( _
"SELECT uthead.uh_account, uthead.uh_mref, utcnt.uu_counter, uthead.uh_oref, utcnt.uu_start, utcnt.uu_vprice, sname.sn_name, umach.um_type, utcnt.uu_finish, uu_finish-uu_start AS 'Vends'" & Chr(13) & "" & Chr(10) & "FROM sname sn" _
, _
"ame, umach umach, utcnt utcnt, uthead uthead" & Chr(13) & "" & Chr(10) & "WHERE utcnt.uu_mref = uthead.uh_mref AND utcnt.uu_date = uthead.uh_date AND sname.sn_account = uthead.uh_account AND uthead.uh_mref = umach.um_ref AND ((u" _
, _
"thead.uh_date>={d '" & Date1 & "'} And uthead.uh_date<={d '" & date2 & "'}) AND (uthead.uh_account<>'CYM001') AND (uthead.uh_oref='" & ActiveSheet.Name & "')AND (utcnt.uu_counter In ('98','99')))" _
), Connection:=Array(Array( _
"ODBC;Driver={Microsoft Visual FoxPro Driver};UID=;;SourceDB=\\Dc01\Server VFP Static and Dynamic\Data\Cymraeg\COMP_C.DBC;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machi" _
), Array("ne;Null=Yes;Deleted=Yes;"))
I have got the debugging code to work by using the following:
Sub CheckWBC()
'used to check the output for debuging only.
Dim conConnect As WorkbookConnection
On Error Resume Next ' ""
For Each conConnect In ThisWorkbook.Connections
With conConnect
Debug.Print .Name
Debug.Print .ODBCConnection.RefreshDate
End With
Next conConnect
On Error GoTo 0
End Sub
We get an output of:
26/11/2019 13:58:20
Connection489
26/11/2019 13:58:20
Connection49
Connection490
26/11/2019 13:58:21
Connection491
26/11/2019 13:58:21
Connection492
26/11/2019 13:58:22
Connection493
26/11/2019 13:58:22
Connection494
26/11/2019 13:58:23
Connection495
26/11/2019 13:58:23
Connection496
26/11/2019 13:58:23
Connection497
26/11/2019 13:58:24
Connection498
26/11/2019 13:58:24
Connection499
26/11/2019 13:58:24
Connection5
Connection50
Connection500
26/11/2019 13:58:24
Connection501
26/11/2019 13:58:25
Connection502
26/11/2019 13:58:26
Connection503
26/11/2019 13:58:26
Connection504
26/11/2019 13:58:27
Connection505
26/11/2019 13:58:27
Connection506
26/11/2019 13:58:27
Connection507
26/11/2019 13:58:27
Connection508
26/11/2019 13:58:28
Connection509
26/11/2019 13:58:28
Connection51
Connection510
26/11/2019 13:58:28
Connection511
26/11/2019 13:58:29
Connection512
26/11/2019 13:58:30
Connection513
26/11/2019 13:58:30
Connection514
26/11/2019 13:58:30
Connection515
26/11/2019 13:58:31
Connection516
26/11/2019 13:58:31
Connection517
26/11/2019 13:58:31
Connection518
26/11/2019 13:58:31
Connection519
26/11/2019 13:58:32
Connection52
Connection520
26/11/2019 13:58:32
Connection521
26/11/2019 13:58:32
Connection522
26/11/2019 13:58:33
Connection523
26/11/2019 13:58:34
Connection524
26/11/2019 13:58:34
Connection525
26/11/2019 13:58:34
Connection526
26/11/2019 13:58:35
Connection527
26/11/2019 13:58:35
Connection528
26/11/2019 13:58:35
Connection529
26/11/2019 13:58:35
Connection53
Connection530
26/11/2019 13:58:36
Connection531
26/11/2019 13:58:36
Connection532
26/11/2019 13:58:37
Connection533
26/11/2019 13:58:37
Connection534
26/11/2019 13:58:38
Connection535
26/11/2019 13:58:38
Connection536
26/11/2019 13:58:38
Connection537
26/11/2019 13:58:39
Connection538
26/11/2019 13:58:39
Connection539
26/11/2019 13:58:39
Connection54
Connection540
26/11/2019 13:58:39
Connection541
26/11/2019 13:58:40
Connection542
26/11/2019 13:58:41
Connection543
26/11/2019 13:58:41
Connection544
26/11/2019 13:58:41
Connection545
26/11/2019 13:58:42
Connection546
26/11/2019 13:58:42
Connection547
26/11/2019 13:58:42
Connection548
26/11/2019 13:58:42
Connection549
26/11/2019 13:58:43
Connection55
Connection550
26/11/2019 13:58:43
Connection551
26/11/2019 13:58:43
Connection552
26/11/2019 13:58:44
Connection553
26/11/2019 13:58:44
Connection554
26/11/2019 13:58:44
Connection555
26/11/2019 13:58:45
Connection556
26/11/2019 13:58:45
Connection557
26/11/2019 13:58:45
Connection558
26/11/2019 13:58:45
Connection559
26/11/2019 13:58:46
Connection56
Connection57
Connection58
Connection59
Connection6
Connection60
Connection61
Connection62
Connection63
Connection64
Connection65
Connection66
Connection67
Connection68
Connection69
Connection7
Connection70
Connection71
Connection72
Connection73
Connection74
Connection75
Connection76
Connection77
Connection78
Connection79
Connection8
Connection80
Connection81
Connection82
Connection83
Connection84
Connection85
Connection86
Connection87
Connection88
Connection89
Connection9
Connection90
Connection91
Connection92
Connection93
Connection94
Connection95
Connection96
Connection97
Connection98
Connection99