Update Web Query via VBA

[breadzeppelin]

So... from the off, I'll mention that I am a complete novice when it comes to Web Queries with Excel, and probably not much better when it comes to VBA either, so please accept apologies in advance...

My idea seemed straightforward enough; I basically need to grab from a website the data in a table that has 30 columns of tidal data for a local area. The issue is that this table is updated each day, such that the columns will start from today's date + 30 days forward... run it/query it tomorrow and you'll get tomorrow's date + 30 days forward from tomorrow's date...

So I thought why not store the dates in an excel sheet as the variables, then get VBA to update the query with whatever the date is when it's executed etc.

Unfortunately it seems to start off well, but gets a bit grumpy and doesn't seem to like me, so I wondered if any of you Excel Jedis could tell me if there is a simple fix for this...

In the code below, I'm storing each of the 30 days as the variables, and I tried replacing the first date (in bold and red text) to use the first variable (i.e. Day1), but I get this error (which as a mere mortal do not understand exactly what it's beef is... tried googling and searching this forum, but no joy).

Hopefully this makes sense. Any help or pointers would be fab.

Thanks all!

Oz

Sub GetNext30TideTimes()


Day1 = Sheet8.Range("C18").Value
Day2 = Sheet8.Range("C19").Value
Day3 = Sheet8.Range("C20").Value
Day4 = Sheet8.Range("C21").Value
Day5 = Sheet8.Range("C22").Value
Day6 = Sheet8.Range("C23").Value
Day7 = Sheet8.Range("C24").Value
Day8 = Sheet8.Range("C25").Value
Day9 = Sheet8.Range("C26").Value
Day10 = Sheet8.Range("C27").Value
Day11 = Sheet8.Range("C28").Value
Day12 = Sheet8.Range("C29").Value
Day13 = Sheet8.Range("C30").Value
Day14 = Sheet8.Range("C31").Value
Day15 = Sheet8.Range("C32").Value
Day16 = Sheet8.Range("C33").Value
Day17 = Sheet8.Range("C34").Value
Day18 = Sheet8.Range("C35").Value
Day19 = Sheet8.Range("C36").Value
Day20 = Sheet8.Range("C37").Value
Day21 = Sheet8.Range("C38").Value
Day22 = Sheet8.Range("C39").Value
Day23 = Sheet8.Range("C40").Value
Day24 = Sheet8.Range("C41").Value
Day25 = Sheet8.Range("C42").Value
Day26 = Sheet8.Range("C43").Value
Day27 = Sheet8.Range("C44").Value
Day28 = Sheet8.Range("C45").Value
Day29 = Sheet8.Range("C46").Value
Day30 = Sheet8.Range("C47").Value


    ActiveWorkbook.Queries.Add Name:="Table 2 (2)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://www.tide-forecast.com/locations/Whitstable/tides/latest""))," & Chr(13) & "" & Chr(10) & "    Data2 = Source{2}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data2,{{" & [COLOR=rgb(226, 80, 65)][B][B]Day1 [/B][/B][/COLOR]& ", type text}, {""6 Mar"", type text}, {""7 Mar"", type text}, {""8 Mar"", type text}, {""9 Mar"", type text}, {""10 Mar"", type text}," & _
        " {""11 Mar"", type text}, {""12 Mar"", type text}, {""13 Mar"", type text}, {""14 Mar"", type text}, {""15 Mar"", type text}, {""16 Mar"", type text}, {""17 Mar"", type text}, {""18 Mar"", type text}, {""19 Mar"", type text}, {""20 Mar"", type text}, {""21 Mar"", type text}, {""22 Mar"", type text}, {""23 Mar"", type text}, {""24 Mar"", type text}, {""25 Mar"", type" & _
        " text}, {""26 Mar"", type text}, {""27 Mar"", type text}, {""28 Mar"", type text}, {""29 Mar"", type text}, {""30 Mar"", type text}, {""31 Mar"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""


    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 2 (2)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 2 (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_2__2"
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[breadzeppelin]

Ok - it didn't store the highlighted VBA code... but it's this bit I'm referring to:

COLOR=rgb(226, 80, 65)]Day1 [/COLOR

 

[John_w]

If you delete the query's "Changed Type" step then it will import the data without referring to the column headings and data types. When you refresh the query each day it will import that day's data table, therefore no VBA code or macro is needed.