CheckFiles

kreshnr said:

BUT its not working on my office PC (XP SP2 Excel 2003).

Click to expand...

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)

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.

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

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

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:

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

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.

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.

No, it is not clear.

You specifically set the folder with

You can test for that folder by: