Open if file less than 7 days old

[cg084]

Hello,

I am trying to write a macro that will check to see if a text file is less than 7 days old. If it is to open the file. The name of the file is fixed and is created though an automated query on a database. The file output is a pipe (|) delimited text file. The file is called. Cluster.txt.

Preferably I would like to test this before opening the file. But it is acceptable for the file to be open to test this.

Thank you for any assistance.

 

[sdraper]

Function ShowFileInfo(filespec)
Dim fso as FileSystemObject
dim f as Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFile(filespec)
ShowFileInfo = f.DateCreated
End Function
or change the last line to ShowFileInfo = f.DateLastModified

Sam

 

[sdraper]

p.s. add a reference to the Windows Script Host Object Model to your project.

Sam

 

[sdraper]

Sorry, my brain is malfunctioning this AM.
change
Dim fso as FileSystemObject
to
Dim fso as object
and the project reference won't be necessary as this code uses late binding.

Sam

 

[cg084]

Sam,

Cheers for the help. I am still a little stuck though. The function seems to do not a lot. I have entered the funtion in to my personal.xls spreadsheet that loads by default when excel opens. From this file i have a macro that will open the file i need from a web server

http://www.site.com/dump

for example. And the file is called Cluster.txt. Is there any way to code this in to the function to display the properties for that file.

Cheers CG084

 

[sdraper]

the calling code could be this:

Private sub YourMacro()
  dim strFilePath as string
  dim dtLastWeek as date
  dtLastweek = DateAdd("d", -7, Date)
  'Change C:\Test\Cluster.txt to your filepath
  strFilePath = "C:\Test\Cluster.txt"
  If ShowFileInfo(strFilePath) > dtLastWeek then
    'put your open code in here
  end if
end sub

Sam

 

[cg084]

Sam,

Greatly appreciate the help. It now works with a local or a named drive.

Your help is greatly appreciated.

There is still a problem in accessing the file on an internet server. My open file macro works with the same string but does not work in this method.

Due to the nature of the file. Here is a sample of what 1 address would be.

http://www.wwwgroup.co.uk/wdb/dump/cluster_based_aaaaaaa_aaaa.TXT

I have though about the case of the txt extension. Commas length of the path. IS there anything you can think of.

Thank you for your help.
CG084

 

[cg084]

Hi,

I just wanted to see if anyone had any thoughts with why a normal filepath works ok with the above but when i change to use a path to a webfile it does not.

Thank you.
CG084

 

[fumei]

To load a file from a web server you must use a WinHTTP.Request. You must also have a reference to WinHTTP.

See

http://msdn.microsoft.com/library/d...p/http/retrieving_data_using_visual_basic.asp

for how to use this to get a file from a web server. It loads it as binary.

Gerry