VBA

Visual Basic for Applications is used in Microsoft Office. This page is a Cheat Sheet.

ADO

The starting point is ADO Programmers Reference, with the ADO API Reference. On those pages there are links to:

• OpenSchema

FSO

File System Object has many methods:

• BuildPath
• CopyFile
• CopyFolder
• CreateFolder
• CreateTextFile
• DeleteFile
• DeleteFolder
• DriveExists
• FileExists
• FolderExists
• GetAbsolutePathName
• GetBaseName
• GetDrive
• GetDriveName
• GetExtensionName
• GetFile
• GetFileVersion
• GetFileName
• GetFolder
• GetParentFolderName
• GetSpecialFolder
• GetStandardStream
• GetTempName
• MoveFile
• MoveFolder
• OpenTextFile

Drive object

Provides access to the properties of a particular disk drive or network share.
Drive has no methods.

Attributes
Attribute		Example
AvailableSpace	object.AvailableSpace
DriveLetter	object.DriveLetter
DriveType	object.DriveType	Set fso = CreateObject("Scripting.FileSystemObject") Set d = fso.GetDrive(drvpath) Select Case d.DriveType Case 0: t = "Unknown" Case 1: t = "Removable" Case 2: t = "Fixed" Case 3: t = "Network" Case 4: t = "CD-ROM" Case 5: t = "RAM Disk" End Select
FileSystem	object.FileSystem The object is always a Drive object. Available return types include FAT, NTFS, and CDFS.
FreeSpace	object.FreeSpace
IsReady	object.isReady
Path	object.Path
RootFolder	object.RootFolder
SerialNumber	object.SerialNumber
ShareName	object.ShareName
TotalSize	object.TotalSize
VolumeName	0bject.VolumeName = [newname] object Required. Always the name of a Drive object. newname Optional. If provided, newname is the new name of the specified object.

File object

Provides access to all the properties of a file.

Methods
Method	Object and Parameters	Example
Copy	object Required. Always the name of a File or Folder object. destination Required. Destination where the file or folder is to be copied. Wildcard characters are not allowed. overwrite Optional. Boolean value that is True (default) if existing files or folders are to be overwritten; False if they are not.	Dim fso, MyFile Set fso = CreateObject("Scripting.FileSystemObject") Set MyFile = fso.CreateTextFile("c:\testfile.txt", True) MyFile.WriteLine("This is a test.") MyFile.Close Set MyFile = fso.GetFile("c:\testfile.txt") MyFile.Copy ("c:\windows\desktop\test2.txt")
Delete	object Required. Always the name of a File or Folder object. force Optional. Boolean value that is True if files or folders with the read-only attribute set are to be deleted; False (default) if they are not.	Dim fso, MyFile Set fso = CreateObject("Scripting.FileSystemObject") Set MyFile = fso.CreateTextFile("c:\testfile.txt", True) MyFile.WriteLine("This is a test.") MyFile.Close Set MyFile = fso.GetFile("c:\testfile.txt") MyFile.Delete
Move	object Required. Always the name of a File or Folder object. destination Required. Destination where the file or folder is to be moved. Wildcard characters are not allowed.
OpenAsTextStream	object Required. Always the name of a File object. iomode Optional. Indicates input/output mode. Can be one of three constants: ForReading, ForWriting, or ForAppending. format Optional. One of three Tristate values used to indicate the format of the opened file. If omitted, the file is opened as ASCII. Format can be TristateUseDefault (System Default), TristateTrue (Unicode), TristateFalse (ASCII).	fso = new ActiveXObject("Scripting.FileSystemObject"); fso.CreateTextFile( "test1.txt" ); f = fso.GetFile("test1.txt"); ts = f.OpenAsTextStream(ForWriting, TristateUseDefault); ts.Write( "Hello World" ); ts.Close( ); ts = f.OpenAsTextStream(ForReading, TristateUseDefault); s = ts.ReadLine( ); ts.Close( );

Attributes
Attribute		Example
Attributes	object.Attributes [= newattributes] object Required. Always the name of a File or Folder object. newattributes Optional. If provided, newattributes is the new value for the attributes of the specified object.	Normal 0 Normal file. No attributes are set. ReadOnly 1 Read-only file. Attribute is read/write. Hidden 2 Hidden file. Attribute is read/write. System 4 System file. Attribute is read/write. Volume 8 Disk drive volume label. Attribute is read-only. Directory 16 Folder or directory. Attribute is read-only. Archive 32 File has changed since last backup. Attribute is read/write. Alias 1024 Link or shortcut. Attribute is read-only. Compressed 2048 Compressed file. Attribute is read-only.
DateCreated	object.DataCreated
DateLastAccessed	object.DataLastAccessed
DateLastModified	object.DataLastModified
Drive	object.Drive
Name	object.Name [= newname] object Required. Always the name of a File or Folder object. newname Optional. If provided, newname is the new name of the specified object.
ParentFolder	object.ParentFolder
Path	object.Path
ShortName	object.ShortName
ShortPath	object.ShortPath
Size	object.Size
Type	object.Type

