CODEWITHSUNDEEP
excelvba
user3214725
user3214725

Reputation: 17

VBA to search for folder or create it

good afternoon all, i am using the following code on my spreadsheet to save the file in a specific folder with a specific format: 

Const csPath As String = "C:\Stationery Orders\"
MyName = ActiveWorkbook.Name
ActiveWorkbook.SaveAs Filename:=csPath & Sheets("Stationery").Cells(1, 1) & Format(CStr(Now), "ddmmyyyy_hhmm") & "  " & MyName & ".xlsm", FileFormat:=52

my problem is that i can't find a way to create this folder C:\Stationery Orders\ if the folder doesn't exist and also paste a shortcut on the user's desktop. Is that even possible? any ideas?

kind regards

Upvotes: 1

Views: 178

Answers (2)

Pankaj Jaju
Pankaj Jaju

Reputation: 5471

Put a check before doing SaveAs. Something like,

If Dir(csPath, vbDirectory) = "" Then MkDir csPath

Then do the SaveAs

Upvotes: 1

ttaaoossuu
ttaaoossuu

Reputation: 7884

Try this. It will check if folder exists and create it if it doesn't exist.

Sub MyCuteSub()
    Dim FSO
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If Not FSO.FolderExists("C:\temp\temptemptemp") Then
        FSO.CreateFolder ("C:\temp\temptemptemp")
    End If
End Sub

Upvotes: 0

Related Questions