Sub or Function not defined

[winstela]

Hello

I am having problems trying to create folders within folders.

I have code that will open a workbook if it exists and if not it will open another workbook which will be a blank template of the first workbook.

But if the path does not exist I want the macro to use the blank template and then create the path and folders. my current code which is not working is below. The sub or function not defined highlights CreateFolder

Sub openmyfile()
 Dim Path As String, File As String, wb As Workbook
 
 Path = Range("B2")
 File = Range("B3")
    
 'If File exists then open.
 If Dir(Path & File & ".xlsm") <> "" Then
    Set wb = Workbooks.Open(Path & File & ".xlsm")
 Else 'else, open the other one:
    Set wb = Workbooks.Open(Path & "05 Daily Bowler - Systems - May 2022.xlsm")
 End If

 Stop 'check if the workbook has been open and press F5 to let code finishing
 If wb = Len(Dir(Path)) = 0 Then
 strDir = Range("j2")
strPath = Range("J3") & Range("j4") & Range("j5")
CreateFolder Path
 'wb.Close SaveChanges:=True
End Sub



'requires reference to Microsoft Scripting Runtime
Function MkDir(strDir As String, strPath As String)

Dim fso As New FileSystemObject
Dim Path As String

Path = strPath & strDir

If Not fso.FolderExists(Path) Then

' doesn't exist, so create the folder
          fso.CreateFolder Path

End If
End Function

If you can help much appreciated.
Thanks

 

[Micron]

CreateFolder looks like a call to another procedure. Does it exist in your project, and if so, where? Sheet module code or standard module?

 

[Joe4]

That is because "CreateFolder" is not a valid function/VBA command (at least not without using FileSystemObjects, like you do in "MkDir").
So it does not know what to do with it.
It assumes that it is a function that you have created yourself, but cannot find it.

Note that if you are using FileSystem objects, there is a "CreateFolder" command, but it is related to the FileSytem object (note the reference in your MkDir command -- "fso.CreateFolder").

Also, using words like "File" and "Path" is generally a bad idea, as these are words already used by VBA, and using them can cause errors and other unexpected results.
Likewise, "MkDir" is already a VBA command, so creating a function with the exact same name is generally a bad idea (if you try to use it, how is VBA to know if you are meaning to call the built-in "MkDir" functionality, or your User Defined Function).

 

[winstela]

@ Joe4 Thanks for explaining, its hard to follow LOL
@ Micron no I do not have CreateFolder in my project.
.
I have been searching a week now and have not come up with anything that I can adapt or even work, as when I have used MkDir its also gave me a compile error with sub or function not defined. I will continue to explore.

Thank you
Winstela

 

[Joe4]

See here for Excel's built-in MkDir functionality:

MS Excel: How to use the MKDIR Statement (VBA)

This Excel tutorial explains how to use the Excel MKDIR statement with syntax and examples. The Microsoft Excel MKDIR statement allows you to create a new folder or directory.

www.techonthenet.com

 

[Micron]

Then my guess is that you copied that code from somewhere and either didn't notice there was another procedure there that you needed, or the site neglected to mention it. If you post a link maybe we can find it and advise or just re-write that to follow Joe4's suggestions (especially about using reserved words).

 

[winstela]

vba code to create folder Code Example

 

[winstela]

I have looked at the MkDir and will it only create one folder?
so I have a path that has multiple folders, in my case I would need to create 3 folders after the main path in cell J2
2023
then inside 2023
Daily
then inside Daily
03 Here

I am not sure it would.

 

[Micron]

You forgot fso. in front of CreateFolder.
As suspected, it's a method of the file scripting object. Copied from website:
If Not fso.FolderExists(path) Then
.....fso.CreateFolder path
End If

 

[winstela]

Thanks Micron

I will mark as answered as this has fixed my original problem

Now to fix the rest ?