Hi, I have recorded a macro that runs a query to an internet address. I have
set up the macro to run automatically at 9pm every evening. My only problem
is the web address changes daily, only the last 10 numbers. Her is the
address for today http://www.bondflights.com/index.php?day=1228089600. The
number always adds 86400 to the new address, therefore tomorrows number will
be 1228176000. Obviously when my macro runs, it only looks at the address I
have in the macro. Is it possible to have the macro change itself by adding
this number to that address or is that just plain stupid.
Thanks for whatever replies this generates.

A URL is a string that can be combined like any other string.

IPNum = 1228089600

URL = "http://www.bondflights.com/index.php?day=" & IPNum

"Woodi2" wrote:

> Hi, I have recorded a macro that runs a query to an internet address. I have
> set up the macro to run automatically at 9pm every evening. My only problem
> is the web address changes daily, only the last 10 numbers. Her is the
> address for today http://www.bondflights.com/index.php?day=1228089600. The
> number always adds 86400 to the new address, therefore tomorrows number will
> be 1228176000. Obviously when my macro runs, it only looks at the address I
> have in the macro. Is it possible to have the macro change itself by adding
> this number to that address or is that just plain stupid.
> Thanks for whatever replies this generates.

Thanks for the quick reply Joel. Could you explain a little better, I'm no
where near as clued as people like yourself and what you have wrote does not
make much sense to an excel apprentice like myself. Ian

"Joel" wrote:

> A URL is a string that can be combined like any other string.
>
> IPNum = 1228089600
>
> URL = "http://www.bondflights.com/index.php?day=" & IPNum
>
> "Woodi2" wrote:
>
> > Hi, I have recorded a macro that runs a query to an internet address. I have
> > set up the macro to run automatically at 9pm every evening. My only problem
> > is the web address changes daily, only the last 10 numbers. Her is the
> > address for today http://www.bondflights.com/index.php?day=1228089600. The
> > number always adds 86400 to the new address, therefore tomorrows number will
> > be 1228176000. Obviously when my macro runs, it only looks at the address I
> > have in the macro. Is it possible to have the macro change itself by adding
> > this number to that address or is that just plain stupid.
> > Thanks for whatever replies this generates.

I would use this code to get the new number

StartDate = DateValue("12/1/08")
StartNumNum = 1228089600

TDate = Date()
NumDays = int(TDate - StartDate)
NewNum = StartNumNum + (86400 * NumDays)

URL = "http://www.bondflights.com/index.php?day=" & NewNum



"Joel" wrote:

> A URL is a string that can be combined like any other string.
>
> IPNum = 1228089600
>
> URL = "http://www.bondflights.com/index.php?day=" & IPNum
>
> "Woodi2" wrote:
>
> > Hi, I have recorded a macro that runs a query to an internet address. I have
> > set up the macro to run automatically at 9pm every evening. My only problem
> > is the web address changes daily, only the last 10 numbers. Her is the
> > address for today http://www.bondflights.com/index.php?day=1228089600. The
> > number always adds 86400 to the new address, therefore tomorrows number will
> > be 1228176000. Obviously when my macro runs, it only looks at the address I
> > have in the macro. Is it possible to have the macro change itself by adding
> > this number to that address or is that just plain stupid.
> > Thanks for whatever replies this generates.

Thanks again Joel. Below is part of the macro query I run. Could you show
me exactly where I would insert your suggestion, also will this change the
value every day by adding 86400? Apologies if I am hogging your time, how do
you learn all of this anyway, can it be picked up easily or are you a man
with many years experience?

Sheets("Query Sheet").Select
Range("E6").Select
Sheets("Query Sheet").Select
With Selection.QueryTable
.Connection =
"URL;http://www.bondflights.com/index.php?day=1228089600"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2,3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

"Joel" wrote:

> I would use this code to get the new number
>
> StartDate = DateValue("12/1/08")
> StartNumNum = 1228089600
>
> TDate = Date()
> NumDays = int(TDate - StartDate)
> NewNum = StartNumNum + (86400 * NumDays)
>
> URL = "http://www.bondflights.com/index.php?day=" & NewNum
>
>
>
> "Joel" wrote:
>
> > A URL is a string that can be combined like any other string.
> >
> > IPNum = 1228089600
> >
> > URL = "http://www.bondflights.com/index.php?day=" & IPNum
> >
> > "Woodi2" wrote:
> >
> > > Hi, I have recorded a macro that runs a query to an internet address. I have
> > > set up the macro to run automatically at 9pm every evening. My only problem
> > > is the web address changes daily, only the last 10 numbers. Her is the
> > > address for today http://www.bondflights.com/index.php?day=1228089600. The
> > > number always adds 86400 to the new address, therefore tomorrows number will
> > > be 1228176000. Obviously when my macro runs, it only looks at the address I
> > > have in the macro. Is it possible to have the macro change itself by adding
> > > this number to that address or is that just plain stupid.
> > > Thanks for whatever replies this generates.

