Open specific folder based on cell value or create folder

Hi,

Just starting to learn VBA and i have a small problem.
I need to open a specific folder or create it if it doesn't exist based on a cell value.

Code:

---

Sub Go_To_Client()

Dim dir As String
Dim fso As Object
Dim FolderPath As String
FolderPath = ("c:\test\")
dir = Range("H10").Value
Set fso = CreateObject("scripting.filesystemobject")
If Not fso.folderexists(dir) Then
    fso.createfolder (dir)
End If
    Call Shell("explorer.exe" & " " & dir, vbNormalFocus)
End Sub

---

It works a little bit :)
It searched for the folder in documents and creates a new one but i cant figure out how to search in a set location

Hope someone can help me

Thanks in advance

Hello Didier,

Welcome!

I do not understand what you mean by a "set location"? Can you give me an example of this?

Hi Leith,

thanks for the reply.

I need to search for the map in for example c:\test\
With the code i am using now it always searches in "documents'' folder and creates a new map there because its the wrong location

It looks like it doesnt read the FolderPath line

Thanks

Hello

I personally like to use the Shell Application Object rather than File System Object. Here is the code to do what you want using the Shell.

Code:

---

Sub Go_To_Client()


    Dim Folder  As Object
    Dim Path    As Variant
   
        Path = "C:\Test"
       
        With CreateObject("Shell.Application")
            Set Folder = .Namespace(Path)
            If Not Folder Is Nothing Then
                .Open Path
            Else
                MkDir Path
            End If
        End With
       
End Sub

---

Hey Leith,

Thanks for the help.
This works for opening the correct folder but doenst work with the cell value for creating or opening the folder

Code:

---

Sub Go_To_Client()

    Dim dir As String
    Dim Folder  As Object
    Dim Path    As Variant
            dir = Range("H10").Value

   
        Path = "C:\Test"
        With CreateObject("Shell.Application")
            Set Folder = .Namespace(Path)
            If Not Folder Is Nothing Then
                .Open Path
            Else
                MkDir Path
            End If
        End With
       
End Sub

---

I tried this and it opens the test folder but doesnt use the cell value to search the map and create the folder using the cell value

Thanks

Hello Didier,

I have been out of the office for several hours. Here is the corrected code...

Code:

---

Sub Go_To_Client()


    Dim Folder  As Object
    Dim Path    As Variant
   
        ' // Path to the folder.
        Path = Range("A10")
       
        With CreateObject("Shell.Application")
            Set Folder = .Namespace(Path)
            If Not Folder Is Nothing Then
                .Open Path
            Else
                MkDir Path
            End If
        End With
       
End Sub

---

Code:

---

Shell "cmd /c md " & "c:\test1\" & [H10], vbHide

---

Hi guys,

I tried the corrected code but i get Folder=Nothing as return.
The path gives the correct H10 cell value.

in the future i also need to put files in the folder that gets created if no file exists (is this the right way of setting this up ?)

Code:

---

Sub Go_To_Client()



    Dim Folder  As Object
    Dim Path    As Variant
   
        'C:\Test
        Path = Range("H10")
       
        With CreateObject("Shell.Application")
            Set Folder = .Namespace(Path)
            If Not Folder Is Nothing Then
                .Open Path
            Else
                MkDir Path
            End If
        End With
       
End Sub

---

Kenneth i dont really get what you mean with this line of code

Thanks for the help
The learning curve of VBA is steep :rotlaugh: But i keep learning thanks to you guys.

The advantage to the command shell method that I used is that it creates all subfolders as well. md means Make Directory. Directory=Folder. Of course if the drive or folder names are not legal, it will not create them. It does not hurt to use it even if the folder(s) already exist.

Code:

---

Sub Ken()   
    Dim f As String
    f = """C:\Test\1\2"""
    Shell "cmd /c md " & f, vbHide
    Shell "explorer " & f, vbNormalFocus
End Sub

---

Thanks for the detailed explanation but the problem is i need to search if the map exists with the value in a cell. They are client files and i need to open the folder of the client or create one with standard content inside like calculation programs and folders.

I can search for the client number within excel and i get all the information of our ERP with a csv file. But now i need a connection back to make it possible to open the correct map of that client. I am sending mails with excel with data of the client and files from the map. (The mail works but the data is still a questionmark)

Thanks in advance

Hello Didier,

The code in post #8 is correct. If Folder is Nothing then the folder was not found. Is your path to a network folder?

Hi Leith,

I get an error on MkDir Path with this code

Attachment 25252

It reads out the correct value of cell H10 but doesnt seem to function

Thanks

I have no idea what a map is as related to your need.

The code that I posted creates all folders in the path. If you want to check for a file existing, you can use fso or Dir(). e.g.

Code:

---

If Dir("c:\Test\ken.xlsx")<>"" then Workbooks.Open("c:\Test\ken.xlsx")

---

Hello Didier,
It appears you do not have permission to create the folder. This looks like a network related issue.

Hi Leith,

That's strange i al testing this on my own pc on my desktop.

Ik Will try some things you are what it is

Thank you so much for the help