Update Web Query via VBA

breadzeppelin

New Member
Joined
Jun 11, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

1615227207270.png


VBA Code:
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

breadzeppelin

New Member
Joined
Jun 11, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,154
Members
417,010
Latest member
jnuss03

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
Top