?
Solved

How determine what CD drive user has selected?

Posted on 2003-03-31
15
Medium Priority
?
144 Views
Last Modified: 2012-05-04
I am putting an Excel file on a CD for distribution to several people. One application requires coding to create an absolute link to a file on the CD in the drive in which the user has inserted the CD. eg:

The file is, say:  232.tif
This user's drive is G:\
need coding to determine that the drive is G so that I can create link:
G:\232.tif
0
Comment
Question by:dmorse03
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
  • +1
15 Comments
 
LVL 26

Expert Comment

by:EDDYKT
ID: 8238084
Private Sub Command1_Click()
Debug.Print Mid(App.Path, 1, 3) & "232.tif"
End Sub
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 8238091
To create short cut


Option Explicit

Enum ShortCutDest
  DeskTop
  Programs
  StartMenu
  StartUp
End Enum

Public Function CreateLink(dest As ShortCutDest, ByVal sName As String, ByVal sPath As String, Optional HotKey As String = "", Optional sIcon As String = "", Optional sWorkingDirectory As String = "", Optional sSubFolder As String = "", Optional WinStyle As Integer = vbNormalFocus)
 Dim WshShell As Object
 Dim oShellLink As Object
 Dim sLinkPath As String
 Set WshShell = CreateObject("WScript.Shell")
 Select Case dest
     Case DeskTop
          sLinkPath = WshShell.SpecialFolders("Desktop")
     Case StartMenu
          sLinkPath = WshShell.SpecialFolders("StartMenu")
     Case StartUp
          sLinkPath = WshShell.SpecialFolders("StartUp")
     Case Programs
          sLinkPath = WshShell.SpecialFolders("Programs")
 End Select
 On Error Resume Next
 If sSubFolder <> "" Then
    sLinkPath = sLinkPath & "\" & sSubFolder
    If Dir(sLinkPath) = "" Then MkDir sLinkPath
 End If
 On Error GoTo 0
 Set oShellLink = WshShell.CreateShortCut(sLinkPath & "\" & sName & ".lnk")
 oShellLink.WindowStyle = WinStyle
 oShellLink.HotKey = sHotKey
 oShellLink.TargetPath = sPath
 oShellLink.IconLocation = sIcon
 oShellLink.Description = sName
 oShellLink.WorkingDirectory = sWorkingDirectory
 oShellLink.Save
 Set oShellLink = Nothing
 Set WshShell = Nothing
End Function

'Usage
Private Sub Command1_Click()
 CreateLink DeskTop, "Calculator", "c:\windows\calc.exe", "CTRL+SHIFT+C", "calc.exe,0", "c:\windows"
 CreateLink StartMenu, "Calculator", "c:\windows\calc.exe", "CTRL+SHIFT+C", "calc.exe,0", "c:\windows"
 CreateLink StartUp, "Calculator", "c:\windows\calc.exe", "CTRL+SHIFT+C", "calc.exe,0", "c:\windows"
 CreateLink Programs, "Calculator", "c:\windows\calc.exe", "CTRL+SHIFT+C", "calc.exe,0", "c:\windows", "WinCalc"
 CreateLink Programs, "Calculator Help", "c:\windows\help\calc.hlp", "", "winhlp32.exe,0", "c:\windows\help", "WinCalc"
 CreateLink Programs, "Visit our web site", "http://www.experts-exchange.com", , "shdocvw.dll,0", , "WinCalc", vbMaximizedFocus
End Sub
0
 
LVL 6

Expert Comment

by:DrTech
ID: 8238107
App.Path gives you the path where the application was run from.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Expert Comment

by:iozturk
ID: 8238292
Try this code

Function ShowDriveType
   Dim fso, d, dc, s, n
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set dc = fso.Drives
   For Each d in dc
      if d.drivetype = 4 then exit for
   Next
   ShowDriveType = d.driveletter
End Function


msgbox showdrivetype
0
 
LVL 6

Expert Comment

by:DrTech
ID: 8238322
iozturk: What if the user has more than one cd-rom drive (for instance I have). Then you are not sure to get the right one!
0
 
LVL 4

Expert Comment

by:iozturk
ID: 8238643
DrTech: What if application is in HDD and 232.tif is in CD? Then you are not sure you are getting right one:)
But my code can be extended as

