PDA

View Full Version : Solved: If Not fso.FileExists ?



omnibuster
05-20-2009, 12:47 PM
In Folder File excist, but code say: NOT Excist!
Why ? What i do wrong?:banghead:

Sub ContrBookS() '
Dim fso As Object, fld As Object, Fil As Object
Dim SubFolderName As String, FilePath
Dim LastRow As Long
Dim i As Integer
Dim WB As Workbook, fName As String
Sheets("Sheet1").Select
LastRow = Range("A1").End(xlDown).Row
For i = 1 To LastRow
Range("A" & i).Activate
fName = Range("A" & i) & ".xls"
Set fso = CreateObject("Scripting.FileSystemObject")
SubFolderName = ThisWorkbook.Path
FilePath = ActiveWorkbook.Path

Set fld = fso.GetFolder(SubFolderName)
' For Each Fil In fld.Files
If Right$(FilePath, 1) <> "\" Then FilePath = FilePath & "\"
' If fName <> ActiveWorkbook.Name Then
If Not fso.FileExists(fld & fName) Then
Range("A" & i).Offset(0, 1).Activate
ActiveCell.Value = 0
MsgBox fld & "\" & fName & " does not exist!", vbExclamation, "Source File Missing"

Else

If fso.FileExists(fld & fName) Then
' fso.MoveFile (fld & fName), fld
Set WB = Workbooks.Open(FilePath & fName, UpdateLinks:=0)
'Do...
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
End If

' Next Fil
Next i

End Sub

Kenneth Hobs
05-20-2009, 01:30 PM
Fld is a folder object, use fld.Path.

e.g.

Sub ContrBookS() '
Dim fso As Object, fld As Object
Dim s As String
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(ActiveWorkbook.Path)
s = fld.Path & "\" & ActiveWorkbook.Name

MsgBox s & vbLf & "Exists: " & fso.FileExists(s)
MsgBox s & vbLf & "Exists: " & (Dir(s) <> "")

s = ActiveWorkbook.FullName & "invalidparttomakeitnotexist"

MsgBox s & vbLf & "Exists: " & fso.FileExists(s)
MsgBox s & vbLf & "Exists: " & (Dir(s) <> "")

Set fld = Nothing
Set fso = Nothing
End Sub

omnibuster
05-21-2009, 10:36 PM
Sry Ken. Busy days.
Thanks for help Ken.

Sub ContrBook() '
Dim fso As Object, fld As Object, Fil As Object
Dim SubFolderName As String, FilePath
Dim LastRow As Long
Dim i As Integer
Dim WB As Workbook, fName As String
Dim s As String
Sheets("Sheet1").Select
LastRow = Range("A1").End(xlDown).Row
For i = 1 To LastRow
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(ActiveWorkbook.Path)
s = fld.Path & "\" & Range("A" & i) & ".xls"

If Right$(FilePath, 1) <> "\" Then FilePath = FilePath & "\"

If Not fso.FileExists(s) Then
Range("A" & i).Offset(0, 1).Activate
ActiveCell.Value = 0

Else

If fso.FileExists(s) Then
Range("A" & i).Offset(0, 1).Activate
ActiveCell.Value = 1
Set WB = Workbooks.Open(s, UpdateLinks:=0)
'Do...
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
End If
Next i
End Sub


Thanks Ken