List files in specific path

Sub FileList()

'*** List all files in specific path and pattern ***'
'*** in Excel file start from cell A1 of sheet1 ***'
'*** using GetFileList and IsArray function ***'

Dim p As String
Dim X As Variant


p = "C:\test\*.xls" 'select only *.xls pattern
X = GetFileList(p)

Select Case IsArray(X)
   Case True 'files found
      'MsgBox UBound(x)
      Sheets("Sheet1").Range("A:A").Clear
      For i = LBound(X) To UBound(X)
         Sheets("Sheet1").Cells(i, 1).Value = X(i)
      Next i
   Case False 'no files found
      MsgBox "No matching files"
End Select


End Sub

Check existing value in array

Function InSArray(ByRef vArray() As String, ByVal vValue As String) As Boolean

'***Return true, if found this value in array***'
'***Return false, if not found this value in array***'

Dim i As Long


For i = LBound(vArray) To UBound(vArray)
   If vArray(i) = vValue Then
      InSArray = True
      Exit Function
   End If
Next i

InSArray = False

End Function

Get user name function

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
---------------------------------------------------------

Public Function CurrentUser() As String
'*********************************************************
'* Function to get the current logged on user in windows *
'*********************************************************


Dim strBuff As String * 255
Dim X As Long

CurrentUser = ""
X = GetUserName(strBuff, Len(strBuff) - 1)
If X > 0 Then
   'Look for Null Character, usually included
   X = InStr(strBuff, vbNullChar)
   'Trim off buffered spaces too
   If X > 0 Then
      CurrentUser = UCase(Left$(strBuff, X - 1)) 'UCase is optional
   Else
      CurrentUser = UCase(Left$(strBuff, X))
   End If
End If


End Function

GetFileSize function

Function GetFileSize(folderspec As String) As Variant

'   Returns an array of file size that match FileSpec

    Dim fs, f, f1, fc, s

    Dim FileArray() As Variant

    Dim FileCount As Integer

    Dim filesize As String

    Set fs = CreateObject("Scripting.FileSystemObject")

    Set f = fs.GetFolder(folderspec)

    Set fc = f.Files

    FileCount = 0

    For Each f1 In fc

        s = f1.Size

        filesize = s

        FileCount = FileCount + 1

        ReDim Preserve FileArray(1 To FileCount)

        FileArray(FileCount) = filesize

        GetFileSize = FileArray

    Next

End Function

GetFileList function

Function GetFileList(filespec As String) As Variant
'***Function to get list of files in specific folder***'
' Returns an array of file names that match FileSpec

' If no matching files are found, it returns False

Dim FileArray() As Variant

Dim FileCount As Integer

Dim FileName As String

On Error GoTo NoFilesFound

FileCount = 0

FileName = Dir(filespec)

If FileName = "" Then GoTo NoFilesFound

' Loop until no more matching files are found

Do While FileName <> ""

   FileCount = FileCount + 1

   ReDim Preserve FileArray(1 To FileCount)

   FileArray(FileCount) = FileName

   FileName = Dir()

Loop

GetFileList = FileArray

Exit Function

' Error handler

NoFilesFound:

GetFileList = False

End Function

Excel VBA command (4)

Calculation

Command	Description
Application.Calculation = xlManual	Set calculation option to Manual (by F9)
Application.Calculation = xlAutomatic	Set calculation option to Automatic
Activesheet.Calculate	Calculate on activesheet only

CutCopyMode

Command	Description
Application.CutCopyMode=False	Clear data in clip board

Quit

Command	Description
Application.Quit	Quit Excel

ScreenUpdating

Command	Description
Application.ScreenUpdating = False	Result of commands between both will not display on screen
Application.ScreenUpdating = True

StatusBar

Command	Description
Application.DisplayStatusBar = False	Not display status bar
Application.DisplayStatusBar = True	Display status bar
Application.StatusBar = "Test"	Set to show "Test" on status bar

DisplayAlert

Command	Description
Application.DisplayAlerts = False	Not display any alert such as dialog box
Application.DisplayAlerts = True	Display alert

Excel VBA command (3)

Workbook

Command	Description
1. Workbooks.Open "Book1.xls"	Open "Book1.xls"
2. Workbooks.Open Sheets("Sheet1").Range("A1").Value	Open file by referring to file name in cell "A1" of "Sheet1"
3. Workbooks.Open Filename:= "C:\Book1.xls"	Open "C:\Book1.xls"
4. ThisWorkbook.Close	Close workbook which is coded
5. Workbooks("Book1.xls").Close	Close file "Book1.xls"
6. Workbooks(Range("A1").Value).Close	Close file by referring to file name in cell "A1"
7. ActiveWorkbook.Save	Save active workbook
8. Workbooks("Book1.xls").Save	Save "Book1.xls"
9. Workbooks(Range("A1").Value).Save	Save file by referring to file name in cell "A1"
10. ActiveWorkbook.SaveAs "C:\Book1.xls"	SaveAs active workbook to "C:\Book1.xls"
11. Workbooks("Book1.xls").SaveAs "C:\Book1.xls"	SaveAs "Book1.xls" to "C:\Book1.xls"
12. Workbooks(1).Close SaveChanges:=False	Close workbook index 1 without save
13. Workbooks(1).Close SaveChanges:=True	Close workbook index 1 with save
14. Kill "C:\Book1.xls"	Kill file "C:\Book1.xls"
15. Workbooks.Add	Add new workbook
16. ActiveWorkbook.Name	Return name of active workbook
17. Difference between ThisWorkBook and ActiveWorkBook	ThisWorkBook object refers to the workbook that the code is contained in ActiveWorkBook object refers to the workbook that is currently on top (Active) in Excel