Generally, the idea is to avoid manipulating Power Query code directly via VBA (since you cannot be sure the result will be syntactically valid in M).
However, you genuinely seem to want to create a separate new sheet and query each time the user clicks an ID.
I therefore suggest you ignore my previous answer/approach and try the code below. I can't test the code (since I don't have my own credentials for this rejestr.io API) but I think it should work:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.CountLarge <> 1 Then Exit Sub
If Intersect(Target.Parent.Range("ID"), Target) Is Nothing Then Exit Sub
' If there is any additional validation required (e.g. if the ID should be numeric,
' or should satisfy some condition/criteria) then it should be done here
' before proceeding to code below.
Dim idSelected As String
idSelected = Target.Value
Dim targetQuery As WorkbookQuery
Set targetQuery = GetOrCreateQueryFromId(idSelected)
Dim targetSheet As Worksheet
Set targetSheet = ThisWorkbook.Worksheets.Add
Dim targetTable As ListObject
Set targetTable = targetSheet.ListObjects.Add( _
SourceType:=0, _
Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & targetQuery.Name & ";Extended Properties=""""", _
Destination:=targetSheet.Range("$A$1") _
)
With targetTable.QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [" & targetQuery.Name & "]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "_" & targetQuery.Name
.Refresh BackgroundQuery:=False
End With
End Sub
Private Function GetOrCreateQueryFromId(ByVal someId As String) As WorkbookQuery
' Should accept an ID and return the existing WorkbookQuery object.
' If no query for the ID exists, this function should create one (and then
' return the newly created query).
Dim targetQuery As WorkbookQuery
On Error Resume Next
Set targetQuery = ThisWorkbook.Queries(someId)
On Error GoTo 0
Dim queryAlreadyExists As Boolean
queryAlreadyExists = Not (targetQuery Is Nothing)
Dim queryFormula As String
queryFormula = CreateQueryFormulaFromId(someId)
If queryAlreadyExists Then
targetQuery.Formula = queryFormula
Set GetOrCreateQueryFromId = targetQuery
Exit Function
End If
Set GetOrCreateQueryFromId = ThisWorkbook.Queries.Add(Name:=someId, Formula:=queryFormula)
End Function
Private Function CreateQueryFormulaFromId(ByVal someId As String) As String
' Given an ID, should return the Power Query code (code only) required to get data for that ID.
' This function returns the code itself only. It doesn't create the query object.
CreateQueryFormulaFromId = _
"let" & Chr(13) & "" & Chr(10) & _
" Source = Json.Document(Web.Contents(""https://rejestr.io/api/v1/krs/" & someId & "/relations"", [Headers=[Authorization=""x""]]))," & Chr(13) & "" & Chr(10) & _
" #""Converted to Table"" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), " & Chr(13) & "" & Chr(10) & _
" #""Expanded Column1"" = Table.ExpandRecordColumn(#""Converted to Table"", ""Column1"", {""address"", ""business_insert_date"", ""ceo"", ""current_relations_count"", ""data_fetched_at"", ""first_entry_date"", ""historical_relations_count"", ""id"", ""is_opp"", ""is_removed"", ""krs"", ""last_entry_date"", ""last_entry_no"", ""last_state_entry_date"", ""last_state_entry_no"", ""legal_form"", ""name"", ""name_short"", ""nip"", ""regon"", ""type"", ""w_likwidacji"", ""w_upadlosci"", ""w_zawieszeniu"", ""relations"", ""birthday"", ""first_name"", ""krs_person_id"", ""last_name"", ""organizations_count"", ""second_names"", ""sex""}, " & _
"{""Column1.address"", ""Column1.business_insert_date"", ""Column1.ceo"", ""Column1.current_relations_count"", ""Column1.data_fetched_at"", ""Column1.first_entry_date"", ""Column1.historical_relations_count"", ""Column1.id"", ""Column1.is_opp"", ""Column1.is_removed"", ""Column1.krs"", ""Column1.last_entry_date"", ""Column1.last_entry_no"", ""Column1.last_state_entry_date"", ""Column1.last_state_entry_no"", ""Column1.legal_form"", ""Column1.name"", ""Column1.name_short"", ""Column1.nip"", ""Column1.regon"", ""Column1.type"", ""Column1.w_likwidacji"", ""Column1.w_upadlosci"", ""Column1.w_zawieszeniu"", ""Column1.relations"", ""Column1.birthday"", ""Column1.first_name"", ""Column1.krs_person_id"", ""Column1.last_name"", ""Column1.organizations_count"", ""Column1.second_names"", ""Column1.sex""})" & Chr(13) & "" & Chr(10) & _
"in" & Chr(13) & "" & Chr(10) & _
" #""Expanded Column1"""
End Function
- If that is a genuine API key/credential in your question, then you may want to have the server provider revoke/change it (so that nobody can consume this service API using your credentials).
- There is no error handling implemented and currently the user's input is not validated/sanitised in any way.
