Error can't Find File

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
0
Utf
4/7/2010 3:06:01 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
813 Views

Similar Articles

[PageSpeed] 22

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@gmail.com
"Dave" <Dave@discussions.microsoft.com> wrote in message 
news:8B383DBA-3F9B-4D41-8BAD-E4DB5A8F2E55@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 

0
Don
4/7/2010 3:31:06 PM
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
0
Dave
4/7/2010 4:04:35 PM
Reply:

Similar Artilces:

Money cannot locate file...
I purchased a new computer with Money 2003 pre-loaded. I am running 2001 on the old computer which also came pre- loaded. When I attempt to run the old money file in the 2003 program, I get the message: Money cannot locate the file C:\My Money.mny or cannot open it, possibly because it is a read only file or you do not have permission to change it or the disk drive is write protected. It goes on to say I should make sure no one else is using it. I am certain the file is not protected, I am logged on as administrator, and I have copied the file using the LAN and a CD into numerous direc...

Calendar Printing Assistant Error
HI all, When I try to open CPA I get the following error. Cannot connect to Microsoft Office Outlook 2007. Please restart Microsoft Office Outlook 2007 and Calendar Printing Assistant. Original Microsoft Office Outlook 2007 error message: Object reference not set to an instance of an object. Does anyone have any suggestions. Thanks Gladys ...

Insert file name into header
I need a macro or something that automatically insert the file name of the excel file into the header or footer, does not matter which, i dont need the directory path. Either this or automatically insert into the header or footer the contents of a specific cell in the worksheet. the cell address will never change., if i create a new worksheet, same cell address needs to go into the header thanks pat Try here: http://www.mrexcel.com/tip055.shtml best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Patrick" <Patrick@di...

Can't delete Outlook 2002 restored data files
My Windows XP Pro system crashed due to a hardware failure, and I had to restore my data once I reloaded a clean copy of Windows XP. I restored my Outlook data (PST files) to my D: drive thinking that I didn't want to overlay the current PST file, and would just import the data for it afterwards. I have managed to copy the data into my current Outlook PST file, but now I can't delete the folder structure that was created by my restore operation (i.e., "Documents and Settings" folder on D: drive) with all of its subfolders. I receive the error, "Cannot delete Ou...

Error 0x800C0155,2
Everytime I tried to start up Maillive I get the message with fault code 0x800C0155,2 I have surched on the web but didn't get a good solution for this problem. The solutions of shutting down the virusscanner and using the administrator logging doesn't work for me. I have also updated, reinstalled Maillive but no solution. I"m stilll using Windows XP and didn't change the last days anything on my computer. This fault just suddenly occured after a correct use of the Mail program for more then a year. Is there anyone who has useful solutions for this problem???? ...

Creating records in one file whilst editing another file
I have a simple form which allows updates to records in an employee file. Each time a record is changed I would like to create an audit record in a separate file. I do not need any input from the user when creating the record as all the information is implied (date, time, user, before and after values etc.) I would be interested to know what people think the easiest way to do this would be...?? All opinions welcome - the more the merrier. Thanks, Jon. 88Caddy wrote: >I have a simple form which allows updates to records in an employee >file. Each time a record is changed I would ...

How to go back from 350 worksheet to the first worksheet in 1 file
Hi.. i hv 1 question to ask. Assume that i have more than 200 worksheet into 1 excel file, so imaging that i need to go back to the first worksheet from 350 page. So how should i do? Can i add the button like 'GO BACK TO FIRST PAGE' inside the 350 page, so when i click that button, it will go back to the first page, or any better suggesstion? Thanks in advanced. Regards, Wilmos Lee Hi try right-clicking with your mosue on the sheet navigation buttons in the lower left corner of your workbook. This should open a list of all your worksheets -- Regards Frank Kabel Frankfurt, Germany ...

