VBA - Create Power Query and save in sheet

[mjacquot82]

Hi everyone.

I have found some information on what i am trying to achieve buy i am failing at making it work.

I have a sheet " Links by City " in which i have city names and beside them the link to the forecast website i use that is specific for that city. At the moment only have 5 links and will be adding more as time goes on.
I have a sheet " Summer Loading Eval " in which i have my plan for a specific week. This is where i would have a command button. I need to have the command button look at the city names in column D, go to sheet " Links By City" and pull in the information from the website into a separate sheet. If the sheet already exists with that city name then i just want it to refresh the link so get the updated information. If the city name doesn't already have a sheet then it needs to create a sheet and name it.

I would then like in " Summer Loading Eval " columns T,U,V & W to somehow look at the city name, go to the sheet that has that city's name and get the associated forrcast for the time in that is in column M.

Any help would be greatly appreciated.

Thanks in advance.

Marc

 

[John_w]

It creates the sheet and names it Sarnia, but then i get that error and then highlights in yellow the line " .Refresh BackgroundQuery:=False"

This line?

            '.Refresh BackgroundQuery:=False

That line is deliberately commented out (so it doesn't execute).

This line does the refresh at the end:

    cityTable.QueryTable.Refresh BackgroundQuery:=False

so that the data is refreshed for a new city sheet and an existing city sheet.

 

[mjacquot82]

This line?

            '.Refresh BackgroundQuery:=False

That line is deliberately commented out (so it doesn't execute).

This line does the refresh at the end:

    cityTable.QueryTable.Refresh BackgroundQuery:=False

so that the data is refreshed for a new city sheet and an existing city sheet.

For whatever reason it worked this time. But i tried running different cities and got an error. I got the same error earlier about not finding the humidex column. After looking closely to a few city's forecasts i notice that some had the humidex and some didn't. Is there a way to adapt the code so that if it doesn't find a column like humidex that it just disregards it ? That way it could still complete what it's doing even if it can't find a certain column because it might not be on the website ?

 

[John_w]

There is a way to ignore missing columns/fields so that the error doesn't occur. Search for "excel power query ignore missing column".

 

[mjacquot82]

This line?

            '.Refresh BackgroundQuery:=False

That line is deliberately commented out (so it doesn't execute).

This line does the refresh at the end:

    cityTable.QueryTable.Refresh BackgroundQuery:=False

so that the data is refreshed for a new city sheet and an existing city sheet.

" cityTable.QueryTable.Refresh BackgroundQuery:=False " is the line that is being highlighted in yellow (Error: We cannot convert the value 1 to type Text) , when i try to add Kingston Kingston - Hourly Forecast - Environment Canada

 

[John_w]

To expand on my previous answer, in the PQ editor or Advanced Editor type in the "MissingField.Ignore" parameter in each field which could be missing. For example:

let
    Source = Web.Page(Web.Contents("https://weather.gc.ca/forecast/hourly/on-147_metric_e.html")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Date/Time (EDT)", type text}, {"Temp. (°C)", type text}, {"Weather Conditions", type text}, {"Likelihood of precipFootnote †", type text}, {"Wind (km/h)", type text}, {"Humidex", type text, MissingField.Ignore}, {"Column7", type date, MissingField.Ignore}})
in
    #"Changed Type"

I've added a comma and MissingField.Ignore to the "Humidex" and "Column7" fields.

 

[John_w]

Use this code instead of all previous code. It has an improved Power Query, where the "MissingField.Ignore" parameter is applied to the whole web table (in the Table.SelectColumns step) instead of individual fields.

The Test routine/macro now requests multiple cities by looping through all the rows in a table on the "Links by City" sheet - therefore you must convert the range to a table.

I've also added the Delete_City_Sheets routine which loops through the rows in the same table and deletes each city sheet and its associated table and workbook query. This is useful whilst testing and experimenting with PQ.

Option Explicit


Public Sub Test()

    Dim linksTable As ListObject
    Dim r As Long
    
    Set linksTable = Worksheets("Links by City").ListObjects(1)
    With linksTable
        For r = 1 To .DataBodyRange.Rows.Count
            Get_Forecast .DataBodyRange(r, 1).Value, .DataBodyRange(r, 2).Value
        Next
    End With
    
End Sub


'Create or refresh Power Query to get web data forecast for specified city and URL
Private Sub Get_Forecast(city As String, cityForecastURL As String)
    
    Dim wbQueryName As String
    Dim tableName As String
    Dim citySheet As Worksheet
    Dim wbQuery As WorkbookQuery
    Dim cityTable As ListObject
    
    'Define name of workbook query and table linked to the query, based on the city name
    
    wbQueryName = city & "_wbQuery"
    tableName = Replace(city, " ", "_") & "_Table"
        
    'Get sheet for this city and add it if it doesn't exist
    
    Set citySheet = Nothing
    On Error Resume Next
    Set citySheet = ThisWorkbook.Worksheets(city)
    On Error GoTo 0
    If citySheet Is Nothing Then
        With ThisWorkbook
            Set citySheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
            citySheet.Name = city
        End With
    End If
    
    'Get workbook query for this city and add it if it doesn't exist. The query reads data from the city's forecast web page
    
    Set wbQuery = Nothing
    On Error Resume Next
    Set wbQuery = ThisWorkbook.Queries(wbQueryName)
    On Error GoTo 0
    If wbQuery Is Nothing Then
        Set wbQuery = ThisWorkbook.Queries.Add(Name:=wbQueryName, Formula:= _
            "let" & vbCrLf & _
            "    Source = Web.Page(Web.Contents(""" & cityForecastURL & """))," & vbCrLf & _
            "    Data0 = Source{0}[Data]," & vbCrLf & _
            "    #""Removed Other Columns"" = Table.SelectColumns(Data0,{""Date/Time (EDT)"", ""Temp. (°C)"", ""Weather Conditions"", ""Likelihood of precipFootnote †"", ""Wind (km/h)"", ""Column6"", ""Column7""},MissingField.Ignore)" & vbCrLf & _
            "in" & vbCrLf & _
            "    #""Removed Other Columns""")
    End If
    
    'Get query's table for this city and add it if it doesn't exist.  The table starts at cell A1 on the city's sheet
    
    Set cityTable = Nothing
    On Error Resume Next
    Set cityTable = citySheet.ListObjects(tableName)
    On Error GoTo 0
    If cityTable Is Nothing Then
        With citySheet
            Set cityTable = .ListObjects.Add(SourceType:=0, _
                                    Source:=Array("OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""" & wbQueryName & """;Extended Properties="""""), _
                                    Destination:=.Range("A1"))
            cityTable.Name = tableName
        End With
        
        'Define this city table's query table to SELECT from the city's workbook query
        
        With cityTable.QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [" & wbQueryName & "]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = tableName
        End With
    
    End If
    
    'Refresh this city's query table to request the forecast data
    
    cityTable.QueryTable.Refresh BackgroundQuery:=False
    
End Sub


Public Sub Delete_City_Sheets()

    Dim linksTable As ListObject
    Dim r As Long
    
    Set linksTable = ThisWorkbook.Worksheets("Links by City").ListObjects(1)
    With linksTable
        For r = 1 To .DataBodyRange.Rows.Count
            On Error Resume Next
            With ThisWorkbook.Worksheets(.DataBodyRange(r, 1).Value)
                If .ListObjects.Count > 0 Then .ListObjects(1).Delete
                Application.DisplayAlerts = False
                .Delete
                Application.DisplayAlerts = True
            End With
            ThisWorkbook.Queries(.DataBodyRange(r, 1).Value & "_wbQuery").Delete
            On Error GoTo 0
        Next
    End With
    
End Sub

 

[mjacquot82]

Use this code instead of all previous code. It has an improved Power Query, where the "MissingField.Ignore" parameter is applied to the whole web table (in the Table.SelectColumns step) instead of individual fields.

The Test routine/macro now requests multiple cities by looping through all the rows in a table on the "Links by City" sheet - therefore you must convert the range to a table.

I've also added the Delete_City_Sheets routine which loops through the rows in the same table and deletes each city sheet and its associated table and workbook query. This is useful whilst testing and experimenting with PQ.

Option Explicit


Public Sub Test()

    Dim linksTable As ListObject
    Dim r As Long
  
    Set linksTable = Worksheets("Links by City").ListObjects(1)
    With linksTable
        For r = 1 To .DataBodyRange.Rows.Count
            Get_Forecast .DataBodyRange(r, 1).Value, .DataBodyRange(r, 2).Value
        Next
    End With
  
End Sub


'Create or refresh Power Query to get web data forecast for specified city and URL
Private Sub Get_Forecast(city As String, cityForecastURL As String)
  
    Dim wbQueryName As String
    Dim tableName As String
    Dim citySheet As Worksheet
    Dim wbQuery As WorkbookQuery
    Dim cityTable As ListObject
  
    'Define name of workbook query and table linked to the query, based on the city name
  
    wbQueryName = city & "_wbQuery"
    tableName = Replace(city, " ", "_") & "_Table"
      
    'Get sheet for this city and add it if it doesn't exist
  
    Set citySheet = Nothing
    On Error Resume Next
    Set citySheet = ThisWorkbook.Worksheets(city)
    On Error GoTo 0
    If citySheet Is Nothing Then
        With ThisWorkbook
            Set citySheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
            citySheet.Name = city
        End With
    End If
  
    'Get workbook query for this city and add it if it doesn't exist. The query reads data from the city's forecast web page
  
    Set wbQuery = Nothing
    On Error Resume Next
    Set wbQuery = ThisWorkbook.Queries(wbQueryName)
    On Error GoTo 0
    If wbQuery Is Nothing Then
        Set wbQuery = ThisWorkbook.Queries.Add(Name:=wbQueryName, Formula:= _
            "let" & vbCrLf & _
            "    Source = Web.Page(Web.Contents(""" & cityForecastURL & """))," & vbCrLf & _
            "    Data0 = Source{0}[Data]," & vbCrLf & _
            "    #""Removed Other Columns"" = Table.SelectColumns(Data0,{""Date/Time (EDT)"", ""Temp. (°C)"", ""Weather Conditions"", ""Likelihood of precipFootnote †"", ""Wind (km/h)"", ""Column6"", ""Column7""},MissingField.Ignore)" & vbCrLf & _
            "in" & vbCrLf & _
            "    #""Removed Other Columns""")
    End If
  
    'Get query's table for this city and add it if it doesn't exist.  The table starts at cell A1 on the city's sheet
  
    Set cityTable = Nothing
    On Error Resume Next
    Set cityTable = citySheet.ListObjects(tableName)
    On Error GoTo 0
    If cityTable Is Nothing Then
        With citySheet
            Set cityTable = .ListObjects.Add(SourceType:=0, _
                                    Source:=Array("OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""" & wbQueryName & """;Extended Properties="""""), _
                                    Destination:=.Range("A1"))
            cityTable.Name = tableName
        End With
      
        'Define this city table's query table to SELECT from the city's workbook query
      
        With cityTable.QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [" & wbQueryName & "]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = tableName
        End With
  
    End If
  
    'Refresh this city's query table to request the forecast data
  
    cityTable.QueryTable.Refresh BackgroundQuery:=False
  
End Sub


Public Sub Delete_City_Sheets()

    Dim linksTable As ListObject
    Dim r As Long
  
    Set linksTable = ThisWorkbook.Worksheets("Links by City").ListObjects(1)
    With linksTable
        For r = 1 To .DataBodyRange.Rows.Count
            On Error Resume Next
            With ThisWorkbook.Worksheets(.DataBodyRange(r, 1).Value)
                If .ListObjects.Count > 0 Then .ListObjects(1).Delete
                Application.DisplayAlerts = False
                .Delete
                Application.DisplayAlerts = True
            End With
            ThisWorkbook.Queries(.DataBodyRange(r, 1).Value & "_wbQuery").Delete
            On Error GoTo 0
        Next
    End With
  
End Sub

Use this code instead of all previous code. It has an improved Power Query, where the "MissingField.Ignore" parameter is applied to the whole web table (in the Table.SelectColumns step) instead of individual fields.

The Test routine/macro now requests multiple cities by looping through all the rows in a table on the "Links by City" sheet - therefore you must convert the range to a table.

I've also added the Delete_City_Sheets routine which loops through the rows in the same table and deletes each city sheet and its associated table and workbook query. This is useful whilst testing and experimenting with PQ.

Option Explicit


Public Sub Test()

    Dim linksTable As ListObject
    Dim r As Long
   
    Set linksTable = Worksheets("Links by City").ListObjects(1)
    With linksTable
        For r = 1 To .DataBodyRange.Rows.Count
            Get_Forecast .DataBodyRange(r, 1).Value, .DataBodyRange(r, 2).Value
        Next
    End With
   
End Sub


'Create or refresh Power Query to get web data forecast for specified city and URL
Private Sub Get_Forecast(city As String, cityForecastURL As String)
   
    Dim wbQueryName As String
    Dim tableName As String
    Dim citySheet As Worksheet
    Dim wbQuery As WorkbookQuery
    Dim cityTable As ListObject
   
    'Define name of workbook query and table linked to the query, based on the city name
   
    wbQueryName = city & "_wbQuery"
    tableName = Replace(city, " ", "_") & "_Table"
       
    'Get sheet for this city and add it if it doesn't exist
   
    Set citySheet = Nothing
    On Error Resume Next
    Set citySheet = ThisWorkbook.Worksheets(city)
    On Error GoTo 0
    If citySheet Is Nothing Then
        With ThisWorkbook
            Set citySheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
            citySheet.Name = city
        End With
    End If
   
    'Get workbook query for this city and add it if it doesn't exist. The query reads data from the city's forecast web page
   
    Set wbQuery = Nothing
    On Error Resume Next
    Set wbQuery = ThisWorkbook.Queries(wbQueryName)
    On Error GoTo 0
    If wbQuery Is Nothing Then
        Set wbQuery = ThisWorkbook.Queries.Add(Name:=wbQueryName, Formula:= _
            "let" & vbCrLf & _
            "    Source = Web.Page(Web.Contents(""" & cityForecastURL & """))," & vbCrLf & _
            "    Data0 = Source{0}[Data]," & vbCrLf & _
            "    #""Removed Other Columns"" = Table.SelectColumns(Data0,{""Date/Time (EDT)"", ""Temp. (°C)"", ""Weather Conditions"", ""Likelihood of precipFootnote †"", ""Wind (km/h)"", ""Column6"", ""Column7""},MissingField.Ignore)" & vbCrLf & _
            "in" & vbCrLf & _
            "    #""Removed Other Columns""")
    End If
   
    'Get query's table for this city and add it if it doesn't exist.  The table starts at cell A1 on the city's sheet
   
    Set cityTable = Nothing
    On Error Resume Next
    Set cityTable = citySheet.ListObjects(tableName)
    On Error GoTo 0
    If cityTable Is Nothing Then
        With citySheet
            Set cityTable = .ListObjects.Add(SourceType:=0, _
                                    Source:=Array("OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""" & wbQueryName & """;Extended Properties="""""), _
                                    Destination:=.Range("A1"))
            cityTable.Name = tableName
        End With
       
        'Define this city table's query table to SELECT from the city's workbook query
       
        With cityTable.QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [" & wbQueryName & "]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = tableName
        End With
   
    End If
   
    'Refresh this city's query table to request the forecast data
   
    cityTable.QueryTable.Refresh BackgroundQuery:=False
   
End Sub


Public Sub Delete_City_Sheets()

    Dim linksTable As ListObject
    Dim r As Long
   
    Set linksTable = ThisWorkbook.Worksheets("Links by City").ListObjects(1)
    With linksTable
        For r = 1 To .DataBodyRange.Rows.Count
            On Error Resume Next
            With ThisWorkbook.Worksheets(.DataBodyRange(r, 1).Value)
                If .ListObjects.Count > 0 Then .ListObjects(1).Delete
                Application.DisplayAlerts = False
                .Delete
                Application.DisplayAlerts = True
            End With
            ThisWorkbook.Queries(.DataBodyRange(r, 1).Value & "_wbQuery").Delete
            On Error GoTo 0
        Next
    End With
   
End Sub

I apologize for my ignorance. I just want to be clear as to how to properly use your code. Am i to create a command button, then double click on it to open design mode, the paste this code between " Private Sub CommandButton1_Click() and End Sub ?

On a different subject, my work has agreed to cover the cost of my taking online courses to understand this type of programing. Would the knowledge you have used here be acquired through excel courses or something completely different ?

 

[John_w]

I apologize for my ignorance. I just want to be clear as to how to properly use your code. Am i to create a command button, then double click on it to open design mode, the paste this code between " Private Sub CommandButton1_Click() and End Sub ?

Private Sub CommandButton1_Click() indicates you've added an ActiveX command button on the sheet, as opposed to a Form Control button, and your code is in the sheet's module.

Paste my code into a new standard module. To run my code when you click the command button, change your code to this:

Private Sub CommandButton1_Click()
    Test
End Sub

so that the Test routine is called when you click the command button. I would change the name "Test" to something more explanatory like "Refresh_All_City_Forecasts", and change your CommandButton1_Click accordingly.

On a different subject, my work has agreed to cover the cost of my taking online courses to understand this type of programing. Would the knowledge you have used here be acquired through excel courses or something completely different ?

Yes, Excel courses would be useful, to understand some of the Excel objects I've used (tables, workbook queries, ranges, etc), and also to understand basic programming concepts such as data types (String, Long, Range, etc.), data structures (arrays, Collection), loops (For ... Next, Do While ... Loop, etc.) decisions (If ... Then, Select Case), subroutines, functions, arguments, etc. I find it better to learn by using the macro recorder and modifying the code generated.

For your project, I think a good understanding of Power Query is needed, because the web data isn't in a 'clean' layout, i.e. row 2 and another row contain the full date, and the "Date/Time" column contains either the full date or the time. You would need knowledge of PQ and its M formula language to manipulate these rows and this column to create a combined date-time column.

 

[mjacquot82]

Private Sub CommandButton1_Click() indicates you've added an ActiveX command button on the sheet, as opposed to a Form Control button, and your code is in the sheet's module.

Paste my code into a new standard module. To run my code when you click the command button, change your code to this:

Private Sub CommandButton1_Click()
    Test
End Sub

so that the Test routine is called when you click the command button. I would change the name "Test" to something more explanatory like "Refresh_All_City_Forecasts", and change your CommandButton1_Click accordingly.


Yes, Excel courses would be useful, to understand some of the Excel objects I've used (tables, workbook queries, ranges, etc), and also to understand basic programming concepts such as data types (String, Long, Range, etc.), data structures (arrays, Collection), loops (For ... Next, Do While ... Loop, etc.) decisions (If ... Then, Select Case), subroutines, functions, arguments, etc. I find it better to learn by using the macro recorder and modifying the code generated.

For your project, I think a good understanding of Power Query is needed, because the web data isn't in a 'clean' layout, i.e. row 2 and another row contain the full date, and the "Date/Time" column contains either the full date or the time. You would need knowledge of PQ and its M formula language to manipulate these rows and this column to create a combined date-time column.

thank you, its funny you mention those rows lol because i was looking at them wondering how to manipulate them lol but i can definitely work with the code you provided. You say that excel courses would be helpful. But is there something specific you would recommend?

Do you think its possible to have the forecaste pasted differently. I find that as the day goes on, the newly pasted forecast is removing earlier times. I was wondering if it was at all possible to have the pasted range change. For example the first time the button is clicked it pastes the info in A1, clicked again then it goes A2, then A3 and so on. I have it set to refresh every hour, and the forecast is hourly. So in theory the info in the previous cell would still be relevant and present in the sheet ?

 

[John_w]

thank you, its funny you mention those rows lol because i was looking at them wondering how to manipulate them lol but i can definitely work with the code you provided. You say that excel courses would be helpful. But is there something specific you would recommend?

The dates and times could be manipulated using VBA, rather than M formula language.

Sorry, I can't recommend any Excel courses because I've never done one - everything I know is self-taught.

Do you think its possible to have the forecaste pasted differently. I find that as the day goes on, the newly pasted forecast is removing earlier times. I was wondering if it was at all possible to have the pasted range change. For example the first time the button is clicked it pastes the info in A1, clicked again then it goes A2, then A3 and so on. I have it set to refresh every hour, and the forecast is hourly. So in theory the info in the previous cell would still be relevant and present in the sheet ?

You can't change the destination cell (actually the table's QueryTable.Destination property) of an existing table. Instead, you have to delete the table and add a new table in the new location.