CheckFiles

kreshnr

Board Regular
Joined
Jan 31, 2007
Messages
70
Hi All,
I am using below mentioned Code for checking if certain file exist on Path.
Its working on my home PC (XP SP2 Excel 2003).
BUT its not working on my office PC (XP SP2 Excel 2003).

Can somebody tell me why its not working in my office PC ( or if its missing something, then from where can i download it.)

Sub CheckFiles()
Const strFolder = "C:\Documents and Settings\kreshnr\Desktop\Test"
Dim fso, msg, i
Dim rngData As Range

Set fso = CreateObject("Scripting.FileSystemObject")
Set rngData = Sheets("Sheet1").Range("A1")

With rngData
Do While .Offset(i, 0).Value <> ""
If (fso.FileExists(strFolder & .Offset(i, 0).Value & ". ")) Then
.Offset(i, 2).Value = "Yes"
Else
.Offset(i, 2).Value = "No"
End If
i = i + 1
Loop
End With
End Sub

Regards

Kreshnr
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
BUT its not working on my office PC (XP SP2 Excel 2003).
When something doesn't work, it is always helpful if you can give a bit more information than that, and try to detail in what way it doesn't work.

For example, does it...

- Give an error message? (If so what message and what line of code causes the error)

- Tell you the file exists when it doesn't exist?

- Tell you the file doesn't exist when it does exist?

- Do nothing?

- Do something else?

I haven't studied the code closely at this stage, but is the path being used exactly the same on each computer?
(That is: C:\Documents and Settings\kreshnr\Desktop\Test)
 
Upvote 0
On closer inspection, assuming 'Test' is a folder, it looks to me like this line needs an extra backslash (so I'm not sure how that worked on any computer?):
Const strFolder = "C:\Documents and Settings\kreshnr\Desktop\Test\"

I'm also wondering what the red part in this line is supposed to be doing?
If (fso.FileExists(strFolder & .Offset(i, 0).Value & ". ")) Then

A few examples of exactly what is in some of the column A cells and the full names of the files in the 'Test' folder might also help.
 
Last edited:
Upvote 0
I thought occurs, my work force our personal profile to a different partion, still looks like desktop but on E and NOT C, also they make C Read Only
 
Upvote 0
Thanx Peter and Mole,

I have tweaked the code but still it works at my home PC but not at office PC :(

Sub CheckFiles()
Const strFolder = "D:\Test\"
Dim fso, msg, i
Dim rngData As Range

Set fso = CreateObject("Scripting.FileSystemObject")
Set rngData = Sheets("Sheet1").Range("A1")

With rngData
Do While .Offset(i, 0).Value <> ""
If (fso.FileExists(strFolder & .Offset(i, 0).Value & ".jpeg")) Then
.Offset(i, 2).Value = "Yes"
Else
.Offset(i, 2).Value = "No"
End If
i = i + 1
Loop
End With
End Sub

I m trying to fetch the file existence on the given path, and its not giving me any kind of information or error code.
its just running fine and giving me "Nope" result on Range B1:B2

I Have File names on Range A1:A2 are jon.jpeg and joe so it should return "Yes" and "Nope"

in B1:B2 which is offset with A1:A2 on (i, 1)

is this a problem with Scripting.FileSystemObject not installed or not working , if so how/from where can i download and install it.

Regards

Kreshnr
 
Upvote 0
Yes, fso may not be installed or your administrators have disable WScript. It may also be that it is working and it is working as designed, the file does not exist.

To skip fso:
Code:
Sub CheckFiles()
    Const strFolder = "D:\Test\"
    Dim msg, i
    Dim rngData As Range
    
    Set rngData = Sheets("Sheet1").Range("A1")
    
    With rngData
        Do While .Offset(i, 0).Value <> ""
            If (Dir(strFolder & .Offset(i, 0).Value & ".jpeg") <> "") Then
                .Offset(i, 2).Value = "Yes"
                Else
                .Offset(i, 2).Value = "No"
            End If
            i = i + 1
        Loop
    End With
End Sub
 
Last edited:
Upvote 0
Kenneth thank you so much its working.

Kenneth i want to ask you few more things on this

Can i modified this code to fetch information on existence of folders and sub folders and files within them on given path (As now this is only checking for files from given folder and path).

Many Thanx and regards

Kreshnr
 
Upvote 0
Yes. Of course if the file exists then the folders must exist. I just need to know exactly what you want. Dir() has a 2nd parameter, vbDirectory, which can be used in the same way to verify that a Directory exists in the same way that I did for a file.

e.g.
Code:
debug.print "Exists?", Dir(strFolder, vbDirectory)<>""
 
Upvote 0
Hi Kenneth,
I want to fetch information on existence/nonexistence of folders,sub folders and files within them on given path (As now this is only checking for files from given folder and path) as against given range with names of folders/sub folders/files on given path and the this returns on same row with offset column.

Say range A1:A10 names as a,b,c,d and so on.. so the corresponding return result should be on their basis of existence/nonexistence "yes" or "no" whatever applicable.

As its working very fine with files right now. but not with folders and sub folders and files within them on given path.

Hope i am clear to you.

Regards

Kreshnr

(Please Paste the entire code, as i am noob in VB. :)
 
Upvote 0
No, it is not clear.

You specifically set the folder with
Code:
strFolder = "D:\Test\"

You can test for that folder by:
Code:
Sub FolderEists()
    Dim strFolder As String
    strFolder = "D:\Test\"
    'strFolder = ActiveWorkbook.Path 'Always exist.
    On Error GoTo DriveError
    If Dir(strFolder, vbDirectory) <> "" Then
        MsgBox strFolder & " exists."
        Else
DriveError:
        MsgBox strFolder & " does not exist."
    End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top