Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

File manipulation with VBA

Posted on 2007-11-30
4
Medium Priority
?
837 Views
Last Modified: 2008-02-01
Hi,

I would like to be able to change file attributes using vba. Also, I would like to see creation date and last accessed date.

Here's an example of what I'd like to do with my code:
1) Change attribute of a file to hidden, read-only, or archive
2) Get the last access date of the file or last modified date.

I would like this code to run for all files in a folder X.
0
Comment
Question by:alateos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 38

Expert Comment

by:PaulHews
ID: 20383478
1.  Use SetAttr
example:
SetAttr Path, vbHidden Or vbSystem

2. Use FileDateTime, which returns date created or modified

Dim dt as Date
dt = FileDateTime(Path)

For last modified date, you should consider the FileSystemObject...
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 20383499
>For last modified date, you should consider the FileSystemObject...

Meant to be last access date...  FileSystemObject can get all the different types of file date/time stamps:

Example:

Dim fso, file, createdate
Set fso= CreateObject("Scripting.FileSystemObject")
Set file= fso.GetFile("C:\temp\test.txt")
msgbox "Created " &  file.DateCreated
msgbox "Modified " & file.DateLastModified
msgbox "Last accessed " & file.DateLastAccessed

0
 

Author Comment

by:alateos
ID: 20383527
thanks for your reply Paul... could you please show me a full practical example... where the app looks through a folder... also please show how one can set the attribute in the example
0
 
LVL 38

Accepted Solution

by:
PaulHews earned 1000 total points
ID: 20383608
Here's an example without the FileSystemObject using pure VB functions.  Let me know if you actually need the access date.
Public Function fGetFiles(ByVal strSpec As String, ByVal strPath As String) As Variant
    Dim strFile() As String, strTemp As String
    Dim lngCount As Long, lngNum As Long
    Const Chunk = 200
    On Error GoTo errGetFiles
    lngNum = Chunk
    ReDim strFile(1 To lngNum)
    If Right$(strPath, 1) <> "\" Then strPath = strPath & "\"
    lngCount = 1
    strTemp = Dir(strPath & strSpec)
    Do While Len(strTemp) > 0
        If lngCount > UBound(strFile) Then
            lngNum = lngNum + Chunk
            ReDim Preserve strFile(1 To lngNum)
        End If
        strFile(lngCount) = strTemp
        strTemp = Dir
        lngCount = lngCount + 1
    Loop
    If lngCount > 1 Then
        ReDim Preserve strFile(1 To lngCount - 1)
    Else
        ReDim strFile(-1 To -1)   'Avoid errors when no files.  Send back a one element blank array.
    End If
    fGetFiles = strFile
    Exit Function
errGetFiles:
    
    MsgBox Err.Description, vbCritical, "fGetFiles"
 
End Function
 
Private Sub Command1_Click()
    Dim strFiles() As String
    Dim i As Long
    Dim strPath As String
    
    strPath = "C:\temp\"
    
    strFiles = fGetFiles("*.*", strPath)
    For i = 1 To UBound(strFiles)
        Debug.Print strFiles(i) & ": " & FileDateTime(strPath & strFiles(i))
        SetAttr strPath & strFiles(i), vbReadOnly Or vbHidden
    Next
End Sub

Open in new window

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Suggested Courses

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question