Prev: Search and replace multiple values
Next: Countif using symbols
From: Dave on 7 Apr 2010 11:06
Hi,

I have a macro that opens a file and pulls any outstanding tasks from the
previous day.

I have a problem when it is a new month because the previous month's file is
called MyFileMarch2010.

The macro now looks for MyFileApril2010. How do I capture the error and look
for the previous month's file if it is the first business day of the month?

Any help or suggestions is appreciated.
--
Thank you

Dave
From: Don Guillett on 7 Apr 2010 11:31
As ALWAYS, post YOUR code for comments

if day(date)=1 then
do this
else
do that
end if

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Dave" <Dave(a)discussions.microsoft.com> wrote in message
news:8B383DBA-3F9B-4D41-8BAD-E4DB5A8F2E55(a)microsoft.com...
> Hi,
>
> I have a macro that opens a file and pulls any outstanding tasks from the
> previous day.
>
> I have a problem when it is a new month because the previous month's file
> is
> called MyFileMarch2010.
>
> The macro now looks for MyFileApril2010. How do I capture the error and
> look
> for the previous month's file if it is the first business day of the
> month?
>
> Any help or suggestions is appreciated.
> --
> Thank you
>
> Dave

From: Dave Peterson on 7 Apr 2010 12:04
Or you could just build the name correctly.

Dim myDate as date
dim myFileName as string
dim wkbk as workbook
dim myPath as string

mypath = "C:\yourpathhere\" 'include the trailing backslash

mydate = date - 1 'yesterday

'fix the extension to match .xlsx or .xlsm or ...
myfilename = "myfile" & format(mydate, "mmmmyyyy") & ".xls"

set wkbk = nothing
on error resume next
set wkbk = workbooks.open(filename:=mypath & myfilename)
on error goto 0

if wkbk is nothing then
msgbox myfilename & " wasn't found in: " & mypath
exit sub
end if

....code to work on wkbk goes here



Dave wrote:
>
> Hi,
>
> I have a macro that opens a file and pulls any outstanding tasks from the
> previous day.
>
> I have a problem when it is a new month because the previous month's file is
> called MyFileMarch2010.
>
> The macro now looks for MyFileApril2010. How do I capture the error and look
> for the previous month's file if it is the first business day of the month?
>
> Any help or suggestions is appreciated.
> --
> Thank you
>
> Dave

--

Dave Peterson
 | 
Pages: 1
Prev: Search and replace multiple values
Next: Countif using symbols