VBA Code - if file doesnt exist move to next file

[katrina]

Hi There

I have a txt files in a folder whos contents i want to append and put
in an excel file. However I the code i have stops if a file doesnt
exist. I need to know what the code is so that I can move to the next
file. Eg I have a function that creates a text file if a particular
scan of a bar code can be found in a system If the barcode doesnt
exist it doesnt create a txt file. This is the code that has been
written unfortunately the author is not available to assist i need
help to do this. Any assistance is appreciated

ub processtxtfiles()
pth = ActiveWorkbook.Path
Open pth & "\manifests_t.txt" For Output As #1
Application.ScreenUpdating = False
i = 2
While Sheet1.Cells(i, 1) <> "0"

'open the file
Workbooks.OpenText pth & "/" & Sheet1.Cells(i, 1) & ".txt", , , ,
xlTextQualifierNone, , True

'get the records
j = 6
While Cells(j, 2) <> ""
'If Cells(j, 2) = "Delivered" Then
' Print #1, Sheet1.Cells(i, 1) & Chr(9) & Cells(j, 1) &
Chr(9) & Cells(j, 2) & Chr(9) & Cells(j, 3)
'ElseIf Cells(j, 2) = "Carded - awaiting collection" Then
' Print #1, Sheet1.Cells(i, 1) & Chr(9) & Cells(j, 1) &
Chr(9) & Cells(j, 2) & Chr(9) & Cells(j, 3)
'ElseIf Cells(j + 1, 2) = "" Then
' Print #1, Sheet1.Cells(i, 1) & Chr(9) & Cells(j, 1) &
Chr(9) & Cells(j, 2) & Chr(9) & Cells(j, 3)
'End If
Print #1, Sheet1.Cells(i, 1) & Chr(9) & Cells(j, 1) & Chr(9) &
Cells(j, 2) & Chr(9) & Cells(j, 3)
j = j + 1
Wend

 

[Tim Williams]

Sub processtxtfiles()

Dim sPath, pth, i, j
pth = ActiveWorkbook.Path
Open pth & "\manifests_t.txt" For Output As #1
Application.ScreenUpdating = False
i = 2
While Sheet1.Cells(i, 1) < "0"

sPath = pth & "/" & Sheet1.Cells(i, 1).Value & ".txt"

If Dir(sPath) <> "" Then
'open the file
Workbooks.OpenText sPath, , , , xlTextQualifierNone, , True

'get the records
j = 6
While Cells(j, 2) < ""
'do stuff
j = j + 1
Wend

End If
i = i + 1
Wend

End Sub

Tim

 

[Joel]

Sub processtxtfiles()


pth = ActiveWorkbook.Path
Open pth & "\manifests_t.txt" For Output As #1
Application.ScreenUpdating = False
i = 2
While Sheet1.Cells(i, 1) <> "0"

FName = pth & "/" & Sheet1.Cells(i, 1) & ".txt"
If Dir(FName) <> "" Then
'open the file
Workbooks.OpenText FName, _
TextQualifier:=xlTextQualifierNone, _
Tab:=True

'get the records
j = 6
While Cells(j, 2) <> ""
'If Cells(j, 2) = "Delivered" Then
' Print #1, Sheet1.Cells(i, 1) & Chr(9) & Cells(j, 1) &
'Chr (9) & Cells(j, 2) & Chr(9) & Cells(j, 3)
'ElseIf Cells(j, 2) = "Carded - awaiting collection" Then
' Print #1, Sheet1.Cells(i, 1) & Chr(9) & Cells(j, 1) &
'Chr (9) & Cells(j, 2) & Chr(9) & Cells(j, 3)
'ElseIf Cells(j + 1, 2) = "" Then
' Print #1, Sheet1.Cells(i, 1) & Chr(9) & Cells(j, 1) &
'Chr (9) & Cells(j, 2) & Chr(9) & Cells(j, 3)
'End If
Print #1, Sheet1.Cells(i, 1) & Chr(9) & Cells(j, 1) & Chr(9) & _
Cells(j, 2) & Chr(9) & Cells(j, 3)
j = j + 1
Wend
End If
Wend
End Sub