Output of VBA code to Excel cells

sanits591

Active Member
Joined
May 30, 2010
Messages
253
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!


Code:
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Where you have, for example:-
Code:
MsgBox "DeviceID: " & .DeviceID
immediately before that line, insert:-
Code:
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:-
Code:
MsgBox "Size: " & Format(.Size / 1000000000, "# GB")
insert this:-
Code:
irow=cells(rows.count,1).end(xlup).row+1
cells(irow,1)="Size: "
cells(irow,2)=.Size / 1000000000
cells(irow,2).numberformat="# GB"
 
Upvote 0
Where you have, for example:-
Code:
MsgBox "DeviceID: " & .DeviceID
immediately before that line, insert:-
Code:
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:-
Code:
MsgBox "Size: " & Format(.Size / 1000000000, "# GB")
insert this:-
Code:
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!
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top