Error Handling Open Function or query for missing Files
 

I have a question regarding Error handling, or possibly writing an if
then statement to correct errors that I get when I open csv Files wit
a macro. What I have is a machine that we download process logs fro
that saves them in sequential order (ex. Lot0001, Lot0002, ect.). Th
problem arises when only certain logs are downloaded (Ex. Lot0003
Lot0004, Lot0007). The logs in sequential order format and run fine
but after skipping over the first missed log, the macro errors out.
tried to put an error handler in that takes it to the next "i" on a
error, but I am not sure that I did it right. I would be willing t
use an if, then statement if you can check to see if the csv file i
there or not, and the go to the next "i" if it is absent. Here is
sample of my code, and any help is greatly appreciated!

On Error GoTo Failsafe


LotFile = InputBox("Enter the file folder in that contains th
Logs")

MyPath = "\\Shared Drive" & LotFile

Before = InputBox("Enter the beginning Lot File ")

After = InputBox("Enter the ending Lot File ")

For i = Before To After

MyFile = LotNumber & i & ".csv"

SaveFile = LotNumber & i

Workbooks.OpenText Filename:=MyPath & "\" & MyFile

'Code to format logs then hyperlink and and save them

Failsafe:

Next i

End Sub

Thanks again

--
Message posted from http://www.ExcelForum.com

Error Handling Open Function or query for missing Files
 

Hi Nate,

One way to handle this is to check for the existence of a specific
filename before you try to open and process it. I've modified your code
below to do this:

Sub Whatever()

LotFile = InputBox("Enter the file folder in that contains the Logs")
MyPath = "\\Shared Drive" & LotFile
Before = InputBox("Enter the beginning Lot File ")
After = InputBox("Enter the ending Lot File ")

For i = Before To After
MyFile = LotNumber & i & ".csv"
SaveFile = LotNumber & i
If Len(Dir$(MyPath & "\" & MyFile)) 0 Then
Workbooks.OpenText Filename:=MyPath & "\" & MyFile
'Code to format logs then hyperlink and and save them
End If
Next i

End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"BigNate " wrote in message
...
I have a question regarding Error handling, or possibly writing an if,
then statement to correct errors that I get when I open csv Files with
a macro. What I have is a machine that we download process logs from
that saves them in sequential order (ex. Lot0001, Lot0002, ect.). The
problem arises when only certain logs are downloaded (Ex. Lot0003,
Lot0004, Lot0007). The logs in sequential order format and run fine,
but after skipping over the first missed log, the macro errors out. I
tried to put an error handler in that takes it to the next "i" on an
error, but I am not sure that I did it right. I would be willing to
use an if, then statement if you can check to see if the csv file is
there or not, and the go to the next "i" if it is absent. Here is a
sample of my code, and any help is greatly appreciated!

On Error GoTo Failsafe


LotFile = InputBox("Enter the file folder in that contains the
Logs")

MyPath = "\\Shared Drive" & LotFile

Before = InputBox("Enter the beginning Lot File ")

After = InputBox("Enter the ending Lot File ")

For i = Before To After

MyFile = LotNumber & i & ".csv"

SaveFile = LotNumber & i

Workbooks.OpenText Filename:=MyPath & "\" & MyFile

'Code to format logs then hyperlink and and save them

Failsafe:

Next i

End Sub

Thanks again!


---
Message posted from http://www.ExcelForum.com/

Error Handling Open Function or query for missing Files
 

BigNate,

Did you try to put the error handler after your for loop?
Also I don't see your "next i" in your sample. Except for the erro
handler. But I assuming it's there in your actual code.

For i = Before To After
On Error GoTo Failsafe


Charle

--
Message posted from http://www.ExcelForum.com

Error Handling Open Function or query for missing Files
 

Thanks Rob! That worked perfect

--
Message posted from http://www.ExcelForum.com