Search code examples
excelwindows-7excel-2007vba

Create a folder in My Documents and save a workbook to that folder

I created an Excel Add-In that is used on a workbook with imported data. I need to add code that will do the following:

  • Check if folder exists C:\Users\\My Documents\ Extract Files\
  • Create the folder if it does not exist
  • Save the file into this folder with current date and time (Now) in the file name with an .xlsx extension.

Example: C:\Users\jdoe\My Documents\Extract Files\Extract - 01-15-2016 15:15.xlsx

I have found this, but need to know how to get my criteria above into this code:

ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlOpenXMLWorkbook

I do not know enough about VBA to create a folder if it does not exist (if you can do this with VBA). I have looked but could not find anything that was helpful. Asking for some guidance here. Thanks.

Solution

  • Try this code:

        Sub Ex()
If InStr(LCase$(ActiveWorkbook.name), "extract") > 0 Then
    Exit Sub
Else
     Dim MyDir As String, fn As String
     MyDir = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\Extract Files" ' change this to valid path
     If Len(Dir(MyDir, vbDirectory)) = 0 Then MkDir MyDir
     fn = MyDir & "\Extract - " & Format(Now, "mm-dd-yyyy hh_mm")
     ActiveWorkbook.SaveAs Filename:=fn, FileFormat:=xlOpenXMLWorkbook
End If
End Sub

    we can not use : in the file name