Working with dates within VBA not cells

[krice1974]

Hi all. I'm trying to write a macro that loops, with each pass rewriting a web query address string that uses a date. The query returns a table with one days' data. When each date's table is returned, I'll extract the data out of it, delete the query, and rewrite the string incrementing by one day, repeating 30 times.

The string needs the date in this format: March 13, 2009 is 20090313.

My problem is how to use the useful date worksheet functions within VBA and not having to calculate each next date in a cell.

Any takers? This seems like a fun bit of code...

 

[Jonmo1]

DateOfInterest = DateSerial(2009,3,13)
DateString = Format(DateOfInterest,"mmmm dd, yyyy")

Hope that helps...

 

[Legacy 68668]

Date function in vba?

Have you tryed DateValue or DateSerial function ?

e.g

Format(DateSerial(2009,3,13),"yyyymmdd")

 

[krice1974]

I'm not sure how that would determine the next day though, then extract the values within it as integers or strings... flipping to the next month when adding day +1 to the last day of a month etc...

 

[Jonmo1]

so the problem isn't really how to format the date, but how to obtain the date...

What do you mean by the next day?

What is the beginning date? How do you get the beginning date, what date do you want?

 

[Legacy 68668]

try

Dim StartDate As Date, EndDate As Date, i As Long
StartDate = Date
EndDate = DateValue("2009/4/10")
For i = 0 To EndDate - StartDate + 1
    MsgBox Format(StartDate + i, "yyyymmdd")
Next

 

[krice1974]

Hi Jonmo, I didn't see your first post.

OK. I want to run it every day, starting with tomorrow of each day that it runs, looping for 30 days, so a for-next loop with NextDay (variable) being incremented by 1. So if it ran today, it'd start by returning 20090313 as the first assignment to NextDay. Hope that's clearer...

 

[krice1974]

Seiya, fantastic.