Dynamic Path ?

[Nalani]

I'm getting ready for a new job interview and need to show off some of the projects I created(have). I have copied these to a flash drive for presentation.

One project that currently sits on a server, I have changed the path that is a variable to the path on My Desktop Computer that says the Flash Drive is E:

i.e.

myFolder = "E:\My VBA Resume\Forecast\"

When I plug this Flash Drive in my Laptop, the path is F:

Clearly the path changes between computers.

Not knowing how this interview will go, I might have to plug into some other computer. I don't want to have to go into the VBA and change the path (once I find it out) in the middle of an interview, if you know what I mean.

Is there some other call that can be made that will automatically detect what the drive letter is on a "foreign" computer?

 

[Greg Truby]

Why not just name the path something you're sure won't exist on someone else's drive and then simply look for it?

'// looking for a path on some drive, but we're
'// not sure which drive letter
Sub WheresMyDrive()
    Const c_strPathSought As String = "\ThisIsAPathThatShouldBeUnique"
 
    For d = Asc("A") To Asc("Z")
        If fnPathExists(Chr(d) & ":" & c_strPathSought) Then Exit For
    Next d
 
    If d > Asc("Z") Then
        MsgBox "I dunno where dat path has got off to, boss", vbInformation, "Lost In Space"
    Else
        MsgBox "The path to success lies on drive " & Chr(d) & ":", vbInformation, "Eureka! Found it!"
    End If
End Sub
 
' _____________________________________________________________________________
' fn PATH EXISTS
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Descrip:  checks to see if a path exists.  taken from walkenbach's site:
'           {http://www.j-walk.com/ss/excel/tips/tip54.htm#func3}
'
' Args:     strPath · · · · the path being verified
'
' Returns:  Boolean · · · · TRUE if the path does indeed exist
'
' Date          Developer   Comments
' ¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
' 21 Aug 2008   G. Truby    • moved to the project - initial version very old
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Function fnPathExists(ByVal strPath As String) As Boolean
' _____________________________________________________________________________
 
    On Error Resume Next
    Let fnPathExists = GetAttr(strPath) And vbDirectory
End Function

 

[Nalani]

Greg,
Thanks for the reply.

But knowing how your are the type to show "how to fish" instead of giving the "fish". And being a little comical at times.

I am still lost on this. Your post really consists of this:

Sub WheresMyDrive()
    Const c_strPathSought As String = "\ThisIsAPathThatShouldBeUnique"
 
    For d = Asc("A") To Asc("Z")
        If fnPathExists(Chr(d) & ":" & c_strPathSought) Then Exit For
    Next d
 
    If d > Asc("Z") Then
        MsgBox "I dunno where dat path has got off to, boss", vbInformation, "Lost In Space"
    Else
        MsgBox "The path to success lies on drive " & Chr(d) & ":", vbInformation, "Eureka! Found it!"
    End If
End Sub

I really don't need Message Boxes comming up when I'm in an Interview showing off some VBA procedure.

Maybe more of the Procedure would help:

Sub MergeBooks()
    Dim myFolder As String
    Dim myBooks
    Dim ws As Worksheet, wsSUMMARY As Worksheet
    Dim nr As Long, rws As Long, i As Long, nc As Long, LR As Long
    Dim Response As Integer
    Set wsSUMMARY = Sheets("Sheet1")
    Response = MsgBox(Prompt:="Would You Like To Keep A Copy" & vbCr & _
        "    Of The Currrent Sheet?", Buttons:=vbYesNo, Title:="          CREATE A COPY")
    If Response = vbYes Then
        Sheets("Sheet1").Copy After:=Sheets(1)
        On Error Resume Next
        ActiveSheet.Name = Format(ActiveSheet.Range("B1").Value, "mm-dd-yy")
        ActiveSheet.Shapes("Bevel 1").Delete
    End If
    wsSUMMARY.Activate
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    myFolder = "E:\My VBA Resume\Forecast\"   '// need this to change per computer
    myBooks = Array("LV Forecast.xls", "TUC Forecast.xls", "AUS Forecast.xls", "DA Forecast.xls", "GA Forecast")
    Set wsSUMMARY = Sheets("Sheet1")
    
    wsSUMMARY.UsedRange.Offset(5).ClearContents
    On Error Resume Next
    For i = 0 To UBound(myBooks)
        Workbooks.Open (myFolder & myBooks(i))
        nc = i * 4 + 1
        For Each ws In ActiveWorkbook.Worksheets
            nr = wsSUMMARY.Cells(Rows.Count, nc).End(xlUp).Row + 1
            If ws.Name <> "DATA" And ws.Name <> "TEMP" And ws.Name <> "SUMMARY" Then
                LR = ws.Cells(Rows.Count, "B").End(xlUp).Offset(1).Row
                With ws.Range("B" & LR - 8 & ":" & "D" & LR).Copy
                    wsSUMMARY.Cells(nr, nc).Offset(1).PasteSpecial Paste:=xlPasteValues
                    wsSUMMARY.Cells(nr, nc).Offset(1).Value = ws.Name
                End With
            End If
        Next ws
        Workbooks(myBooks(i)).Close SaveChanges:=False
    Next i
    wsSUMMARY.UsedRange.EntireColumn.AutoFit
    wsSUMMARY.Range("B1") = Date
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Code was made with alot of help from Peter S S

 