Office errors
Hi: I have Windows2000 Pro with sp2 installed. Also I have Office2000 professional. Recently, my office programms have some problems: 1.Word does not work well but I can open it. 2.Excel doesn't open at all. I've tried to reinstall them, no luck. any suggestions? Thanks a lot! One common solution is to clean up your windows temp folder. For excel: Sometimes, the file that holds the customized toolbar gets corrupted and causes excel heartache when opening. Use Windows start button|Find| and search for *.xlb. You may have to search for hidden files within hidden folders. But...

What is error# 0x80040102, my Domino connector is not reading mail
My calendar & tasks are read by the outlook easily but fails to read mails. It's working for perfect for a collague but fails on me every time. Finally this is the error which is shown by the replicator. ...

Linked Template compatibility error
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am receiving this message in the compatibility tool upon open. This is a .DOC file not a .docx <br><br>Word cannot find linked template. <br><br>What can I do? <br><br>I don't really want any template. <br><br>Thanks <br> -Alan All Word documents are based on templates, .doc - .docx makes no difference whatsoever :-) Apparently a template was attached to the document by its creator. The document will continue to look for it every time you open the ...

returning to file previously worked on after closing it.
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel In Appleworks, whenever I returned to the file I had been using (eg the manuscript I had been working on) the last page I had been editing would open up. But in Word, it always opens at the first page of the document and i have to scroll down many pages to reach the page I had been last working on. <br> Is there a short cut? A couple of the simplest & most direct solutions: Once the file opens key Command+End. If using a laptop or compact keyboard you may need to use the fn key along with that. ...

Reports error #7
Setup: Server A is CRM 4.0 Rollup 2 Server B is SQL 2005 w/SP2 and SSRS connector Server A has two orgs installed: Production and Test. Reports work great on Test, no problems at all. However, on Production reports are met with the following error: The permissions granted to user 'domain\server$' are insufficient for performing this operation. I have not found any solutions for CRM servers that are running multiple tenants. I checked the report server settings on the two orgs and they are indentical. I have also checked the DB settings on the SQL server and they are identic...

Runtime error
I am using a module called TimeToDec to convert a time like: 10:00 Pm to 10 for the purpose of multiplying it with another value to get a dollar amount for hours worked*pay rate=amount owed. This is taking place in a form and as I step through the EmployeeID field, I am getting a: Microsoft Visual Basic RunTime error '94': Invalid use of null. My options are to debug or end. Does anyone know how to fix this? Thanks Check the underlying data at table level. You probably have null values in the field that TimeToDec uses in some records. TimeToDec probably doesn't know ...

excel e-mail file
i rcvd an e-mail with an excel doc. attach. i do not have microsoft excel on my computer. is there any other program that will open an excel doc. >-----Original Message----- >i rcvd an e-mail with an excel doc. attach. >i do not have microsoft excel on my computer. >is there any other program that will open an excel doc. >. >never mind, i was able to open it with microsoft access. Hi there is a free Excel viewer available at http://www.microsoft.com/downloads/details.aspx?FamilyID=4EB83149-91DA- 4110-8595-4A960D3E1C7C&displaylang=EN HTH Frank anonymous@discussions.m...

error code #2
I'm having a hard time sending & receiving. the "Outlook Send/Receive Progress" window pops up it says "0 of 2 Tasks have completed successfully" Sending and Receiving' reported error (0x8004210A) : 'The operation timed out waiting for a response from the receiving (POP) server. If you continue to receive this message, contact your server administrator or Internet service provider (ISP).' Sending and Receiving' reported error (0x8004210B) : 'The operation timed out waiting for a response from the sending (SMTP) server. If you continue to ...

