Programaticaly update webquery - help

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Hi,

I would like to be able to programaticaly change some of the parameters
in an webquery. For example if I have a webquery that uses
www.somesite.com?day=1&month=2, when I access my workbook the next day
and refresh the data it will load the previous days data. So I need some
way to change the parameters of the query to
www.somesite.com?day=(=date.day)&month=(=date.month);

Could someone point me to a website that might explain how to program
access to do this automatically when the workbook is opened?

Thank you
Paul
 
something like this:

dayoftoday= day(date)
monthoftoday=month(date)
ConnectString = "URL;www.somesite.com?day=" & dayoftoday & "&month=" &
monthoftoday

' Define a new Web Query
Set QT = ActiveSheet.QueryTables.Add(Connection:=ConnectString,
Destination:=activesheet.Range("A1"))
With QT
.Name = MyName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "7"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
End With

hope it helps...

"Paul" kirjoitti:
 
Back
Top