PDA

View Full Version : Open specific folder based on cell value or create folder



Didier
10-07-2019, 11:41 AM
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
10-08-2019, 10:23 AM
Hello Didier,

Welcome!

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

Didier
10-08-2019, 11:17 AM
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
10-08-2019, 12:02 PM
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
10-08-2019, 03:06 PM
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
10-08-2019, 05:15 PM
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
10-08-2019, 06:20 PM
Shell "cmd /c md " & "c:\test1\" & [H10], vbHide

Didier
10-09-2019, 03:36 AM
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 :rotlaugh: But i keep learning thanks to you guys.

Kenneth Hobs
10-09-2019, 08:30 AM
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
10-09-2019, 12:36 PM
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
10-09-2019, 02:33 PM
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
10-09-2019, 02:44 PM
Hi Leith,

I get an error on MkDir Path with this code

25252

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

Thanks

Kenneth Hobs
10-09-2019, 02:50 PM
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
10-09-2019, 09:08 PM
Hello Didier,
It appears you do not have permission to create the folder. This looks like a network related issue.

Didier
10-09-2019, 11:30 PM
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