VBA - Create Power Query and save in sheet

mjacquot82

New Member
Joined
Jan 2, 2018
Messages
34
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
 

Attachments

  • Links by City.PNG
    Links by City.PNG
    16.8 KB · Views: 18
  • Summer Loading Eval.PNG
    Summer Loading Eval.PNG
    40.2 KB · Views: 18
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?

VBA Code:
            '.Refresh BackgroundQuery:=False
That line is deliberately commented out (so it doesn't execute).

This line does the refresh at the end:
VBA Code:
    cityTable.QueryTable.Refresh BackgroundQuery:=False
so that the data is refreshed for a new city sheet and an existing city sheet.
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
This line?

VBA Code:
            '.Refresh BackgroundQuery:=False
That line is deliberately commented out (so it doesn't execute).

This line does the refresh at the end:
VBA Code:
    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 ?
 

Attachments

  • 1684521358303.png
    1684521358303.png
    6.7 KB · Views: 3
Upvote 0
There is a way to ignore missing columns/fields so that the error doesn't occur. Search for "excel power query ignore missing column".
 
Upvote 0
This line?

VBA Code:
            '.Refresh BackgroundQuery:=False
That line is deliberately commented out (so it doesn't execute).

This line does the refresh at the end:
VBA Code:
    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
 
Upvote 0
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:

Power Query:
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.
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
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.

VBA Code:
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.

VBA Code:
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 ?
 
Upvote 0
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:

VBA Code:
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.
 
Upvote 0
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:

VBA Code:
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 ?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top