VBA First day of the month help

[Jason Sawyer]

I turn to you all again as this site has been my best resource.

I am using an Excel macro open a Reflection session. I run this daily but the variables vary each day.

What I need is the start to be the first day of the month from yesterday. example:
From - 06012009
To - 06242009 (as today is the 06/25/09)

Here is what I tried.

sDate = VBA.Format(Now, "mmddyyyy")
fDate = Left(sDate, 2) & "01" & Right(sDate, 4)
tDate = Left(sDate, 2) & (Mid(sDate, 3, 2) - 1) & Right(sDate, 4)

And then
.Transmit fDate & Chr(13)
etc.

Well, this has proven that Excel is much smarter than I am.

 

[phxsportz]

tdate = format(date-1,"mmddyyyy")
fdate = left(tdate,2) & "01" & right(tdate,4)

 

[Norie]

Jason

What do you mean by 'the first day of the month from yesterday'?

 

[VoG]

Perhaps

Dim fdate as Date
fdate = DateValue("1" & "/" & Month(Date - 1) & "/" & Year(Date - 1))

 

[phxsportz]

oops.. and

application.SendKeys(tdate & "~")

 

[phxsportz]

Jason

What do you mean by 'the first day of the month from yesterday'?

I think he meant it as follows...:

Today is 06/02/09 So first of the month from yesterday would be 06/01/09

If today is 06/01/09 the first of the month from Yesterday (05/31/09) would be 05/01/09

At least that was my take on it