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

 

[mjacquot82]

I have worked on it some more and i am now able to create more than one query and have a new sheet with the cell value i want for that query. Problem now is that when that sheet name already exists nothing works. I would want it so that if the sheet name already exists to refresh the query associated to that sheet. That i really have no clue how to do.

Sub Macro14()
'
' Macro14 Macro
'
Dim Qus As WorkbookQuery

For Each Qus In ActiveWorkbook.Queries
Qus.Delete
Next

Application.CutCopyMode = False
Selection.Copy
ActiveWorkbook.Queries.Add Name:="Table 0 (2)", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.Page(Web.Contents(""Toronto - Hourly Forecast - Environment Canada""))," & Chr(13) & "" & Chr(10) & " Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & " #""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 t" & _
"ext}, {""Column6"", type date}, {""Column7"", type date}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
ActiveWorkbook.Worksheets.Add.Name = Range("D25").Value
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0 (2)"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Table 0 (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 = "Table_0__2"
.Refresh BackgroundQuery:=False
End With
End Sub

 

[John_w]

Your macro doesn't work for me. The error is "The column 'Column6' of the table wasn't found". Nethertheless, I've modified your macro to make it general for any specified city and its hourly URL.

Here is your macro which I've changed to a subroutine which you would call from another routine with a specific city name and hourly URL. There are a few comments to explain the steps.

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 & _
            " #""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}, {""Column6"", type date}, {""Column7"", type date}})" & vbCrLf & _
            "in" & vbCrLf & _
            " #""Changed Type""")
    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

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.

Call the above Get_Forecast subroutine from another procedure, like this to request the forecast for Toronto.

Public Sub Test()
    With Worksheets("Links by City")
        Get_Forecast .Range("A1").Value, "https://weather.gc.ca/forecast/hourly/on-143_metric_e.html" '.Range("B1").Value
    End With
End Sub

Although I note that your "Links by City" sheet column B doesn't contain the hourly URL for the cities, so I've hard-coded the hourly URL for Toronto, instead of reading the URL from the column B cell.

The first time you run the Test routine it creates the Power Query and associated table for Toronto and gets the data from the web page for Toronto. For subsequent runs it refreshes the existing table to get the latest data for Toronto.

 

[mjacquot82]

I truly appreciate your time and effort thank you . But my lack of understanding has me wondering stumped. I don't know if t his is what i was suppose to do with the code you gave me. But i created a button and assigned this code to it. When i press the button i get the following error " Run-time error "1004": Method 'Name" of object'_worksheet" failed". Followed by this line being highlighted in yellow " citySheet.name=city".

Am i understanding this correctly. Your code makes is create/or refresh "depending on if there is already a sheet assigned to a city name " all the cities/links in the page named "Links by City "?

 

[mjacquot82]

I truly appreciate your time and effort thank you . But my lack of understanding has me wondering stumped. I don't know if t his is what i was suppose to do with the code you gave me. But i created a button and assigned this code to it. When i press the button i get the following error " Run-time error "1004": Method 'Name" of object'_worksheet" failed". Followed by this line being highlighted in yellow " citySheet.name=city".

Am i understanding this correctly. Your code makes is create/or refresh "depending on if there is already a sheet assigned to a city name " all the cities/links in the page named "Links by City "?

in regards to your comment about not column B not containing the hourly URL for for the cities, at the time i had just found the link for the hourly information. I have changed/and will be only adding those links to the sheet. Currently i have updated that sheet that it only contains the links to the hourly forecast. Not sure if that makes a difference or not.

 

[John_w]

I don't know if t his is what i was suppose to do with the code you gave me. But i created a button and assigned this code to it. When i press the button i get the following error " Run-time error "1004": Method 'Name" of object'_worksheet" failed". Followed by this line being highlighted in yellow " citySheet.name=city".

What is the name of the city in A1 of the "Links by City" sheet? You could get that error if it contains characters that aren't valid for a sheet name.

Am i understanding this correctly. Your code makes is create/or refresh "depending on if there is already a sheet assigned to a city name " all the cities/links in the page named "Links by City "?

Yes that's correct.

in regards to your comment about not column B not containing the hourly URL for for the cities, at the time i had just found the link for the hourly information. I have changed/and will be only adding those links to the sheet. Currently i have updated that sheet that it only contains the links to the hourly forecast. Not sure if that makes a difference or not.

The city summary and hourly URLs obviously determine which forecast web page is used and therefore what data Power Query reads into Excel, via your Transform Data steps. As you've recorded a macro with these steps and posted the code I can use it in my code and make it general for any city and URL. I also recorded the Power Query steps myself to get data from Toronto hourly URL.

Please start again with a new empty workbook.

Add a sheet named "Links by City" and copy the following data to it:

Power Query weather.ga.ca city forecasts.xlsm
	A	B
1	City	Hourly Forecast URL
2	Toronto	https://weather.gc.ca/forecast/hourly/on-143_metric_e.html
3
Links by City

Note that column headings are in row 1 and Toronto is in A2 and its hourly forecast URL in B2.

In the VBA editor, insert a standard module and paste in the following code:

Public Sub Test()
    With Worksheets("Links by City")
        Get_Forecast .Range("A2").Value, .Range("B2").Value
    End With
End Sub


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 & _
            "    #""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}, {""Column7"", type date}})" & vbCrLf & _
            "in" & vbCrLf & _
            "    #""Changed Type""")
    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
            '.Refresh BackgroundQuery:=False
        End With
    
    End If
    
    'Refresh this city's query table to request the forecast data
    
    cityTable.QueryTable.Refresh BackgroundQuery:=False
    
End Sub

Run the Test macro directly from the VBA editor, or assign the Test macro to a button on the sheet.

When run for the first time, the macro should create a sheet named "Toronto" and import the hourly forecast data from the URL via Power Query into a table on the same sheet. Run it again and it should requery the web page and update the table with the latest data. That's all the code does, for just one city. It is easy to modify the code to request multiple cities, although I would convert the range on "Links by City" to a table first because it's generally easier and better for code to read an Excel table rather than a 'raw' data range.

 

[mjacquot82]

Before trying to create a table to get more city's forecasts. I tried getting the forecast for Sarnia "Sarnia - Hourly Forecast - Environment Canada" and got the following error " Invalid URI:I The hostname could not be parsed". I think that maybe this won't work as well as i had hoped. Might be too many variables in the URL's ? Do you get this error ?

 

[mjacquot82]

Before trying to create a table to get more city's forecasts. I tried getting the forecast for Sarnia "Sarnia - Hourly Forecast - Environment Canada" and got the following error " Invalid URI:I The hostname could not be parsed". I think that maybe this won't work as well as i had hoped. Might be too many variables in the URL's ? Do you get this error ?

sorry i didn't notice that the URL link i pasted ended up changing that way.

Sarnia - Hourly Forecast - Environment Canada

Detailed forecast for the next 24 hours – temperature, weather conditions, likelihood of precipitation and winds

weather.gc.ca

 

[John_w]

Before trying to create a table to get more city's forecasts. I tried getting the forecast for Sarnia "Sarnia - Hourly Forecast - Environment Canada" and got the following error " Invalid URI:I The hostname could not be parsed". I think that maybe this won't work as well as i had hoped. Might be too many variables in the URL's ? Do you get this error ?

No error - it works for me with URL https://weather.gc.ca/forecast/hourly/on-147_metric_e.html

 

[mjacquot82]

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