Ignore if file not there?

**boomshank** · 07-14-2009, 07:02 AM

Hello,

I am trying to write a macro that imports three text files, formats the data and then saves each file. The problem i have is that although two of the files appear every day, one file does not. Is there any code that will say " if this file is here do this, if not skip it?"

This is the code that i am currently using to import the file.

Please Login or Register  to view this content.

Thank you in Advance

**Richard Schollar** · 07-14-2009, 07:37 AM

Hi

You can use Dir to determine if a file exists:

Please Login or Register  to view this content.

Amend the "C:\Folder\File.txt" to whatever file name & path you want to check.

Richard

**boomshank** · 07-14-2009, 08:40 AM

Thank you Richard,

However the user has to enter a date into a text box, which then completes the filename, will i still be able to use the code you have suggested with this message box?

**Richard Schollar** · 07-14-2009, 09:27 AM

What's the source of your text files? I must admit I didn't fully consider whether they are on a physical drive or perhaps being downloaded from a web address.

**broro183** · 07-14-2009, 09:33 AM

hi all,

Here's a link to a brief function on Ken Puls's site which identifies if the file exists: http://www.excelguru.ca/node/30

The function is very similar to Richard's approach & shows an example of how to use it.
Yes, you can modify either Ken or Richard's version to incorporate the user input via textbox by predefining the file string, for example,

Please Login or Register  to view this content.

Note, that you may need to modify the format of the user's input to exclude characters that can't be part of a file name (eg "/"). Also, do you have error checking/validation to ensure the user inputs a valid string & that inputs of "dd/mm/yy" are not confused with "mm/dd/yy"?

hth
Rob

**boomshank** · 07-14-2009, 10:02 AM

The file is automatically populated throughout the day and stored on a server, each morning a user has to pull it off the server and store it in a folder.
The file name is either

ESRIPS.MIS.Print.date
ESRIPS.MIS.Dispatch.date
ESRIPS.MIS.Reprint.date

the date is in ddmmyyyy format i.e. 10072009

A database has been created to store all the data from these files so that queries can be run on them

I have found that the easiest way to get the data into the database is to first import it into excel, change the format of the data, save the file as HTML and then import it, as you can imagine this was a long process for i am trying to create a macro to do it automatically. the only input is the date.

**Richard Schollar** · 07-14-2009, 11:49 AM

You can use the exact same format you already have for your file path & name:

Please Login or Register  to view this content.

**broro183** · 07-14-2009, 04:45 PM

hi,

It sounds like you're sorted now, but here's a suggestion for ease of sorting saved files at future dates:
I usually save files using the date format of yyyymmdd, because this allows for files to be ordered correctly when ordered in by Name in dialog boxes or in Windows Explorer.

hth
Rob