none
Excel Macro to access specific folder RRS feed

  • Question

  • Hello All,

    Is there any macro to access specifi folder in a drive

    I have zip files under

    D:\Project\Level 1\987658\Draft\Part 1\*.zip

    E:\Project\Level 1\123456\Draft\Part 1\*.zip

    X:\Project\Level 1\54652\Draft\Part 1\*.zip

    When I enter the  Drive name and then job number in a message box, the macro should open the path of "Part 1" folder under the job number I entered.

    * The "job number" indicates combination of 5 or 6 digits numeric values.

    Thanks & regards

    John



    Tuesday, November 5, 2019 12:55 PM

Answers

  • Hi,

    If you want to open a specific path (with File Explorer), use "Shell" like this.
    Shell "C:\Windows\Explorer.exe " & openPath, vbNormalFocus
    "openPath" is path description that you want to open.

    Regards,

    Ashidacchi -- http://hokusosha.com

    P.S.
    I've modified my previous code:
    Sub Get_FileName(ByVal driveLetter As String, ByVal jobNumber As String)
        Dim targetPath As String
        targetPath = driveLetter & ":\Project\Level 1\" & _
                     jobNumber & "\Draft\Part 1"
        ' === open path with File Explorer ===
        Shell "C:\Windows\Explorer.exe " & targetPath, vbNormalFocus
    End Sub
    I hope it will help you.
    • Edited by Ashidacchi Wednesday, November 6, 2019 4:12 AM
    • Marked as answer by Johnmiller1234 Wednesday, November 6, 2019 8:50 AM
    Wednesday, November 6, 2019 4:07 AM

All replies

  • Hi Johnmiller1234,

    I've made a sample.
        

    [VBA code: in Sheet1]
    Option Explicit
    ' ---
    Sub Show_InputBox()
        Dim driveLetter As String
        Dim jobNumber As String
        '---
        driveLetter = InputBox("Input Drive Letter")
        If (driveLetter = "") Then
            Exit Sub
        Else
            Range("B1").Value = driveLetter
        End If
        ' ---
        jobNumber = InputBox("Input Job Number")
        If (jobNumber = "") Then
            Exit Sub
        Else
            Range("B3:B100").Value = ""
            Range("B2").Value = jobNumber
            Call Get_FileName(driveLetter, jobNumber)
        End If
    End Sub
    ' ---
    Sub Get_FileName(ByVal driveLetter As String, ByVal jobNumber As String)
        Dim targetPath As String
        targetPath = driveLetter & ":\Project\Level 1\" & _
                     jobNumber & "\Draft\Part 1"
        ' MsgBox "targetPath=" & targetPath  ' --<< for debugging
        ' ---
        Dim checkPath As String
        checkPath = Dir(targetPath, vbDirectory)
        If (Len(checkPath) = 0) Then
            MsgBox targetPath & vbCrLf & " is not found."
            Exit Sub
        End If
        ' ---
        Dim myRow As Integer
        myRow = 3
        Dim zipFile As String
        zipFile = Dir(targetPath & "\*.*")
        ' MsgBox "zipFile=" & zipFile  ' --<< for debugging
        Do While zipFile <> ""
            If (LCase(Right(zipFile, 4)) = ".zip") Then
                Range("B" & myRow).Value = zipFile
                myRow = myRow + 1
                zipFile = Dir()
            End If
        Loop
    End Sub
    


    Regards,

    Ashidacchi -- http://hokusosha.com

    Tuesday, November 5, 2019 11:51 PM
  • Hi Ashidacchi ,

    Thanks for the reply.

    I just ran it, the macro is not open the Part 1 folder but hanging.

    All I want is just open the path after entered the Drive & and Job number.

    Please let me know if any further details required.

    Thanks & regards

    John



    Wednesday, November 6, 2019 1:48 AM
  • Hi Johnmiller1234,

    I've tested with [G], [H], and [R] drives (I don't have the same drives as you).
        e.g. [G] drive is like this:
        
    If the above path is correct, you should run the macro [Show_InputBox] successfully.
    Please check if your path is the same as my macro, including space in the path.

    I've shared my sample "JohnMiller_Dir.xlsm" via OneDrive.
    Please download and check it.

    Regards,

    Ashidacchi -- http://hokusosha.com

    Wednesday, November 6, 2019 2:14 AM
  • Hi Ashidacchi,

    Let me start with big thanks for the prompt reply.

    I have tried again, but it didn't run the way for me, however thank you very much for the continous follow up.

    Note: I just want the macro to open the folder path instead get the file names inside the path.

    Really appreciate your help!

    John




    Wednesday, November 6, 2019 3:02 AM
  • Hi,

    If you want to open a specific path (with File Explorer), use "Shell" like this.
    Shell "C:\Windows\Explorer.exe " & openPath, vbNormalFocus
    "openPath" is path description that you want to open.

    Regards,

    Ashidacchi -- http://hokusosha.com

    P.S.
    I've modified my previous code:
    Sub Get_FileName(ByVal driveLetter As String, ByVal jobNumber As String)
        Dim targetPath As String
        targetPath = driveLetter & ":\Project\Level 1\" & _
                     jobNumber & "\Draft\Part 1"
        ' === open path with File Explorer ===
        Shell "C:\Windows\Explorer.exe " & targetPath, vbNormalFocus
    End Sub
    I hope it will help you.
    • Edited by Ashidacchi Wednesday, November 6, 2019 4:12 AM
    • Marked as answer by Johnmiller1234 Wednesday, November 6, 2019 8:50 AM
    Wednesday, November 6, 2019 4:07 AM
  • Hi Ashidacchi,

    The macro works excellent, this is what  exactly I want

    Thnak you so much for the help

    Kudos to you!!!

    Regards

    John


    Wednesday, November 6, 2019 8:50 AM
  • Hi John,

    Thank you for marking my post as an answer.

    #  I found you asked several questions.
        I'm trying to make answers, but it's evening in Japan.
        It's time to eat and drink.  See you tomorrow.

    Regards, 

    Ashidacchi -- http://hokusosha.com

    Wednesday, November 6, 2019 9:03 AM