Output of VBA code to Excel cells

[sanits591]

Hi,

I am new to VBA, i would like the outcome of the VBA code to excel cells in rows / columns.

I want the message box value to excel cell as soon as the message box appears.

Anticipatory thanks!

Sub TestDriveSpace_1()
' [URL="http://msdn.microsoft.com/en-us/library/aa394592(VS.85).aspx"]http://msdn.microsoft.com/en-us/libr...92(VS.85).aspx[/URL]
Dim cell As Range
Dim objWMI As Object
Dim colDisks As Object
Dim objDisk As Object
Dim strDriveType As String
Dim Urng As Range
Dim i As Integer
'Dim LUcell As String
  
Application.ScreenUpdating = False
Application.DisplayAlerts = False
  

  Set objWMI = GetWMIService

  ' determine free space
Set colDisks = objWMI.ExecQuery _
                 ("Select * from Win32_LogicalDisk")
  
For Each objDisk In colDisks
  
    With objDisk

    MsgBox "DeviceID: " & .DeviceID
      'Debug.Print "DeviceID: " & .DeviceID
      ' assume GB
            
MsgBox "Size: " & Format(.Size / 1000000000, "# GB")

     'Debug.Print "Size: " & Format(.Size / 1000000000, "# GB")
      ' assume GB
MsgBox "Free Disk Space: " _
                & Format(.FreeSpace / 1000000000, "# GB")
      MsgBox "% Free: " & Format(.FreeSpace / .Size, "Percent")

     'Debug.Print "Free Disk Space: " _
                & Format(.FreeSpace / 1000000000, "# GB")
      'Debug.Print "% Free: " & Format(.FreeSpace / .Size, "Percent")

'      ' [URL="http://msdn.microsoft.com/en-us/library/aa394173(VS.85).aspx"]http://msdn.microsoft.com/en-us/libr...73(VS.85).aspx[/URL]
     Select Case .DriveType
        Case 0
          strDriveType = "Unknown"
        Case 1
          strDriveType = "No Root Directory"
        Case 2
          strDriveType = "Removable Disk"
        Case 3
          strDriveType = "Local Disk"
        Case 4
          strDriveType = "Network Drive"
        Case 5
          strDriveType = "Compact Disc"
        Case 6
          strDriveType = "RAM Disk"
      End Select

MsgBox "Drive Type: " & strDriveType

'Debug.Print "Drive Type: " & strDriveType

'
            If (strDriveType = "Local Disk") Or (strDriveType = "Network Drive") Then
        MsgBox "File System: " & .FileSystem

        '        Debug.Print "File System: " & .FileSystem
      End If
      If strDriveType = "Network Drive" Then
         MsgBox "Provider Name: " & .ProviderName
         'Debug.Print "Provider Name: " & .ProviderName
      End If
'      Debug.Print "---"

      
End With

Next objDisk
'

End Sub

 

[Ruddles]

Where you have, for example:-

MsgBox "DeviceID: " & .DeviceID

immediately before that line, insert:-

irow=cells(rows.count,1).end(xlup).row+1
cells(irow,1)="DeviceID: "
cells(irow,2)=.DeviceID

irow should be declared as Long.

Where you use Format in the MsgBox, omit it when you copy the info to the cell, then format the cell. So before this:-

MsgBox "Size: " & Format(.Size / 1000000000, "# GB")

insert this:-

irow=cells(rows.count,1).end(xlup).row+1
cells(irow,1)="Size: "
cells(irow,2)=.Size / 1000000000
cells(irow,2).numberformat="# GB"

 

[sanits591]

Where you have, for example:-

MsgBox "DeviceID: " & .DeviceID

immediately before that line, insert:-

irow=cells(rows.count,1).end(xlup).row+1
cells(irow,1)="DeviceID: "
cells(irow,2)=.DeviceID

irow should be declared as Long.

Where you use Format in the MsgBox, omit it when you copy the info to the cell, then format the cell. So before this:-

MsgBox "Size: " & Format(.Size / 1000000000, "# GB")

insert this:-

irow=cells(rows.count,1).end(xlup).row+1
cells(irow,1)="Size: "
cells(irow,2)=.Size / 1000000000
cells(irow,2).numberformat="# GB"

Thanks Ruddles! its working, and most of my problems are resolved now!