Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    subdirectory open macro (2003)

    I have a dialog macro which opens a particular subdirectory so that I can get the appropriate project file. The macro was probably from this forum, although I don't remeber for sure - which is to say, I don't know enough to write it myself <g>.

    It worked fine until we started to use a server and everything is no longer on my C: drive. What would be the best way to modify the following macro so that (1) drives other than C: will be recognized and (2) subdirectories on the server will be recognized. And whlie I'm asking, is there a way to have the directory exhibited in date descending order?

    Thanks.


    Sub opensezme()
    '
    ' shift Macro
    ' Macro recorded 04/28/2003 by John Palmer
    '
    ' Keyboard Shortcut: Ctrl+h

    Dim stDir As String
    Select Case UCase(InputBox("Enter a letter" & vbNewLine & "A 123" & vbNewLine & "B 123proposal" & vbNewLine & "C 123time & expense" & vbNewLine & "D Utah" & vbNewLine & "E UtahRockware Data" & vbNewLine & "F DGF-060" & vbNewLine & "G DGF-080 - Titusville" & vbNewLine & "H PLP-010 Martins Creek" & vbNewLine & "I Seepage Meter" & vbNewLine & "J streamflow" & vbNewLine & "K UtahGW Vistas" & vbNewLine & "L " & vbNewLine & "M " & vbNewLine & "N " & vbNewLine & "O Old Projects" & vbNewLine & "P " & vbNewLine & "Q " & vbNewLine & "R " & vbNewLine & "S " & vbNewLine & "T " & vbNewLine & "U " & vbNewLine & "z A Transfer Folder", "Select Directory on D: Drive"))
    Case ""
    ' cancelled
    Exit Sub
    Case "A"
    stDir = "123"
    Case "B"
    stDir = "123proposal"
    Case "C"
    stDir = "timesheets"
    Case "D"
    stDir = "!projectsUtah"
    Case "E"
    stDir = "!projectsUtahRockware Data"
    Case "F"
    stDir = "!projectsDGF-060"
    Case "G"
    stDir = "!projectsDGF-080 - Titusville"
    Case "H"
    stDir = "!projectsPLP-010 Martins Creek"
    Case "I"
    stDir = "!projectsTBF Annual ReportsCYC2006"
    Case "J"
    stDir = "!projectsOLD PROJECTSonaprecip"
    Case "K"
    stDir = "!projectsUtahGW_Vistas"
    Case "L"
    stDir = "CES Stuff"
    Case "M"
    stDir = "!projectsCES"
    Case "N"
    stDir = "123CES Timesheets"
    Case "O"
    stDir = "!projectsOld Projects"
    Case "P"
    stDir = "!projectsOnabox&whisker"
    Case "Q"
    stDir = "A Transfer Folder"
    Case "R"
    stDir = "!projectsstreamflow data"
    Case "S"
    stDir = "!projectsseepage meter"
    Case "T"
    stDir = "!projectsAltman"
    Case "U"
    stDir = "!projectsEast Lake Model PCF-180"
    Case "V"
    stDir = ""
    Case "W"
    stDir = ""
    Case "X"
    stDir = "Peace River - Ona Mine CD's"
    Case "Y"
    stDir = ""
    Case "Z"
    stDir = "A Transfer Folder"
    '...
    End Select
    If Application.Dialogs(xlDialogOpen).Show("c:" & stDir & "*.xls") Then
    ' file opened
    Else
    ' no file opened
    End If
    End Sub

  2. #2
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: subdirectory open macro (2003)

    also, a server path example is: SDI-SERVERStorageOffice Information

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: subdirectory open macro (2003)

    The C: drive is specified in the line

    If Application.Dialogs(xlDialogOpen).Show("c:" & stDir & "*.xls") Then

    You can replace "c:" with another drive letter that refers to a network path, or with a UNC path that looks like "serversharefolder".

    The order in which files and folders are displayed is a user-level setting.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: subdirectory open macro (2003)

    If you need to pick a file, then it is probably easier to use <code>Application.GetOpenFilename</code> and let the user browse as required - it will be at least as quick as reading through all those options!
    Something like:
    <pre> varFileName = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select project file")
    If TypeName(varFileName) = "Boolean" Then Exit Sub
    Workbooks.Open varFileName
    </pre>

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: subdirectory open macro (2003)

    Thanks to all. I got my macro in working order (and Word as well).

Posting Permissions

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