receiving mdb files
I have Outlook 2000. I also have someone that needs to regularly send me Access mdb files, but Outlook 2000 is blocking them; how do I get Outlook 2000 to NOT DO THAT? : } "Lisa Simpson" <noone@nowhere.com> wrote in message news:OsvVaR$9EHA.2192@TK2MSFTNGP14.phx.gbl... >I have Outlook 2000. I also have someone that needs to regularly send >me > Access mdb files, but Outlook 2000 is blocking them; how do I get > Outlook > 2000 to NOT DO THAT? : } > > Tell your sender to rename the file to <filename>.mdx (or whatever extension you like that...

Publsher cannot open the file
I created a calendar in publisher and tried to open it today to update it. I keep getting the above message. I have tried the following 1. Turning off Norton Anti-Virus 2. Opening the file on 3 different computers, one of which is not running Norton 3. Opening the file in Safe Mode Nothing is working! I have several graphics embedded in the publication - all from microsoft. I really don't want to waste another day redoing the entire publication. Any suggestions? cslviola wrote: > I created a calendar in publisher and tried to open it > today to update it. I keep getting th...

Deleting rows when cell has "#DIV/0!" error
Hi, I'm currently using this code to delete rows with balnk cells. However, i have changes the formatting of my sheet and the cells are no longer blank they have the "#DIV/0!" error. How can I delete the entire row when the cell in column D has that value? Sub DeleteRowsIfDIsBlank() With payrollsht Dim rg As Range, rgBlank As Range Set rg = Cells.Range("D:D") On Error Resume Next Set rgBlank = rg.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If rgBlank Is Nothing Then Else rgBlank...

Problem Opening Files #2
When an attempt to open an Exel file by double clicking on the file icon in Windows Explorer the Exel window opens (without any workbook present) and the following error message is displayed: "Cannot find the file 'C:\***.XLS (or one of its components) Make sure the path (... etc)" The file can be opened by using the open command within Exel. This problem first appeared while running MS Office under Windows 98 but has persisted since upgrading to Windows 2000 Professional. I have tried Windows update and repairing the program automatically from 'ADD / REMOVE PROGRA...

Find/Find all 03-30-10
Hello I've got a spreadsheet that I need to do a search for on part numbers, and there may be multiple part numbers. If there are duplicate then I'll use the Revison letter of the latest occurance. The cloum headers never change, so i know that b2:b500 is a range for the part numbers that I need to search, and e2:e500 is the range for the rev letter. Can I use the find function, or the findall function, to return me a range, or a count of numbers of cells that is equal to the part number I need? If the count is 1, I can just grab that row, if the count is greater t...

Error Msg
Hi When I drop the table through query or enterprise manager in sql server 2000 it gives the following error “Server: Msg 7105, Level 22, State 3, Line 1 Page (1:1424), slot 0 for text, ntext, or image node does not exist. Connection Broken” There is no column with the data type text, ntext or image is in table and no foreign key only a composite primary key is on the table. But data in the table can be modified, deleted or modified. Dbcc checkdb also raises error “Server: Msg 8929, Level 16, State 1, Line 1 Object ID 2: Errors found in text ID 2655486279680 owned by dat...

http error 501
Hy everybody, I would like to ask if someone has any clue about my problem. When I'm triing to create new public folder I'll ger the error mentioned in subject. This problem is discused in Article ID 839744, but it doesn't help:-( If someone knows something please share your knowledge. Thank You very much. ...

Error 2147217871 (80040e31)
We are getting timeout error 2147217871 (80040e31) - we have been running for several months on 2.0 and all of a sudden we are getting this. We were thinking our computer was dying so we moved our database to another computer, but we are still getting this error every few days. It locks up the system for about 10 minutes - it's a nightmare. I found a KB article (897881) that describes a Query that may clear it up - just wanted to know if this KB article would apply in my case - it say's it's for version 1.2. Also, on some of the networked computers - if I close Manager a...

ost files
We have people that use Outlook 2003 with an Exchange account. The Exchange acounts are hosted by our ISP. I want to know what is the best practice to backup et restore the Outlook 2003 files with an Exchange account. Let say a user has accidently deleted a folder in the Inbox and I want to restore it. With an Exchage account, the outlook file format is ost. Can I backup and resotre juste the ost file. Thanks for your response -- Shidewa Assuming that the Exchange application service provider has deleted item retention turned on, you should be able to use the dumpsteralwayson registry ...

How do I save multiple messages as text file?
When I select multiple messages -> file -> save as text file. Only the headers of the messages are saved and not the bodies. In Outlook 2003, how do I save the bodies also? Thanks. ...