VBA to search for folder or create it

Question

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

Pankaj Jaju · Accepted Answer · 2014-02-17 15:07:41Z

1

Put a check before doing SaveAs. Something like,

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

Then do the SaveAs

answered Feb 17, 2014 at 15:07

Pankaj Jaju

5,5112 gold badges29 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3214725 Over a year ago

thank you very much for your replies, they both work but this one is easier to implement.

Taosique · Accepted Answer · 2014-02-17 15:02:24Z

0

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

answered Feb 17, 2014 at 15:02

Taosique

7,9143 gold badges33 silver badges60 bronze badges

Collectives™ on Stack Overflow

VBA to search for folder or create it

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related