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!

 

[John_w]

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

Okay, John, when you say "Start with a new workbook and paste this code into a standard module (e.g. Module1):", do you mean 'new worksheet'? I wasn't sure what you meant by that so I copied your code into a module for the worksheet where I want to import the data from the web (it's called tax_credits_web). Then I pasted the second piece of code into a new module on ThisWorkbook, as you said to do and changed 'Sheet1' to 'tax_credits_web' as you also alluded to. I saved the workbook as an 'Excel Macro enabled workbook', closed the file and re-opened. Where will I find this InputBox prompt because I am not seeing it?

I would like to be able to test this now so I will wait to hear from you.

No, I do mean a new workbook, to keep it separate from my first attempt.

The first routine (starting Public Sub Get_Tax_Credits) should be pasted into a new standard module (also called a regular module), e.g. Module1. See https://www.contextures.com/xlvba01.html#videoreg.

The second routine (starting Private Sub Workbook_Open) should be pasted into the ThisWorkbook module. See https://www.contextures.com/xlvba01.html#Workbook.

Save the workbook as a macro-enabled workbook (.xlsm file), close and reopen it. When you reopen it the prompt to enter the simulated current year should appear.

Note - if you change the default name of the sheet in the new workbook ("Sheet1") then you must change this line in the ThisWorkbook module to the same name:

Worksheets("Sheet1").Activate

 

[sandy666]

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

did you try post #19 ?
it's maintenance-free except re-define refresh time if necessary

 

[leopardhawk]

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

Okay, I did as you suggested and tested both 2019 and 2020. They both worked perfectly. Is there a way that I can test this in my workbook?

 

[leopardhawk]

sandy, I'm not sure I understand your code. When I copied it into a module, most of it turned red which I assumed meant that there was a problem with it...

 

[sandy666]

this is NOT vba, this is M-code for Power Query

download the file, set refresh time (connection/properties) if necessary and that's all. It's ready-for-use

 

[leopardhawk]

How do I get your M-code into my workbook (19 worksheets all inter-twined)? I don't need a separate workbook for this.

 

[sandy666]

sure,
Data tab - New Query - From Other Sources - Blank Query
then you'll see Power Query Editor, find Advanced Editor and click it then replace code there with code copied from the post then Done and next Close&Load, it should close PQ Editor and load TaxTips table into the new sheet

 

[leopardhawk]

Okay, I think I understand. This could be an awesome solution for me. Question though. I have an existing worksheet (tax_credits_web) that has always been the one in question. Will this query update that worksheet or do I have to create a new one, delete the old one and then rename the other to the same name? There are a lot of formulas on other worksheets that are all linked to this one. Thanks!

 

[sandy666]

if you do what I said it will create new sheet with table
I don't know structure of your old tax table so you need to compare them
also I don't know which name you want to change
I don't know how the web source table will change and how often so too many questions without answer, sorry

for PowerQuery doesn't matter sheet name or where it is, you can drag whole QueryTable to the left/right/up or down and it still work

I did very simple version of TaxTips with minimum code with autoupdate, the rest is up to you

 

[John_w]

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

Okay, I did as you suggested and tested both 2019 and 2020. They both worked perfectly. Is there a way that I can test this in my workbook?

Yes, simply paste my code into modules, as previously described, in your workbook. I have slightly changed the code so that it operates on your "tax_credits_web" sheet, rather than the active sheet, which necessitated the Activate line.

ThisWorkbook module:

Private Sub Workbook_Open()

    Dim currentYear As String
    
    currentYear = InputBox("Enter the simulated current year")  'Delete this line when you have finished testing
    If currentYear = "" Then currentYear = Year(Date)
    Get_Tax_Credits Worksheets("tax_credits_web"), currentYear
    
End Sub

Module1:

Public Sub Get_Tax_Credits(ws As Worksheet, currentYear As String)
    
    Dim qt As QueryTable
            
    With ws
        If .QueryTables.Count = 1 Then
            Set qt = .QueryTables(1)
            If qt.Name <> "tax-credits-" & currentYear Then qt.Delete
        End If
        If .QueryTables.Count = 0 Then
            .Cells.Clear
            Set qt = .QueryTables.Add(Connection:="URL;https://www.taxtips.ca/nrcredits/tax-credits-" & currentYear & "-base.htm", Destination:=.Range("A1"))
            With qt
                .Name = "tax-credits-" & currentYear
                .RefreshOnFileOpen = False
                .SaveData = True
                .WebSelectionType = xlSpecifiedTables
                .WebFormatting = xlWebFormattingNone
                .WebTables = "4"
                .Refresh BackgroundQuery:=False
            End With
        End If
    End With
    
End Sub