Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change web address in macro
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change web address in macro
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change web address in macro
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change web address in macro
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change web address in macro
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change web address in macro
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change of cells address | Excel Discussion (Misc queries) | |||
HOW TO CHANGE DRIVE ADDRESS IN MACRO | Excel Programming | |||
Range's Address: How To Change via VBA? | Excel Programming | |||
How do I avoid excel change absolute address to relative address | Excel Discussion (Misc queries) | |||
Change IP address with macro | Excel Programming |