VBA

From HaFrWiki
Jump to: navigation, search

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:

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

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

Authors

VBA References