Visible data in Power Query, not refreshing to Excel table

Raddle

New Member
Joined
Oct 24, 2023
Messages
37
Office Version
  1. 2016
Can anyone help with this headache .. ?

I have a Power Query which is collating from a folder and then a VBA routine coverts the connection to table per below.
However if I make a change to a source file, the data refreshes in the PQ but then does not in turn refresh to the Excel table.
To troubleshoot, I created the query from scratch and loaded it to a table manually.
In this scenario, a change to source data DOES reflect in the PQ and then refresh to the Excel table.

So can anyone see what might be wrong with this bit of VBA?

Lastly , if I record a macro of the manually created PQ there does appear to be a load of additional information after the loading of the data to the table but I can't diagnose this and am not sure if this is related to the issue.

Help very my appreciated.

Sub LoopToCreate() ' LoopToCreateSheetsAndTablesFromQueries
'declare a variant array

Dim ws As Worksheet
Dim StartTime As Double
Dim MinutesElapsed As String


'populate the array
On Error Resume Next

Qnames = Array("PrePayments2")

'loop through the entire array
For Each item In Qnames 'strNames
Sheets.Add After:=ActiveSheet
ActiveSheet.name = item
Range("H1") = item ' Put a title on the page
Range("H1").Select

With Selection.Font
.name = "Calibri"
.Size = 22
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Selection.Font.Underline = xlUnderlineStyleSingle

ActiveSheet.Tab.ColorIndex = 9 '3=Red , 4=green,5=blue,6=yellow,etc...

' Create the tables from the list of queries

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & item & ";Extended Properties=""""" _
, Destination:=Range("$b$5")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM " & item & "") ' this is where you need to concatenate the array value name
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = item ' this doesn't need concatenating
.Refresh BackgroundQuery:=False
.TableStyle = "TableStyleMedium10"
.AutoFilter = False
.ShowTotals = True



End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=True

Next item

End Sub
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Beats me why all this trouble to load Power Query data in a table with VBA. It's a default of Power Query.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

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
Back
Top