VBA - Getting all the information about a file

December 2016




Preliminaries

  • Open a new workbook
  • Add a module

In module


' Declare variables for wizard.  
Public balloon1 As Balloon  
Public balloon2 As Balloon  
Public balloon3 As Balloon  
' It is possible to use a table instead of  
'multiple statements, I chose the multiple option for   
'the code to be more explicit   
'Public BalloonMultipl(3) as balloon  
'  
Public Title As String  
Public Message As String  

Sub openMessage()  
      'Declare variables.  
      Dim WizardName As String  
      Dim IsVisible As Boolean  
      Dim Result As Byte  
    ' Set errors as clear  
    On Error Resume Next  
    Err.Clear  
    'Memorize current name of Wizard.  
    WizardName = Wizard.Name  
    'If the Wizard is not visible, set it as visible  
    If Wizard.Visible = False Then  
       Wizard.Visible = True  
       IsVisible = False  
    Else  
       IsVisible = True  
    End If  
    ' create Wizard balloon.  
    Set balloon2 = Wizard.NewBalloon  
      With balloon2  
         ' put the title and questions.  
         .Heading = Title  
         .Text = Message  
         ' Sets the properties of the Wizard.  
         .BalloonType = msoBalloonTypeButtons  
         ' the modal mode, as default.  
         .Mode = msoModeModal  
         'assign Canceled option to button, OK is default.  
         .Button = msoButtonSetOK  
      End With  
      ' wait for selection  
      Do  
         ' Selection done  
         Result = balloon2.Show  
         ' If button is selected, end macro.  
         If Err <> 0 Then  
            If IsVisible = False Then  
               Wizard.Visible = False  
            End If  
            End  
         End If  
      Loop  
End Sub  
Sub DisplayInfoAccessFile (specfile)   
'We need that the workbook is already on the hard disk.  
'----------------------------------------------------  
    Dim fs, f, s  
    Set fs = CreateObject("Scripting.FileSystemObject")  
    Set f = fs.GetFile(specfile)  
    s = UCase(specfile) & vbCrLf  
    s = s & "Created the : " & f.DateCreated & vbCrLf  
    s = s & "Last acess: " & f.DateLastAccessed & vbCrLf  
    s = s & "Last modification : " & f.DateLastModified & vbCrLf  
    s = s & "Size " & f.Size & " bytes." & vbCrLf  
    s = s & "Drive " & f.Drive & vbCrLf  
    s = s & "Directory " & f.ParentFolder  
    Title = "Infos about the file: " & specfile  
    Message = s  
    openMessage  
End Sub  

In sheet1 module


Private Sub Worksheet_Activate()  
    Range("B5").Value = "display info about the file"  
    With ActiveSheet.Range("B5").Font  
        .Name = "Arial"  
        .Size = 16  
        .ColorIndex = 5  
        .Bold = True  
    End With  
    Columns("B").ColumnWidth = 48  

End Sub  

Private Sub Worksheet_SelectionChange(ByVal Target As Range)  
Dim a$, b$  

a$ = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name  
b$ = ActiveWorkbook.Name  
    If ActiveCell.Address = "$B$5" Then  
        AfficheInfoAccesFile (ActiveWorkbook.Name)  
    End If  
End Sub  



Related :

This document entitled « VBA - Getting all the information about a file » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.