File manipulation with VBA

Posted on 2007-11-30
Last Modified: 2008-02-01

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.
Question by:alateos
  • 3
LVL 38

Expert Comment

ID: 20383478
1.  Use SetAttr
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...
LVL 38

Expert Comment

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:


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


Author Comment

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
LVL 38

Accepted Solution

PaulHews earned 250 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


    If lngCount > 1 Then

        ReDim Preserve strFile(1 To lngCount - 1)


        ReDim strFile(-1 To -1)   'Avoid errors when no files.  Send back a one element blank array.

    End If

    fGetFiles = strFile

    Exit Function



    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


End Sub

Open in new window


Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Not needed 13 94
Modifying Conditional Format from VBA code 3 49
How to measure sizes and angles in scanned images ? 3 53
Advice on Xojo as a development tool over VB. 4 39
This is an explanation of a simple data model to help parse a JSON feed
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

920 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now