You should ALWAYS post your code for comments. You certainly don't want to
add another fetch each time.

Assuming your query in sheet1, place a number such as1228089600

in cell a1 and run this sub. Run within your timer if desired


Sub GetSchedule()
Dim mynum As Long
mynum = range("a1").Value

With Sheets("sheet1").QueryTables(1)
.Connection = "URL;http://www.bondflights.com/index.php?day=" &
mynum
.WebSelectionType = xlSpecifiedTables
.WebTables = "1"
.Refresh BackgroundQuery:=False
End With
range("a1") = mynum + 86400
'MsgBox mynum
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Woodi2" <(E-Mail Removed)> wrote in message
news:C32642C1-2262-460A-859D-(E-Mail Removed)...
> Thanks for the quick reply Joel. Could you explain a little better, I'm
> no
> where near as clued as people like yourself and what you have wrote does
> not
> make much sense to an excel apprentice like myself. Ian
>
> "Joel" wrote:
>
>> A URL is a string that can be combined like any other string.
>>
>> IPNum = 1228089600
>>
>> URL = "http://www.bondflights.com/index.php?day=" & IPNum
>>
>> "Woodi2" wrote:
>>
>> > Hi, I have recorded a macro that runs a query to an internet address.
>> > I have
>> > set up the macro to run automatically at 9pm every evening. My only
>> > problem
>> > is the web address changes daily, only the last 10 numbers. Her is the
>> > address for today http://www.bondflights.com/index.php?day=1228089600.
>> > The
>> > number always adds 86400 to the new address, therefore tomorrows number
>> > will
>> > be 1228176000. Obviously when my macro runs, it only looks at the
>> > address I
>> > have in the macro. Is it possible to have the macro change itself by
>> > adding
>> > this number to that address or is that just plain stupid.
>> > Thanks for whatever replies this generates.

StartDate = DateValue("12/1/08")
StartNumNum = 1228089600

TDate = Date()
NumDays = int(TDate - StartDate)
NewNum = StartNumNum + (86400 * NumDays)
URL = "http://www.bondflights.com/index.php?day=" & NewNum

With Sheets("Query Sheet")
With .QueryTable
.Connection = "URL;" & URL
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2,3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End With

"Woodi2" wrote:

> Thanks again Joel. Below is part of the macro query I run. Could you show
> me exactly where I would insert your suggestion, also will this change the
> value every day by adding 86400? Apologies if I am hogging your time, how do
> you learn all of this anyway, can it be picked up easily or are you a man
> with many years experience?
>
> Sheets("Query Sheet").Select
> Range("E6").Select
> Sheets("Query Sheet").Select
> With Selection.QueryTable
> .Connection =
> "URL;http://www.bondflights.com/index.php?day=1228089600"
> .WebSelectionType = xlSpecifiedTables
> .WebFormatting = xlWebFormattingNone
> .WebTables = "2,3"
> .WebPreFormattedTextToColumns = True
> .WebConsecutiveDelimitersAsOne = True
> .WebSingleBlockTextImport = False
> .WebDisableDateRecognition = False
> .WebDisableRedirections = False
> .Refresh BackgroundQuery:=False
> End With
>
> "Joel" wrote:
>
> > I would use this code to get the new number
> >
> > StartDate = DateValue("12/1/08")
> > StartNumNum = 1228089600
> >
> > TDate = Date()
> > NumDays = int(TDate - StartDate)
> > NewNum = StartNumNum + (86400 * NumDays)
> >
> > URL = "http://www.bondflights.com/index.php?day=" & NewNum
> >
> >
> >
> > "Joel" wrote:
> >
> > > A URL is a string that can be combined like any other string.
> > >
> > > IPNum = 1228089600
> > >
> > > URL = "http://www.bondflights.com/index.php?day=" & IPNum
> > >
> > > "Woodi2" wrote:
> > >
> > > > Hi, I have recorded a macro that runs a query to an internet address. I have
> > > > set up the macro to run automatically at 9pm every evening. My only problem
> > > > is the web address changes daily, only the last 10 numbers. Her is the
> > > > address for today http://www.bondflights.com/index.php?day=1228089600. The
> > > > number always adds 86400 to the new address, therefore tomorrows number will
> > > > be 1228176000. Obviously when my macro runs, it only looks at the address I
> > > > have in the macro. Is it possible to have the macro change itself by adding
> > > > this number to that address or is that just plain stupid.
> > > > Thanks for whatever replies this generates.