Get External Data (long shot question!)

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
611
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Re: Need help with Get External Data (long shot question!)

Just create a string and use year and now.

eg.


Code:
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]"
 
Last edited:
Upvote 0
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!
 
Upvote 0
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.

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

Code:
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
 
Upvote 0
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).
 
Upvote 0
Re: Need help with Get External Data (long shot question!)

What is your Excel version?
 
Upvote 0
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:

PHP:
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,224,584
Messages
6,179,691
Members
452,938
Latest member
babeneker

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