Microsoft Excel

Excel und VBA: Formeln, Programmierung, Lösungen

Die Excel/VBA-Beispiele

Thema

Speicherinformationen ermitteln

Gruppe

System

Problem

Die Speicherinformationen auszuwählender Laufwerke werden ermittelt.

Lösung
Geben Sie den nachfolgenden Code in ein Standardmodul ein und weisen Sie ihn einer Schaltfläche zu.

StandardModule: Modul1

Private Declare Function GetDiskFreeSpace Lib "kernel32.dll" Alias _
  "GetDiskFreeSpaceA" (ByVal lpRootPathName As String, _
  lpSectorsPerCluster As Long, lpBytesPerSector As Long, _
  lpNumberOfFreeClusters As Long, lpTotalNumberOfClusters As Long) As Long
Private Declare Function GetDiskFreeSpaceEx Lib "kernel32.dll" Alias _
  "GetDiskFreeSpaceExA" (ByVal lpDirectoryName As String, _
  lpFreeBytesAvailableToCaller As ULARGE_INTEGER, _
  lpTotalNumberOfBytes As ULARGE_INTEGER, _
  lpTotalNumberOfFreeBytes As ULARGE_INTEGER) As Long
Private Declare Function GetVersionEx Lib "kernel32.dll" Alias "GetVersionExA" _
  (lpVersionInformation As OSVERSIONINFO) As Long
Private Declare Function GetLogicalDriveStrings Lib "kernel32.dll" Alias _
  "GetLogicalDriveStringsA" (ByVal nBufferLength As Long, _
  ByVal lpBuffer As String) As Long
Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" _
  (Destination As Any, Source As Any, ByVal Length As Long)

Private Type ULARGE_INTEGER
  LowPart As Long
  HighPart As Long
End Type

Private Type OSVERSIONINFO
  dwOSVersionInfoSize As Long
  dwMajorVersion As Long
  dwMinorVersion As Long
  dwBuildNumber As Long
  dwPlatformId As Long
  szCSDVersion As String * 128
End Type

Private Const VER_PLATFORM_WIN32s = 0 ' Win 3.1 mit 32 Bit-Erweiterung
Private Const VER_PLATFORM_WIN32_WINDOWS = 1 ' Win NT/2000
Private Const VER_PLATFORM_WIN32_NT = 2 ' Win 9x

Sub GetInfos()
  Dim Retval As Long, OS As OSVERSIONINFO, InputRet As String, Buffer As String
  Buffer = Space(256)
  Retval = GetLogicalDriveStrings(Len(Buffer), Buffer)
  Buffer = Left$(Buffer, Retval)
  Buffer = Replace(Buffer, "\" & vbNullChar, ", ")
  InputRet = InputBox("Bitteg geben sie einen Laufwerksbuchstaben ein (" & _
  Buffer & ").", "Speicher Information ermitteln", "C:\")
  If InputRet = "" Or Len(InputRet) > 3 Then
    MsgBox "Ungültige eingabe, Bitte geben sie nur Laufwerksbuchstaben, Doppelpunkt und Backslash ein"
    Exit Sub
  End If
  OS.dwOSVersionInfoSize = Len(OS)
  Retval = GetVersionEx(OS)
  If Retval = 0 Then
    MsgBox "Betriebssystem Version konnte nicht ermittelt werden."
    Exit Sub
  End If
  With OS
    Select Case .dwPlatformId
    Case VER_PLATFORM_WIN32_WINDOWS
      If InStr(1, OS.szCSDVersion, "B") <> 0 Or .dwMinorVersion > 0 Then
        Call GetNewFreespace(InputRet)
      Else
        Call GetOldFreespace(InputRet)
      End If
    Case VER_PLATFORM_WIN32_NT
      If .dwMajorVersion >= 4 Then
        Call GetNewFreespace(InputRet)
      Else
        Call GetOldFreespace(InputRet)
      End If
    Case Else
      MsgBox "Windows 3.x Version kann nicht ermittelt werden."
    End Select
  End With
End Sub

Private Function GetOldFreespace(ByVal Root As String)
  Dim Retval As Long
  Dim SC As Long, BC As Long, FC As Long, TC As Long
  Dim TSpace As Long, FSpace As Long, USpace As Long
  Retval = GetDiskFreeSpace(Root, SC, BC, FC, TC)
  TSpace = TC * SC * BC / 1024 / 1024
  FSpace = FC * SC * BC / 1024 / 1024
  USpace = TSpace - FSpace
  MsgBox "Gesamt: " & Format$(TSpace, "##.00 MB") & vbCrLf & _
    "Belegt: " & Format$(USpace, "##.00 MB") & vbCrLf & _
    "Frei: " & Format$(FSpace, "##.00 MB") & vbCrLf _
    , , "Festplattenspeicher Laufwerk """ & Root & """"
End Function

Private Function GetNewFreespace(ByVal Root As String)
  Dim Retval As Long
  Dim CBytes As ULARGE_INTEGER, TBytes As ULARGE_INTEGER, FBytes As ULARGE_INTEGER
  Dim CB  As Currency, TB As Currency, FB As Currency, UB As Currency
  Retval = GetDiskFreeSpaceEx(Root, CBytes, TBytes, FBytes)
  CopyMemory CB, CBytes, 8
  CB = CB * 10000 / 1024 / 1024
  CopyMemory TB, TBytes, 8
  TB = TB * 10000 / 1024 / 1024
  CopyMemory FB, FBytes, 8
  FB = FB * 10000 / 1024 / 1024
  UB = TB - FB
  MsgBox "Gesamt: " & Format$(TB, "##.00 MB") & vbCrLf & _
    "Belegt: " & Format$(UB, "##.00 MB") & vbCrLf & _
    "Frei: " & Format$(FB, "##.00 MB") & vbCrLf _
    , , "Festplattenspeicher Laufwerk """ & Root & """"
End Function