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?

 

[Nalani]

Thanks for all the replies, guys.

Took a little while trying all the suggestions.

Tried SillyCat's first. Worked just fine, once I spelled the flash drive correctly. After over an hour of stepping trough and banging my head, I realized I forgot the R in the name.

flashDriveName = "CORSAIR"

Tried Greg's next, but could never get the Logic on it. Spent a few hours trying with no success.

Then I went back to tusharm's post and dissected what he was trying to tell me. Once I figured it out, it required me to change only one line in my code versus adding all that SillyCat had suggested. (although it was a good suggestion)

Changed

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

To This

myFolder = ThisWorkbook.Path & "\"

Again, thanks alot.

 

[Greg Truby]

Tried Greg's next, but could never get the Logic on it. Spent a few hours trying with no success.

OK - that's more than enough to convince me you are trying to learn to fish. :wink:

All you needed to do was drop the code for the two functions into your VBA project and then just use them to find what you needed.

Not testest, but something along these lines should'a done it.

Sub MergeBooks()
    '// rather than slip "magic numbers" or "magic strings" into
    '// the middle of code, you should corral them up top as constants
    Const c_strPath As String = "\My VBA Resume\Forecast\"
    Dim strDrive As String
    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
    
    '// use the function to find the needed drive
    Let strDrive = fnFindPath(c_strPath)
    '// construct the path now that you know the drive
    myFolder = strDrive & ":" & c_strPath
    
    
    myBooks = Array("LV Forecast.xls", "TUC Forecast.xls", "AUS Forecast.xls", "DA Forecast.xls", "GA Forecast")
    Set wsSUMMARY = Sheets("Sheet1")
'//...(cont'd)

 

[Nalani]

Thanks for the reply Greg.

I did test it out just for my own learning experience.

It brings back Drive C, but the Folder is truely on Drive E.

I don't understand enough about your code/Functions to figure out why.

 

[tusharm]

Glad you got that figured out. Now, when you show off your projects they will be more like your projects. {grin}

Thanks for all the replies, guys.

Took a little while trying all the suggestions.

Tried SillyCat's first. Worked just fine, once I spelled the flash drive correctly. After over an hour of stepping trough and banging my head, I realized I forgot the R in the name.

flashDriveName = "CORSAIR"

Tried Greg's next, but could never get the Logic on it. Spent a few hours trying with no success.

Then I went back to tusharm's post and dissected what he was trying to tell me. Once I figured it out, it required me to change only one line in my code versus adding all that SillyCat had suggested. (although it was a good suggestion)

Changed

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

To This

myFolder = ThisWorkbook.Path & "\"

Again, thanks alot.

 

[Nalani]

Glad you got that figured out. Now, when you show off your projects they will be more like your projects. {grin}

Well one thing that I will be sure to say is:

"I may not be an expert in VBA codeing, but I do know where to go to get Help. From there I can adapt."

 

[Greg Truby]

It brings back Drive C, but the Folder is truely on Drive E.

The path must be unique. If the path exists on more than on drive, then it will return the first drive that contains such a path.