Find if a file exists

[hjgoldstein]

Hi.
I am running a routine each day which saves a file in a folder with the format :

//sharepoint.xxxx.com/sites/Shared%20Documents/Reporting/Transaction%20and%20Utilisation%20Reports/yyyy-mm-dd_Filename.xlsx

where the yyyy-mm-dd is today's date.

I need to check first that the file does not exist.

I have tried using

If Dir (path filename) <> ""

using string variables to generate the path/filename combo

    Dim SptPath As String
        SptPath = "[URL unfurl="true"]http://sharepoint.xxxx.com/sites/Shared%20Documents/Reporting/Transaction%20and%20Utilisation%20Reports"[/URL]
    Dim SptFilename As String
        SptFilename = (Format(Date, "yyyy-mm-dd") & "_Utilisations.xlsx")
    Dim SptPathFilename As String
        SptPathFilename = SptPath & "/" & SptFilename

but keep getting errors.

I see that there is a "FileExists" method using a FileSystemObject (according to Ms help) and this would, I think, do the job but I can't find out more.

Any pointers would be appreciated.

Aspiring to mediocrity since 1957

 

[ClulessChris]

http://msdn.microsoft.com/en-us/library/x23stk5t(v=vs.84).aspx

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[hjgoldstein]

Thanks Chris. I am not sure how to do this still because every information source I look at says "File system object" is required but that's about all. Any tips on this aspect?

Aspiring to mediocrity since 1957

 

[combo]

Add a reference to "Microsoft Scripting Runtime" or directly to a scrrun.dll file.
There may be a problem with proper path name, is it the same as in saving process? If so, does the Dir function work?

combo

 

[Andrzejek]

In Excel 2010:
Tools - References... - (check) Microsoft Scripting Runtime - (click) OK

That will allow you to use File system object

I just do not know if that will allow you to select file from URL....

Have fun.

---- Andy

 

[hjgoldstein]

thanks chaps. All sorted - for now!

Aspiring to mediocrity since 1957