Function ShowDriveType
  Dim fso, d, dc, s, n
  dim flag as boolean
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set dc = fso.Drives
   
  flag=false
For Each d in dc
     if d.drivetype = 4 then
        if fso.fileexists (d.drivename & ":\232.tif") then exit for
        flag=true
     end if
     
Next

if flag=true then
   ShowDriveType = d.driveletter
else
   ShowDriveType = "Cannaot find correct drive"
end if


OK?
0
 
LVL 6

Expert Comment

by:DrTech
ID: 8238968
Agree! But the way I read the question, the application is run from the cd.

dmorse03, please clarify!
0
 
LVL 4

Expert Comment

by:iozturk
ID: 8239876
There is a typo! Pls. try this

Function ShowDriveType
 Dim fso, d, dc, s, n
 dim flag as boolean
 Set fso = CreateObject("Scripting.FileSystemObject")
 Set dc = fso.Drives
 
 flag=false
For Each d in dc
    if d.drivetype = 4 then
       if fso.fileexists (d.drivename & ":\232.tif") then
          flag=true
          exit for
        end if
    end if
   
Next

if flag=true then
  ShowDriveType = d.driveletter
else
  ShowDriveType = "Cannaot find correct drive"
end if
End Function
0
 

Author Comment

by:dmorse03
ID: 8246402
When executing this line:

if fso.fileexists (d.drivename & ":\232.tif") then

I get this error:

Run Time error '438'
Object doesn't support this property or method

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
I could put the entire Excel file on web storage for you to examine, but it is 930 Kb!
Incidently, yes the Excel file and all the fotos will be on CD. Also, most of the people who will be using this will use D:\ as the CD drive. But some will not. My Cd drives, for instance, are J:\ and K:\.

0
 
LVL 4

Expert Comment

by:iozturk
ID: 8246840
Sorry change d.drivename  to d.driveletter
0
 
LVL 6

Accepted Solution

by:
DrTech earned 375 total points
ID: 8247687
If the excel file is run from the cd, just use the App.Path property. That's really all there is to it!

Keep it simple!
0
 

Author Comment

by:dmorse03
ID: 8270820

Quote:
--------------------------------------------------------------------------------
Originally Posted by Iceplug
So, if you are running from the Excel macro, then
Application.ActiveWorkbook.Path hopefully gives you the path of your workbook... and you can substitute this for the App.Path that OnErr0r mentioned.
--------------------------------------------------------------------------------


^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
I owe you big time. I have been on this project since last June. Many forums have helped me but you supplied the final link I needed to finish it. So simple (if you know what you are doing). I now have 600+ family fotos on CD along with an Excel control program to allow the user to create a list of fotos per his criteria (Aunt Jane, eg) and run this list as a slideshow (your link).
Don
Cortland, NY
0
 

Author Comment

by:dmorse03
ID: 8272863
I originally posted this question. There is a wealth of information here. But the method I finally chose was from another source. The post is listed as "unlocked". I do not know how to close it out under the circumstances as I did not actually use any of the answers herein. But I do appreciate all the response I got here.
0
 
LVL 6

Expert Comment

by:DrTech
ID: 8278775
What solution did you use?
0
 

Author Comment

by:dmorse03
ID: 8279193
This is the suggestion I was given:

Application.ActiveWorkbook.Path

This is my final coding:

FindDrive:

   DriveID = Application.ActiveWorkbook.Path
     Range("U5").Select    'Select your first data cell
      Do Until ActiveCell.Value = ""
        ActiveCell.Value = DriveID & "\" & ActiveCell.Value & ".tif"        'Grab current value of the cell, Show path, append '.TIF', & replace it
       ActiveCell.Offset(1, 0).Select  'Move to next cell
     Loop
   
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month11 days, 5 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question