Example

The script below is for educational purposes only. It is working example of a registry change for restoring a minimized explorer bar. The script is taken from Kellys Korner.

'xp_taskbar_desktop_fixall.vbs - Repairs the Taskbar when minimized programs don't show.
'© Kelly Theriot and Doug Knox - 8/22/2003
Set WSHShell = WScript.CreateObject("WScript.Shell")
    Message = "Repairs the taskbar when minimized programs don't show" & vbCR & vbCR
    Message = Message & "To work correctly, the script will close" & vbCR
    Message = Message & "and restart the Windows Explorer shell." & vbCR
    Message = Message & "This will not harm your system." & vbCR & vbCR
    Message = Message & "Continue?"
    X = MsgBox(Message, vbYesNo, "Notice")
    If X = 6 Then 
        On Error Resume Next
        WshShell.RegDelete "HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\StuckRects2\"
        WshShell.RegDelete "HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\StreamMRU\"
        WshShell.RegDelete "HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\Streams\Desktop\"
        WshShell.RegDelete "HKCU\Software\Microsoft\Internet Explorer\Explorer Bars\"& _
                           "{32683183-48a0-441b-a342-7c2a440a9478}\BarSize"
        P1 = "HKCU\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer\"
        WshShell.RegWrite p1 & "NoBandCustomize", 0, "REG_DWORD"
        WshShell.RegWrite p1 & "NoMovingBands", 0, "REG_DWORD"
        WshShell.RegWrite p1 & "NoCloseDragDropBands", 0, "REG_DWORD"
        WshShell.RegWrite p1 & "NoSetTaskbar", 0, "REG_DWORD"
        WshShell.RegWrite p1 & "NoToolbarsOnTaskbar", 0, "REG_DWORD"
        WshShell.RegWrite p1 & "NoSaveSettings",0,"REG_DWORD"
        WshShell.RegWrite p1 & "NoToolbarsOnTaskbar", 0, "REG_DWORD"
        WshShell.RegWrite p1 & "NoSetTaskbar",0,"REG_DWORD"
        WshShell.RegWrite p1 & "NoActiveDesktop",0,"REG_DWORD"
        WshShell.RegWrite p1 & "ClassicShell",0,"REG_DWORD"
        p1 = "HKCU\Software\Microsoft\Windows\CurrentVersion\Group Policy Objects\"& _
             "LocalUser\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer\"
        WshShell.RegWrite p1 & "NoCloseDragDropBands", 0, "REG_DWORD"
        WshShell.RegDelete p1 & "NoMovingBands"
        p1 = "HKLM\Software\Microsoft\Windows NT\CurrentVersion\Winlogon\Shell"
        WshShell.RegWrite p1, "explorer.exe", "REG_SZ"
        p1 = "HKCU\Software\Microsoft\Internet Explorer\Explorer Bars\{32683183-48a0-441b-a342-7c2a440a9478}\"
        WshShell.RegDelete p1 & "BarSize"
        WshShell.RegWrite p1, "Media Band", "REG_SZ"
        On Error Goto 0
        For Each Process in GetObject("winmgmts:"). _
            ExecQuery ("select * from Win32_Process where name='explorer.exe'")
            Process.terminate(0)
        Next
        MsgBox "Finished." & vbcr & vbcr & "© Kelly Theriot and Doug Knox", 4096, "Done"
    Else
        MsgBox "No changes were made to your system." & vbcr & vbcr & "© Kelly Theriot and Doug Knox", 4096, "User Cancelled"
    End If
' End of the script    

See also

• Microsoft FSO website
• VBA and DOM
• VBA and Excel, Special examples for MS Excel.
• VBA and Word, Special examples for MS Word.
• W3School VBA reference functions

See also the compiled help files in office (*.chm).

Authors

• Allen Browne, Date pickers and more example code
• LeBans, More examples and sample code.

VBA References

• ozgrid, Excel templates and VBA Example code.
• VBA Express, vba examples