[Greg Truby]

I really don't need Message Boxes comming up when I'm in an Interview showing off some VBA procedure.

Yeah, the idea was not to use messages. The idea was to take the kernal of logic -- looping all drive letters and testing for a path and use that. The actual soluton would likely involve taking the logic I used to create a function that would return the drive letter. For example:

' _____________________________________________________________________________
' fn FIND PATH
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Descrip:  Searchs all drive letters to attempt to locate a specified path
'           (in essense, a tool to determine an external drive's letter)
'
' ¡¡N.B.!!  This function REQUIRES the existence of another function:
'           fnPathExists()
'
' Args:     strPathSought · the path being looked for on all drive letters
'
' Returns:  string· · · · · the letter of the drive where the path was found
'                           or a nullstring if drive not found
'
' Date          Developer   Comments
' ¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
' 23 Jun 2011   G. Truby    • initial version
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
  Function fnFindPath(ByVal strPathSought As String) As String
' _____________________________________________________________________________
 
    Dim d%
 
    For d = Asc("A") To Asc("Z")
        If fnPathExists(Chr(d) & ":" & c_strPathSought) Then Exit For
    Next d
 
    If d > Asc("Z") Then
        Let fnFindPath = vbNullString
    Else
        Let fnFindPath = Chr(d)
    End If
End Function

...But knowing how your are the type to show "how to fish" instead of giving the "fish"...

Yes - so now, see if you can use the above function in your code to solve your problem.

 

[tusharm]

Maybe, you should adapt the code so that instead of the path being hard coded it works off something like thisworkbook.path.

If this property is new to you, you may want to look it up in Excel VBA help.

I'm getting ready for a new job interview and need to show off some of the projects I created(have). I have copied these to a flash drive for presentation.

One project that currently sits on a server, I have changed the path that is a variable to the path on My Desktop Computer that says the Flash Drive is E:

i.e.


When I plug this Flash Drive in my Laptop, the path is F:

Clearly the path changes between computers.

Not knowing how this interview will go, I might have to plug into some other computer. I don't want to have to go into the VBA and change the path (once I find it out) in the middle of an interview, if you know what I mean.

Is there some other call that can be made that will automatically detect what the drive letter is on a "foreign" computer?

 

[SillyCat]

Something simpler like looking for the drive with the correct label/volume name?

flashDriveName = "Label of my Flash Drive"

Set fs = CreateObject("Scripting.FileSystemObject")
Set dc = fs.Drives
    
For Each d In dc
    If d.IsReady Then 'So it does not bomb out on empty optical drives and the like
        If d.VolumeName = flashDriveName Then
            flashDriveLetter = d.DriveLetter
        End If
    End If
Next

myFolder = flashDriveLetter & ":\My VBA Resume\Forecast\"

 

[Nalani]

Maybe, you should adapt the code so that instead of the path being hard coded it works off something like thisworkbook.path.

If this property is new to you, you may want to look it up in Excel VBA help.

Thank you tusharm, that is what I think I need for this code.

I'm just not sure what to "look up in VBA Help"

Greg,
Your suggestion may do the job, but I don't know how to add it to what I already have.

 

[tusharm]

What do you think you should look up in VBA help? Particularly, after I suggest you use thisworkbook.path!

Thank you tusharm, that is what I think I need for this code.

I'm just not sure what to "look up in VBA Help"

Greg,
Your suggestion may do the job, but I don't know how to add it to what I already have.

 

[Nalani]

What do you think you should look up in VBA help? Particularly, after I suggest you use thisworkbook.path!

Looking up the above gives me this:

expression.Path
expression A variable that represents an Application object.

Example
This example displays the complete path to Microsoft Excel.



<TABLE><TBODY><TR><TH>Visual Basic for Applications</TH></TR><TR><TD>

<CODE>Sub TotalPath() MsgBox "The path is " & Application.Path</CODE>
<CODE></CODE>
<CODE>End Sub

</CODE>




</PRE>

<CODE>So with that , I'm still lost on what to do.</CODE>




</PRE></TD></TR></TBODY></TABLE></P>

 

[Greg Truby]

OK - your problem is this - you have hardcoded a path that is now a variable.

    myFolder = "E:\My VBA Resume\Forecast\"   '// need this to change per computer

So now you need to think about how you find the path you need. SillyCat and I are proposing identical solutions. He's just using a different means of finding the drive. I'm saying look for path XYZ on every possible drive letter. SillyCat is saying to inspect each valid drive's volume name and match that way.

What Tushar is saying is that if there is any kind of a relationship between the path of the workbook where the code is located and the path you seek, then simply utilize that relationship. Is the path you need the same? Is it a subdirectory? Is it up-and-over? If there's any kind of a relationship, you should be able to derive what you need.