Change Data Source of Power Query using VB Script

Question

The following vba code works when line 4 replaced with

... Source = Json.Document(Web.Contents(""https://pidynaapst02.com/e/78df7c69-3512-4946-8d3c-f1c0e451fe80/api/v1/problem/feed?relativeTime=month""))," & Chr(13) & ...

However, in order to do it automatically, I want to replace url by a variable, as shown below:

... Source = Json.Document(Web.Contents(""" & VA & """))," & Chr(13) & ...

This is the complete vb code:

`                                                                                                 
Sub Macro2()                                                                                      
VA = Sheets(1).Range("A1").Value
ActiveWorkbook.Queries.Add Name:= _
    "Table 0", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Json.Document(Web.Contents(""" & VA & """)," & Chr(13) & "" & Chr(10) & "    result = Source[result]," & Chr(13) & "" & Chr(10) & "    problems = result[problems]," & Chr(13) & "" & Chr(10) & "    #""Converted to Table"" = Table.FromList(problems, Splitter.SplitByNothing(), null, null, ExtraValues." & _
    "Error)," & Chr(13) & "" & Chr(10) & "    #""Expanded Column1"" = Table.ExpandRecordColumn(#""Converted to Table"", ""Column1"", {""id"", ""startTime"", ""endTime"", ""displayName"", ""impactLevel"", ""status"", ""severityLevel"", ""commentCount"", ""tagsOfAffectedEntities"", ""rankedImpacts"", ""affectedCounts"", ""recoveredCounts"", ""hasRootCause""}, {""Column1.id"", ""Column1.startTime"", " & _
    """Column1.endTime"", ""Column1.displayName"", ""Column1.impactLevel"", ""Column1.status"", ""Column1.severityLevel"", ""Column1.commentCount"", ""Column1.tagsOfAffectedEntities"", ""Column1.rankedImpacts"", ""Column1.affectedCounts"", ""Column1.recoveredCounts"", ""Column1.hasRootCause""})," & Chr(13) & "" & Chr(10) & "    #""Expanded Column1.tagsOfAffectedEntities"" = Table.ExpandListColumn(#" & _
    """Expanded Column1"", ""Column1.tagsOfAffectedEntities"")," & Chr(13) & "" & Chr(10) & "    #""Expanded Column1.tagsOfAffectedEntities1"" = Table.ExpandRecordColumn(#""Expanded Column1.tagsOfAffectedEntities"", ""Column1.tagsOfAffectedEntities"", {""context"", ""key"", ""value""}, {""Column1.tagsOfAffectedEntities.context"", ""Column1.tagsOfAffectedEntities.key"", ""Column1.tagsOfAffectedEntit" & _
    "ies.value""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Expanded Column1.tagsOfAffectedEntities1"""Sheets.Add After:=ActiveSheet
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0""" _
    , "ended Properties="""""), Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array( _
    "SELECT * FROM [Table 0]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = False
    .ListObject.DisplayName = "Table_0"
    .Refresh BackgroundQuery:=False
End WithSelection.ListObject.QueryTable.Refresh BackgroundQuery:=False                            
End Sub`

It is giving error "Intialization of the data source failed. Check the database server or contact you database administrator. Make sure the external database is available, and then try the operation again. If you see this message again, create a new data source to connect to the database."

Hopefully somebody could help.

chillin chillin · Accepted Answer · 2019-09-19T17:49:53

I think you're missing a closing ) in the first line of your M code. The Advanced Editor shows an error:

I can't test if that fixes your issue or not. However, I would recommend not manipulating your query's formula in VBA (as you can't be sure the result is syntactically valid in M).

Based on your VBA code, your query is in the same workbook as the ranges/cells being used to make the query's formula "dynamic". If you turn those ranges/cells (e.g. cell A1) into named ranges, you can then use Excel.CurrentWorkbook in Power Query to read the values in those named ranges.

This means you don't need to push those cells' values to Power Query. Instead, Power Query can read them for itself -- and your query is still "dynamic".

Minor detail. Your M code contains Table.FromList, followed by Table.ExpandRecordColumn. It's possible both of those steps could be replaced by Table.FromRecords.

Change Data Source of Power Query using VB Script

1 Answers