How do I check to see if a file exists?

[mike101]

I am using a for loop to open and convert data.

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\All Users.WINDOWS\Documents\sciencefair\Wind data\06\" & date8 & "\" & i & ".dat", _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(4, 1), Array(8, 1), Array(12, 1), Array(15, 1), Array(19, 1), _
Array(22, 1), Array(26, 1), Array(30, 1), Array(34, 1), Array(38, 1), Array(42, 1), Array( _
46, 1), Array(53, 1), Array(58, 1), Array(64, 1), Array(68, 1))

^ That works fine, but the problem is there ARE some data files missing. I want to somehow make the for loop continue on to check for the next one if it can't find the file. I know I can usually use "On Error Resume Next" but with this, it doesn't work. If I put it before the open part, it doesn't see any errors and continues on and messes up. If I put "On Error Resume Next" after the workbooks.opentext... I still get a popup saying it can't find the file and I can either end or debug. Any suggestions? Thanks.

 

[PHV]

Have you tried to play with the Dir function to test the existence of the file before open it?

Hope This Help
PH.

 

[alvika]

mExcel.DisplayAlerts = False

you will not get the message popup.

 

[Loomah]

Mike
The code below gives you two options using the FileSystemObject Object.

The first will open each file in the given folder and therefore doesn't need to test for whether a file exists.

The second tests for the existance of a file before attempting to open it.

Just substitute your loop and file name types for opening etc.

Sub a()
Dim oFSO, oFold, oFyls, f1
Dim i As Integer
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFold = oFSO.GetFolder("C:\Documents and Settings\All Users.WINDOWS\Documents\sciencefair\Wind data\06\" & date8 & "\")
Set oFyls = oFold.Files

'OPTION 1
'Loop through all files
For Each f1 In oFyls
    Workbooks.Open Filename:=f1.Name 'etc
Next f1

'OPTION 2
'Check for existing files
For i = 1 To 10
    If oFSO.fileexists(oFold & "\file" & i & ".xls") Then
        Workbooks.Open Filename:=oFold & "\file" & i 'etc
    End If
Next i
    
Set oFSO = Nothing
Set oFold = Nothing
Set oFyls = Nothing
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244