VBA - Getting all the information about a file

October 2017




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  


Published by jak58. Latest update on January 2, 2012 at 10:28 AM by jak58.
This document, titled "VBA - Getting all the information about a file," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).