Excel VBA - End Of Month Report

[jordanctc]

I'm trying to finish a fully automated report for shipping so they don'
have to do anything.

The report is run once a month and needs to be "Inventory 3-31-200
with Discounts.xls" (or whatever the last day of that month is...)

Right now I'm running...

ActiveWorkbook.SaveAs Filename:= _
"I:\Accounting\Inventory Files\" & Format(Date, "yyyy")
"\Inventory " & Format(Date, "m-d-yyyy") & " wit
Discounts.xls",....etc....

which is great if they run it on the last day of the month but it turn
out they might run it a couple of days before the last day of the mont
or at the end of the graveyard shift (like 5-7am on April 1st).

Temporarily, I could manually fix it but long term this won't wor
(plus I'd never learn anything new). Additionally, the guy in shippin
(who will run this report) called our system admin once to ask how t
cut and paste properly - thusly I think it is in my best interest t
take him totally out of the equation.

I've tried to figure out the EOMONTH function to no avail... I am als
trying to be mindful that files are saved in year specifi
folders....so....if Dec 05 report was run in Jan 06 the save in folde
would have to be 2005.

It's a bit much for my little gray cells today....

Thanks,
Jorda

 

[Doug Bell]

If I understand, you are not controlling the data in your Workbook "Report",
just where it gets saved and what it gets called, it can be run anytime
before or after the actual EOM date.

Personally I would keep well away from Excel and use a database for this,
hiving data off into separate files is risky and it is easy to loose data,
Also Excel does not have record integrity, it has cells, not records so your
data can be easily corrupted.

Anyway,
If you have a cell on the report that always gives you a date within the
month, that can be your trigger or you can use a form asking for the month &
year then

Dim intMonth as integer, intYear as integer
Dim datKey as date, datEOM as Date

datKey=#...........# 'some date within the month
intYear=Year(datKey)
intMonth=Year(datKey)
datKey=DateSerial(intYear, intMonth, 1)
datKey=DateAdd("m",1,datKey)
datEOM=DateAdd("d",-1,datKey)

ActiveWorkbook.SaveAs Filename:= _
"I:\Accounting\Inventory Files\" & intYear & _
"\Inventory " & Format(datEOM, "m-d-yyyy") & _
"Discounts.XLS"