I have this code that is working as intended. Since I am still learning VBA and coding, I was wondering if anyone could look at this at let me know if there is another way to write this. This is part of an Add-In I created and is used by several other people (meaning that I installed the Add-In onto their PCs).
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
I had a problem with the code creating a new file each time the custom menu button (which is the trigger for the add-in and has other tools that are used on this workbook) was activated, so I added this to prevent that from occurring:
If InStr(LCase$(ActiveWorkbook.name), "extract") > 0 Then Exit Sub Else
Please let me know if there is any room for improvement of the way this is constructed.