Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Using a CD-ROM (Excel 97 and >)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Gang

    Happy New year to all of you, I trust you all had a nice vacation/holiday and now it is time for some work.

    I have a silly question <img src=/S/shy.gif border=0 alt=shy width=15 height=15>, but is there a way that I can detect if my User is using a CD-ROM vs a Hard drive or a Zip Drive?

    I have this application, and it reads files from a source, maybe a CD, or a Network drive. It saves the workbooks in the same location it read it from, and when it is read from a CD well <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> How do you save it back?

    So I need to know if the path leads to a CD-ROM so that I can batch process all the files read from the CD in the next CD cut.

    I tried saving to the path and trap any errors, but that does not work all the time. I tried learning the Drive letters, but some PCs have multiple physical HDs and that did not work either.

    So any ideas? <img src=/S/lightbulb.gif border=0 alt=lightbulb width=15 height=15>

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a CD-ROM (Excel 97 and >)

    I think that you will have to use the Windows API to determine that. Unfortunately, my API documentation is packed up in storage so I can't look up how to do it. Maybe someone else will jump in with the details.
    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Using a CD-ROM (Excel 97 and >)

    Hi Wassim,

    Using the windows API that Legare mentioned <pre>Declare Function GetDriveType Lib "kernel32" _
    Alias "GetDriveTypeA" _
    (ByVal nDrive As String) As Long

    Function DriveType(Optional fPath As String) As String
    If fPath = "" Then
    If ActiveWorkbook.Path = "" Then
    DriveType = "Not saved"
    Exit Function
    Else
    fPath = ActiveWorkbook.FullName
    End If
    End If
    Select Case GetDriveType(Left(fPath, 3))
    Case 2
    DriveType = "Removable"
    Case 3
    DriveType = "Fixed"
    Case 4
    DriveType = "Remote"
    Case 5
    DriveType "CD-ROM"
    Case 6
    DriveType "Ram Disk"
    Case Else
    DriveType = "Unknown"
    End Select
    End Function</pre>

    Or you could use the FileSystem Object with the following :<pre>Function DriveType(Optional fPath As String)
    Set fso = CreateObject("Scripting.FileSystemObject")
    If fPath = "" Then
    If ActiveWorkbook.Path = "" Then
    DriveType = "Not saved"
    Exit Function
    Else
    fPath = ActiveWorkbook.FullName
    End If
    End If
    Set Fi = fso.GetFile(fPath)
    Select Case Fi.Drive.DriveType
    Case 1
    DriveType = "Removable"
    Case 2
    DriveType = "Fixed"
    Case 3
    DriveType = "Network"
    Case 4
    DriveType = "CD-ROM"
    Case 6
    DriveType = "Ram Disk"
    Case Else
    DriveType = "Unknown"
    End Select
    End Function</pre>

    In either case if you omit the Filename argument, the drive type of the active workbook is returned, providing of course that it is saved. With regrad to the API, I do not know what the value 1 represents, if anything.

    Andrew C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •