Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for existence of a file
How can I check for the existence of a file (I can provide the path and the
file name to look for)? Once I have checked the existence of the file, how can I look up various attributes of the file, e.g. creation date, last modified date, last accessdate, etc. many thanks in anticipation |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for existence of a file
Hi DJS,
Try something like '============= Public Sub TesterA01() Dim FSO As Object Dim oFile As Object Dim sStr As String Const sPath As String = "C:\Data\myBook.xls" '<<=== CHANGE Set FSO = CreateObject("Scripting.FileSystemObject") Set oFile = FSO.GetFile(sPath) With oFile sStr = "Date Created " & .DateCreated _ & vbNewLine _ & "Last Accessed " & .DateLastAccessed _ & vbNewLine & "Last Modified " _ & .DateLastModified End With MsgBox Prompt:=sStr, Title:=sPath End Sub '<<============= --- Regards, Norman "DKS" wrote in message ... How can I check for the existence of a file (I can provide the path and the file name to look for)? Once I have checked the existence of the file, how can I look up various attributes of the file, e.g. creation date, last modified date, last accessdate, etc. many thanks in anticipation |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for existence of a file
Hi DKS,
Better would be: '============= Public Sub Tester() Dim FSO As Object Dim oFile As Object Dim sStr As String Const sPath As String = "C:\Data\MyFile.xls" '<<=== CHANGE Set FSO = CreateObject("Scripting.FileSystemObject") On Error Resume Next Set oFile = FSO.GetFile(sPath) On Error GoTo 0 If oFile Is Nothing Then sStr = "The file " & sPath & " was not found" GoTo XIT End If With oFile sStr = "Date Created " & .DateCreated _ & vbNewLine _ & "Last Accessed " & .DateLastAccessed _ & vbNewLine & "Last Modified " _ & .DateLastModified End With XIT: MsgBox Prompt:=sStr, Title:=sPath End Sub '<<============= --- Regards, Norman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for existence of a file
Const sFilename As String = "C:\projects\Ref 1234.0102 report.xls"
Dim iFilenum As Long Dim iErr As Long On Error Resume Next iFilenum = FreeFile() Open sFilename For Input Lock Read As #iFilenum Close iFilenum iErr = Err On Error GoTo 0 If iErr = 53 Then MsgBox "File not found" End If -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DKS" wrote in message ... How can I check for the existence of a file (I can provide the path and the file name to look for)? Once I have checked the existence of the file, how can I look up various attributes of the file, e.g. creation date, last modified date, last accessdate, etc. many thanks in anticipation |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for existence of a file
You can also use FSO.FileExists to check if a file exists:
Public Sub Tester() Dim FSO As Object Dim oFile As Object Dim sStr As String Const sPath As String = "C:\Data\MyFile.xls" '<<=== CHANGE Set FSO = CreateObject("Scripting.FileSystemObject") If Not FSO.FileExists(sPath) Then sStr = "The file " & sPath & " was not found" GoTo XIT End If Set oFile = FSO.GetFile(sPath) With oFile sStr = "Date Created " & .DateCreated _ & vbNewLine _ & "Last Accessed " & .DateLastAccessed _ & vbNewLine & "Last Modified " _ & .DateLastModified End With XIT: MsgBox Prompt:=sStr, Title:=sPath End Sub Also, I have this to get file properties: Sub FileProperties() strComputer = "." Set objWMIService = GetObject("winmgmts:" _ & "{impersonationLevel=impersonate}!\\" _ & strComputer & "\root\cimv2") Set colFiles = objWMIService.ExecQuery _ ("Select * from CIM_Datafile Where name = 'c:\\Data\\test.xls'") For Each objFile In colFiles Debug.Print "Access mask: " & objFile.AccessMask Debug.Print "Archive: " & objFile.Archive Debug.Print "Compressed: " & objFile.Compressed Debug.Print "Compression method: " & objFile.CompressionMethod Debug.Print "Creation date: " & objFile.CreationDate Debug.Print "Computer system name: " & objFile.CSName Debug.Print "Drive: " & objFile.Drive Debug.Print "8.3 file name: " & objFile.EightDotThreeFileName Debug.Print "Encrypted: " & objFile.Encrypted Debug.Print "Encryption method: " & objFile.EncryptionMethod Debug.Print "Extension: " & objFile.Extension Debug.Print "File name: " & objFile.Filename Debug.Print "File size: " & objFile.FileSize Debug.Print "File type: " & objFile.FileType Debug.Print "File system name: " & objFile.FSName Debug.Print "Hidden: " & objFile.Hidden Debug.Print "Last accessed: " & objFile.LastAccessed Debug.Print "Last modified: " & objFile.LastModified Debug.Print "Manufacturer: " & objFile.Manufacturer Debug.Print "Name: " & objFile.Name Debug.Print "Path: " & objFile.Path Debug.Print "Readable: " & objFile.Readable Debug.Print "System: " & objFile.System Debug.Print "Version: " & objFile.Version Debug.Print "Writeable: " & objFile.Writeable Next End Sub Hope that helps -- urkec "Norman Jones" wrote: Hi DKS, Better would be: '============= Public Sub Tester() Dim FSO As Object Dim oFile As Object Dim sStr As String Const sPath As String = "C:\Data\MyFile.xls" '<<=== CHANGE Set FSO = CreateObject("Scripting.FileSystemObject") On Error Resume Next Set oFile = FSO.GetFile(sPath) On Error GoTo 0 If oFile Is Nothing Then sStr = "The file " & sPath & " was not found" GoTo XIT End If With oFile sStr = "Date Created " & .DateCreated _ & vbNewLine _ & "Last Accessed " & .DateLastAccessed _ & vbNewLine & "Last Modified " _ & .DateLastModified End With XIT: MsgBox Prompt:=sStr, Title:=sPath End Sub '<<============= --- Regards, Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check for existence | Excel Discussion (Misc queries) | |||
VBA to check for existence of a DSN | Excel Programming | |||
Check File Existence | Excel Programming | |||
How do I check for existence of a worksheet? | Excel Worksheet Functions | |||
Code to check existence | Excel Programming |