PDA

View Full Version : VBA variable url webquery



Spongejobu
04-30-2018, 12:11 AM
Dear all,

I have been struggeling to get this piece of vba working but to no avail.
The thing i try to do is import the weather statistics from the following website:

http prefix followed by: projects.knmi.nl/klimatologie/daggegevens/getdata_dag.cgi?stns=330&vars=YYYYMMDD:FG:FHX:FXX:TG:TN:TNH:TX:TXH:RH:RHX:R HXH&byear=2018&bmonth=4&bday=2&eyear=2018&emonth=4&eday=6
(sorry, pasting the complete link blocks me from posting this post)
through the click on a button.
This is not very difficult I recorded making a webquery and copied it into the code of the commandbutton and it all works.
So far so good but now i want to make the url variable so that the data that is pulled from the website is from the range of dates I specify in my worksheet.
The dates are embeded in the link as seen above, through some formulas I created a link which is dependant on dates I specify, all this can be found in the "Helper sheet" specifially cell C39,
the button for importing the webquery is situated in the sheet "Weather report".
I have tried many things but my vba knowledge is too lacking in order to solve this, I have searched/ read for many hours without succes, so here I am.
Can anyone help me out or guide me into the right direction, if there is something unclear say it and I'll elaborate.

Normally the link is placed where "Alfa" is bold
This was a trial which didn't work, exchanging the bold "Alfa" with the provided link above works.
The piece of code:



Private Sub CommandButton1_Click()

Dim Alfa As String

Alfa = Worksheets("Helper sheet").Cells(39, 3).Value


ActiveWorkbook.Queries.Add Name:= _
"RH&byear=2018&bmonth=4&bday=2&eyear=2018&emonth=4&eday=7", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Table.FromColumns({Lines.FromBinary(Web.Contents(""Alfa""), null, null, 1252)})," & Chr(13) & "" & Chr(10) & " #""Split Column by Delimiter"" = Table.SplitColumn(Source, ""Column1"", Splitter.SplitTextByDelimiter("","", QuoteStyle." & _
"Csv), {""Column1.1"", ""Column1.2"", ""Column1.3"", ""Column1.4"", ""Column1.5"", ""Column1.6"", ""Column1.7""})," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Split Column by Delimiter"",{{""Column1.1"", type text}, {""Column1.2"", type text}, {""Column1.3"", type text}, {""Column1.4"", type text}, {""Column1.5"", type text}, {""Column1.6"", type text}, {" & _
"""Column1.7"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""

Worksheets("Weather report").Activate

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""RH&byear=2018&bmonth=4&bday=2&eyear=2018&emonth=4&eday=7"";Ext" _
, "ended Properties="""""), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT * FROM [RH&byear=2018&bmonth=4&bday=2&eyear=2018&emonth=4&eday=7]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = _
"RH_byear_2018_bmonth_4_bday_2_eyear_2018_emonth_4_eday_7"
.Refresh BackgroundQuery:=False
End With
End Sub

I attached the excel file.