Creating new folder before export

[Curry]

Hi All,

I am exporting a Query to excel to an existing Folder. I would like to be able to export to a new folder that is created before the export ie labelled by the current month. So my question is, is their a way to create a folder say on the desktop, labelled current month before the exported file is placed within this folder.

Thanks
IC

 

[HiTechCoach]

Sure.

I use this:

Function CreateFolder(MyPath As String)
  
'Created by Dane A. Miller aka dallr
'create recursive folders on a path eg. "c:\dane\fred\harry" 
Dim c As Integer
On Error Resume Next

If InStr(4, MyPath, "\") = 0 Then
    MkDir (MyPath)
Else
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
        For c = 4 To Len(MyPath)
            If Mid(MyPath, c, 1) = "\" Then
                MkDir Left(MyPath, c)
            End If
        Next c
End If
End Function

 

[Curry]

Thank you for this. I will give it a go.

IC

 

[pono1]

Something else to experiment with...

' Get folder name.
Dim myMonth As String: myMonth = MonthName(Month(Now))
Dim myfolder As String: myfolder = VBA.Environ("ALLUSERSPROFILE") & "\DeskTop\Test_It"

' Create folder if necessary.
Dim fso:  Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FolderExists(myfolder) = False Then
  fso.CreateFolder (myfolder)
End If

Regards,
Tim

 

[DJkarl]

As always there are so many ways to do this, my prefered method has always been

Declare Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal DirPath As String) As Long
 
Sub TestMkDir
 
Call MakeSureDirectoryPathExists("C:\Temp\NewDir\NewDir2")
 
End Sub

 

[Curry]

Thanks Everybody. The MkDir (MyPath) worked as I needed it to.

IC

 

[HiTechCoach]

Glad you found a solution that worked for you!