Jul 21 2021 09:07 AM
Hi All,
I'm using an excel which allows user to choose Json file and with help of power query and macro, json is breaking into TABs with rows and columns.
Excel is showing info of file, location and filename and I'd like to add one more parameter UserName which will show who upload particular json file like this ..
Also, when clicking on Refresh json Table button things get start to break json file in desired format .
I want to bring a progress bar with % completion on screen instead of traditional excel message in bar during this time.
I attached my excel file with sample json file here.
Jul 22 2021 06:14 AM
SolutionHi @TheTallman
UserName in table 'Parameters'
For C5 in your table you can use : Application.UserName (Application.UserName property)
As a note only: this is not a parameter but an information. Nowhere in your queries you use something else than the 1st row of table 'Parameters'
Progress bar with % completion on screen
No way to get that information. This would require an ETA - something PQ doesn't provide. And assuming it could compute an ETA for queries where the Source is "under its control" i.e. an Excel table in the current workbook, this would require the server to provide an ETA for queries that fold to i.e. OData, SQL Server and the like
As an alternative you could do the following that will display, on the left side of the status bar, +/- where you are in the process of refreshing your 3 queries:
Sub RefreshJsonQueries()
With Application
.ScreenUpdating = False
Worksheets("Tables").Activate
.StatusBar = "Refreshing query JsonTable..."
With ActiveSheet.ListObjects("JsonTableConnection").QueryTable
.Refresh BackgroundQuery:=False
End With
Worksheets("Attributes").Activate
.StatusBar = "Refreshing query JsonAttributesConnection..."
With ActiveSheet.ListObjects("JsonAttributesConnection").QueryTable
.Refresh BackgroundQuery:=False
End With
Worksheets("SetTypes").Activate
.StatusBar = "Refreshing query JsonSetTypesConnection..."
With ActiveSheet.ListObjects("JsonSetTypesConnection").QueryTable
.Refresh BackgroundQuery:=False
End With
Worksheets("Main").Activate
.StatusBar = ""
.ScreenUpdating = True
End With
End Sub
Or create a UserForm to display the 3 "messages" in the middle of the screen
Jul 22 2021 06:14 AM
SolutionHi @TheTallman
UserName in table 'Parameters'
For C5 in your table you can use : Application.UserName (Application.UserName property)
As a note only: this is not a parameter but an information. Nowhere in your queries you use something else than the 1st row of table 'Parameters'
Progress bar with % completion on screen
No way to get that information. This would require an ETA - something PQ doesn't provide. And assuming it could compute an ETA for queries where the Source is "under its control" i.e. an Excel table in the current workbook, this would require the server to provide an ETA for queries that fold to i.e. OData, SQL Server and the like
As an alternative you could do the following that will display, on the left side of the status bar, +/- where you are in the process of refreshing your 3 queries:
Sub RefreshJsonQueries()
With Application
.ScreenUpdating = False
Worksheets("Tables").Activate
.StatusBar = "Refreshing query JsonTable..."
With ActiveSheet.ListObjects("JsonTableConnection").QueryTable
.Refresh BackgroundQuery:=False
End With
Worksheets("Attributes").Activate
.StatusBar = "Refreshing query JsonAttributesConnection..."
With ActiveSheet.ListObjects("JsonAttributesConnection").QueryTable
.Refresh BackgroundQuery:=False
End With
Worksheets("SetTypes").Activate
.StatusBar = "Refreshing query JsonSetTypesConnection..."
With ActiveSheet.ListObjects("JsonSetTypesConnection").QueryTable
.Refresh BackgroundQuery:=False
End With
Worksheets("Main").Activate
.StatusBar = ""
.ScreenUpdating = True
End With
End Sub
Or create a UserForm to display the 3 "messages" in the middle of the screen