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!

 

[leopardhawk]

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

John, that works exactly as you said it would. However, is there any disadvantage to having a macro-enabled workbook vs. a normal workbook? Do you have an opinion about using a PowerQuery instead of VBA code?

I know some folks are worried about viruses etc. Thanks for all your help and advice.

 

[John_w]

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

The main disadvantage of a macro-enabled workbook is that any macros (including Workbook_Open) won't run unless the user enables them. The enabling of macros is done either in the macro security settings (Developer tab -> Macro Security) or by the user responding to the security warning when they open the workbook and clicking 'Enable Content' or 'Enable Macros'.

Sandy666's Power Query approach used in the normal workbook has the advantage of simplicity, and no macros. However, as I understand it, the query is refreshed only if the user refreshes it manually or the connection is set to refresh automatically when opening the workbook. If the latter then the query would refresh unnecessarily, even though the current year hadn't changed. A macro would be needed to have the Power Query refresh only when the workbook is opened and the year has changed, e.g. on 1-Jan-2020.

 

[leopardhawk]

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

Sandy666, as per your post above and taking into consideration John's post (#32), will your PowerQuery auto-update every year when the file is opened after midnight on January 1st? If not, is there a simple way to make this happen within the query?

Thanks!

 

[sandy666]

sure, it will

 

[leopardhawk]

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

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

Hello John_w, I ran this code and I am getting a 'compile error: sub or function not defined'. I have not changed anything within the workbook so I am hoping you can help me determine the cause of the error. Thanks!

 

[John_w]

If you click OK on that error message, which part of the code is highlighted in blue?

The only Sub which is called anywhere is Get_Tax_Credits, so either the call to it in Workbook_Open is misspelt or the Public Sub name in Module1 is misspelt.

 

[leopardhawk]

You are correct, I was going to mention that in my post but I forgot to do so. The Sub Get_Tax_Credits is highlighted in blue. I re-copied your code from post #30 so I'm not sure how anything could be misspelled. I double-checked the spelling in the code and they appear to be identical. Ideas?

 

[John_w]

Have you put the Public Sub Get_Tax_Credits(ws As Worksheet, currentYear As String) to End Sub lines in a standard module, e.g. Module1?

I can reproduce the error if I put that code in a sheet module. That code must go in a standard module.

If you have put it in a standard module and you still get that error then I can only suggest starting with a new empty workbook and put the code in the appropriate modules, as described in my earlier post.

 

[leopardhawk]

Okay, once again, you are correct, I had it in a sheet module, the same one that contains the link to the external data. That is where I thought it had to be. I have cut and pasted it to a standard module as per your previous post and the error is gone. Thank you so much!