CODEWITHSUNDEEP
vbaapipowerquery
Alwexistwo
Alwexistwo

Reputation: 37

Copying and Modyfing the Connection with VBA

I'm currently building a solution using VBA to copy my existing query (API query) and change its parameters depending on the values in columns from the original query.

I'm using the p1 variable that contains a looped column values to be used in API URL

The result should be a multiple sheets with one query each, vba code should loop through a certain column in my first query and then pass this values into each new query.

I encountered a problem. I don't know if its regional formatting issue or not, but i cannot really paste the query into Queries.Add method. I was pretty sure i formatted the "" correctly but:

enter image description here

AS you can see editor flashed this part of code in red (in Notepadd++ with VBA formatting its fine)

asdasdasdasdasd

Here is my code:

mFormula =
    "let" & Chr(13) & "" & Chr(10) & " Source = Json.Document(Web.Contents(""https://rejestr.io/api/v1/krs/"" & p1 & ""/relations"", [Headers=[Authorization=""xxxxxxx""]]))," & Chr(13) & "" & Chr(10) &" #""Converted to Table"" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), " & Chr(13) & "" & Chr(10) & " #""Expanded Column1"" = Table.ExpandRecordColumn(#""Con" & _
"verted 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_upadlo" & _
"sci"", ""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_rem" & _
"oved"", ""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) & " #""Renamed Columns3"""

Also, is it entirely possible to dynamically pass multiple variables from first query to create multiple queries to API service? Maybe someone already did something similar?

Many thanks

Alex

Upvotes: 0

Views: 52

Answers (1)

Tomasz Paluch
Tomasz Paluch

Reputation: 357

You need a line continuation marker (_) in the mFormula = line:

mFormula = _

Upvotes: 1

Related Questions