A few words of thanks would be greatly appreciated.

VBA - Getting all the information about a file


  • 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  
    '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  
       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  
         ' 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 If  
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  
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  


A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
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).