Open specific folder based on cell value or create folder

[Didier]

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.

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

[Leith Ross]

Hello Didier,

Welcome!

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

[Didier]

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

[Leith Ross]

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.

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

[Didier]

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

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

[Leith Ross]

Hello Didier,

I have been out of the office for several hours. Here is the corrected 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

[Kenneth Hobs]

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

[Didier]

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 ?)

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 But i keep learning thanks to you guys.

[Kenneth Hobs]

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.

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

[Didier]

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

[Leith Ross]

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?

[Didier]

Hi Leith,

I get an error on MkDir Path with this code

mkdirpatherror.JPG

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

Thanks

[Kenneth Hobs]

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.

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

[Leith Ross]

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

[Didier]

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