Forcing folder creation

thekamel

New Member
Joined
Feb 11, 2015
Messages
16
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Hello everyone,

I am using a script that will save a file to a specific folder based of a couple of cells. If the folder doesn't exist, the command fails. I cannot figure out how to get it to create the folder if it isn't there. Here is what I am using now:

Sub File_Save()

Dim Path1 As String
Dim Path2 As String
Dim myfilename As String
Dim fpathname As String

Path1 = Range("K278")
Path2 = Range("K279")
myfilename = Range("K280")
fpathname = Path1 & "\" & Path2 & myfilename & ".xlsm"

MsgBox "You are trying to save the file to:" & vbCrLf & fpathname
ActiveWorkbook.SaveAs Filename:=fpathname, FileFormat:=xlOpenXMLWorkbookMacroEnabled

End Sub

Any help is greatly appreciated!

Thank you,
Kamel
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Untested, but try this:

VBA Code:
Sub File_Save()
'
    Dim Path1       As String, Path2        As String
    Dim fpathname   As String, myfilename   As String
'
    Path1 = Range("K278")
    Path2 = Range("K279")
    myfilename = Range("K280")
'
    If Len(Dir(Path1)) = 0 Then MkDir Path1
    If Len(Dir(Path2)) = 0 Then MkDir Path2
'
    fpathname = Path1 & "\" & Path2 & myfilename & ".xlsm"
'
    MsgBox "You are trying to save the file to:" & vbCrLf & fpathname
'
    ActiveWorkbook.SaveAs Filename:=fpathname, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub
 
Upvote 0
Hello,​
or just using MakeSureDirectoryPathExists like in this thread :​
 
Upvote 0
A VBA demonstration to paste to the top of the module (for Excel versions prior to 2010 version remove PtrSafe statement) :​
VBA Code:
Private Declare PtrSafe Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal DirPath$) As Boolean

Sub Demo1()
    Dim F$
        F = Join$([TRANSPOSE(K278:K280)], "\")
        If MakeSureDirectoryPathExists(F) Then ActiveWorkbook.SaveAs F, 52 Else Beep
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top