Get External Data (long shot question!)

[leopardhawk]

This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to by 'Get External Data' every year on January 1 at 00:01 local time?

The following URL is currently in use on a worksheet within my workbook:

https://www.taxtips.ca/nrcredits/tax-credits-2019-base.htm

I would like it to change to the following on January 1, 2020 at 00:01 local time:

https://www.taxtips.ca/nrcredits/tax-credits-2020-base.htm

And then do the same thing every year into perpetuity. If possible...

Thanks!

 

[mrshl9898]

Re: Need help with Get External Data (long shot question!)

Just create a string and use year and now.

eg.

Dim yearnum as long
Dim myURL as string


yearnum = YEAR(NOW())
myURL = "[URL="https://www.taxtips.ca/nrcredits/tax-credits-2019-base.htm"]https://www.taxtips.ca/nrcredits/tax-credits-" & yearnum & "-base.htm[/URL]"

 

[leopardhawk]

Re: Need help with Get External Data (long shot question!)

Okay, I kind of expected that if this was possible, it would have to be accomplished using VBA. I have a question about how it will work.

If I add this code to the worksheet in question, how will it be able to find the URL that is embedded, not in the worksheet itself, but in the external connection properties? Do I have to add more code for that?

Thanks!

 

[mrshl9898]

Re: Need help with Get External Data (long shot question!)

Well if it's a new query, maybe this?

I just turned on the macro recorder, added the source and substituted the URL.

Sub Macro1()




Dim yearnum As Long
Dim myURL As String




yearnum = Year(Now())
myURL = "https://www.taxtips.ca/nrcredits/tax-credits-" & yearnum & "-base.htm"




    ActiveWorkbook.Queries.Add Name:="Table 1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(" [B]& myURL & [/B]"))," & Chr(13) & "" & Chr(10) & "    Data1 = Source{1}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data1,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", typ" & _
        "e text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 1"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 1]")
        .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_1"
        .Refresh BackgroundQuery:=False
    End With
    Application.CommandBars("Queries and Connections").Visible = False


End Sub

 

[leopardhawk]

Re: Need help with Get External Data (long shot question!)

Okay, I added your code to the worksheet in question (tax_credits_web_) and then changed the date on my computer to 2020 and then refreshed the external data but it is still going out to the 2019 web page. The 2020 web page does exist now.

 

[John_w]

Re: Need help with Get External Data (long shot question!)

Try this macro. It runs a web query to import the data for the specified year (in B1 on the active sheet, so no need to change your computer's date) starting at A3.

Public Sub Get_Tax_Credits()
    
    Dim destCell As Range
    Dim URL As String
    Dim qt As QueryTable
    
    With ActiveSheet
        URL = "https://www.taxtips.ca/nrcredits/tax-credits-" & .Range("B1").Value & "-base.htm"
        Set destCell = .Range("A3")
        destCell.CurrentRegion.Clear
    End With
    
    With destCell.Parent
        Set qt = .QueryTables.Add(Connection:="URL;" & URL, Destination:=destCell)
        With qt
            .Name = "tax-credits"
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = "4"
            .Refresh BackgroundQuery:=False
        End With
        qt.Delete
    End With
    
End Sub

 

[leopardhawk]

Re: Need help with Get External Data (long shot question!)

Okay, first, thanks to mrshl and John for helping. I added your code John and when I 'refresh' now (without changing the date), it still retrieves the data from https://www.taxtips.ca/nrcredits/tax-credits-2019-base.htm instead of https://www.taxtips.ca/nrcredits/tax-credits-2020-base.htm. Don't I have to change the date on my computer to 2020 to test and see if this is going to work? When I do change the date on my computer, I am now getting a security alert that says "Revocation information for the security certificate for this site is not available. Do you want to proceed?" I am given 'Yes', 'No' and 'View Certificate' buttons to choose from. If I choose 'Yes', it is still going to the 2019 site.

Just so you know, I can manually change the date in the URL to 2020 and it retrieves data from the 2020 web page (without giving me the security alert).

 

[sandy666]

Re: Need help with Get External Data (long shot question!)

What is your Excel version?

 

[John_w]

Re: Need help with Get External Data (long shot question!)

It sounds like you're not using the macro correctly - you shouldn't be 'refreshing', the macro imports the web site table from:

https://www.taxtips.ca/nrcredits/tax-credits-XXXX-base.htm

where XXXX is the number in cell B1, e.g. 2019 or 2020.

Start with a new workbook, paste my code into a standard module, put 2019 in cell B1 and run the macro. Then change B1 to 2020 and run the macro again and it should import a different set of data.

 

[leopardhawk]

Re: Need help with Get External Data (long shot question!)

Okay, I will try that. In the meantime, to answer sandy, I am using Excel 2016.

Thanks!