I have established an API question via Power Query and I am just mimic it through VBA. The JSON document that is returned has multiple drill downs that I need; however, I had to create a new connection each time to change the let statements. I think this is where my query is going really slow. Does anyone know how to make this process more efficient?
Code:
WSAPI.Select Columns("A:F").Select
Selection.Delete Shift:=xlToLeft
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveWorkbook.Queries.Add Name:= _
"?number=" & WS.Cells(r + 1, 1).Value & "&enumeration_type=&taxonomy_description=&first_name=&last_name" _
, Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Json.Document(Web.Contents(""https://npiregistry.cms.hhs.gov/api/?number=" & WS.Cells(r + 1, 1).Value & "&enumeration_type=&taxonomy_description=&first_name=&last_name=&organization_name=&address_purpose=&city=&state=&postal_code=&country_code=&limit=&skip=""))," & Chr(13) & "" & Chr(10) & " results = Source[results]," & Chr(13) & "" & Chr(10) & " results1 = results{0}," & Chr(13) & "" & Chr(10) & " taxonomies = results1[taxonomies]," & Chr(13) & "" & Chr(10) & " taxo" & _
"nomies1 = taxonomies{0}," & Chr(13) & "" & Chr(10) & " #""Converted to Table"" = Record.ToTable(taxonomies1)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Converted to Table"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""?number=" & WS.Cells(r + 1, 1).Value & "&enumeration_type=&taxonomy_description=&fi" _
, "rst_name=&last_name"";Extended Properties="""""), Destination:=Range( _
"API!$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT * FROM [?number=" & WS.Cells(r + 1, 1).Value & "&enumeration_type=&taxonomy_description=&first_name=&last_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 = _
"number_" & WS.Cells(r + 1, 1).Value & "_enumeration_type__taxonomy_description__first_name__last_name"
.Refresh BackgroundQuery:=False
End With
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveWorkbook.Queries.Add Name:= _
"?number=" & WS.Cells(r + 1, 1).Value & "&enumeration_type=&taxonomy_description=&first_name=&last_ (2)" _
, Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Json.Document(Web.Contents(""https://npiregistry.cms.hhs.gov/api/?number=" & WS.Cells(r + 1, 1).Value & "&enumeration_type=&taxonomy_description=&first_name=&last_name=&organization_name=&address_purpose=&city=&state=&postal_code=&country_code=&limit=&skip=""))," & Chr(13) & "" & Chr(10) & " results = Source[results]," & Chr(13) & "" & Chr(10) & " results1 = results{0}," & Chr(13) & "" & Chr(10) & " addresses = results1[addresses]," & Chr(13) & "" & Chr(10) & " addres" & _
"ses1 = addresses{0}," & Chr(13) & "" & Chr(10) & " #""Converted to Table"" = Record.ToTable(addresses1)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Converted to Table"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""?number=" & WS.Cells(r + 1, 1).Value & "&enumeration_type=&taxonomy_description=&fi" _
, "rst_name=&last_ (2)"";Extended Properties="""""), Destination:=Range("$C$1" _
)).QueryTable
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT * FROM [?number=" & WS.Cells(r + 1, 1).Value & "&enumeration_type=&taxonomy_description=&first_name=&last_ (2)]" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = _
"number_" & WS.Cells(r + 1, 1).Value & "_enumeration_type__taxonomy_description__first_name__last___2"
.Refresh BackgroundQuery:=False
End With
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveWorkbook.Queries.Add Name:= _
"?number=" & WS.Cells(r + 1, 1).Value & "&enumeration_type=&taxonomy_description=&first_name=&last_ (3)" _
, Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Json.Document(Web.Contents(""https://npiregistry.cms.hhs.gov/api/?number=" & WS.Cells(r + 1, 1).Value & "&enumeration_type=&taxonomy_description=&first_name=&last_name=&organization_name=&address_purpose=&city=&state=&postal_code=&country_code=&limit=&skip=""))," & Chr(13) & "" & Chr(10) & " results = Source[results]," & Chr(13) & "" & Chr(10) & " results1 = results{0}," & Chr(13) & "" & Chr(10) & " basic = results1[basic]," & Chr(13) & "" & Chr(10) & " #""Converted t" & _
"o Table"" = Record.ToTable(basic)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Converted to Table"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""?number=" & WS.Cells(r + 1, 1).Value & "&enumeration_type=&taxonomy_description=&fi" _
, "rst_name=&last_ (3)"";Extended Properties="""""), Destination:=Range("$E$1" _
)).QueryTable
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT * FROM [?number=" & WS.Cells(r + 1, 1).Value & "&enumeration_type=&taxonomy_description=&first_name=&last_ (3)]" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = _
"number_" & WS.Cells(r + 1, 1).Value & "_enumeration_type__taxonomy_description__first_name__last___3"
.Refresh BackgroundQuery:=False
End With
