How to build Excel macro with date range

Question

I hv created a From Date & To Date in excel (Sheet1). Under cell A3, I hv From Date :: 01-01-2010 and cell A4 To Date :: 31-08-2010.

How do I link the Date Range to the below macro:-

Sub Sales()

Dim StrSQl As String

Con = "Provider=IBMDA400;Data Source=XXX.XXX.XXX.XXX;User Id=yyyy;Password=zzzz"

Set Db = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.recordset")
Db.connectionSTring = Con
Db.Open
StrSQl = "select myuc, sum (myac) as Amount from myabc.myqwerty where mydt >= 20100101 and mydt <= 20100831 group by (mycl)"
rs.Open StrSQl, Db, 3, 3
Sheet1.Cells(10, 1).CopyFromRecordset rs
rs.Close
Set rs = Nothing
Set cn = Nothing
End Sub

Pls take note of the date format between excel & SQL statement. Both are different in formats.

Thanks.

Fionnuala · Accepted Answer · 2010-09-07 18:21:54Z

1

Perhaps:

FromDate=Format(Sheet1.Range("A3"),"yyyymmdd")
ToDate=Format(Sheet1.Range("A4"),"yyyymmdd")
StrSQl = "select myuc, sum (myac) as Amount from myabc.myqwerty where mydt >= "
    & FromDate & " and mydt <= " & ToDate & " group by (mycl)"

answered Sep 7, 2010 at 18:21

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Bob Over a year ago

After inserted the above, the system prompted with an error. Run-time error '6': Overflow under FromDate=Format(Sheet1.Range("A3"),"yyyymmdd").

Fionnuala Over a year ago

What, if anything, did you declare FromDate As? BTW, I tested before I posted.

Bob Over a year ago

It works as I hv changed the input format to yyymmdd from 01-01-2010. Just need to educate the users of the new format.

Fionnuala Over a year ago

You should not have to change the format, the line Format(Sheet1.Range("A3"),"yyyymmdd") takes a date of any format, including 01-01-2010, and changes it to 20100101, which is what you show in your example SQL.

Bob Over a year ago

ok, i hv re-tried the above again. This time, it has no error & no result too. When I amended it to Format(Sheet1.Range("A3")) with input format as yyyymmdd, I can get the result. Wonder why ? BTW, how to clear the result when u insert a new set of variable ?

|

Collectives™ on Stack Overflow

How to build Excel macro with date range

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related