Create filepath if it's not available

[StephanieH]

I use the following to save a file to a new folder. How can I have it check
to see if the filepath exists, and if not go ahead and create the path?

ActiveWorkbook.SaveAs Filename:= _
"\\Fl-msjf-fs1\Data\Data\RECOVERY\EXLDATA\Loan Recovery MIS\" &
MyDate & "\Gross Placement Batch Tracks\Product Breakdown\Quaternary GBT
Prod_Breakdown " & MyDate & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

 

[AMDRIT]

from
http://cwashington.netreach.net/depo/view.asp?Index=1027&ScriptType=vbscript


Dim oFso

Set oFso = CreateObject("Scripting.FileSystemObject")

WScript.Echo MakePath(oFso, "C:\TEMP\FRED\BLOGGS")

' MakePath
' Arguments:
' oFso - Instance of FileSystemObject
' sPath - Required path (must be fully qualified)
' Returns:
' True - Path now exists
' False - Path does not exist
Function MakePath(oFso, sPath)
' Default result
MakePath = False

' Fail if drive is not valid
If Not oFso.DriveExists(oFso.GetDriveName(sPath)) Then Exit Function

' Succeed if folder exists
If oFso.FolderExists(sPath) Then
MakePath = True
Exit Function
End if

' Call self to ensure parent path exists
If Not MakePath(oFso, oFso.GetParentFolderName(sPath)) Then Exit
function

' Create folder
On Error Resume next
oFso.CreateFolder sPath
MakePath = oFso.FolderExists(sPath)
End function

 

[David Lloyd]

Stephanie:

VBA contains a Dir function for examining files and directories, and a MkDir
function for creating directories. If you pass the folder path to the Dir
function, it will return an empty string ("") if it does not exist. Both
functions are documented in VBA Help.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I use the following to save a file to a new folder. How can I have it check
to see if the filepath exists, and if not go ahead and create the path?

ActiveWorkbook.SaveAs Filename:= _
"\\Fl-msjf-fs1\Data\Data\RECOVERY\EXLDATA\Loan Recovery MIS\" &
MyDate & "\Gross Placement Batch Tracks\Product Breakdown\Quaternary GBT
Prod_Breakdown " & MyDate & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

 

[StephanieH]

Got it working. Thanks for your help AMDRIT and David.