I got this very helpful answer:

Sub Dynamic_Query()

Dim sTabName As String
Dim sXS As String
Dim sQuery As String

sXS = CStr(Format(Date, "mmddyy"))
sTabName = "[" & sXS & "$]"
sQuery = "Select * From " & sTabName & ";" '

End Sub




to this post:

2 Posts

Posted - 07/31/2007 : 7:54:19 PM Show Profile Edit Topic Reply with Quote Report Abuse
I am using MS office 2000. I use query to import a list of client names and their individual contract numbers from an a billable report which is in a separate excel workbook.
EX:

15 ABC
30 Group
12 Joe Smith Co.

I then take this info and put it into a chart. My problem is that each new week, I create a new billable report worksheet. Currently my query data is coming from 071407 but now I want it to come from 072807 and so on and so forth. How can I change this? (And if so, automatically would be great).



I just don't know what to fill in after sTabName = and
sQuery =

Any help?

Hi

sXS = CStr(Format(Date, "mmddyy"))

Here Date Replaces the Sheet No

sTabName = "[" & sXS & "$]"

Other than that you need not do anything. Can you give the Names of the sheet?

Regards
Shasur

http://www.dotnetdud.blogspot.com

The sheet name is 081107.

Hi Kate

The code should take care of your needs.

As the sheet name is 081107

sXS = CStr(Format(Date, "mmddyy"))

will get you 080807 (Today's date 08-Aug-07)

Here Date Replaces the Sheet No

sTabName = "[" & sXS & "$]"

sTabName will be the formatted sheet name.

Hence sQuery = "Select * From " & sTabName & ";" ' will fetch you the records of that particular date

If you want for some series of dates, you can used DateAdd function to increment the date value



http://www.dotnetdud.blogspot.com