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