Macro to check if folder/subfolder exist for any user if not create them and save file

[1nk3d]

Hello all.

I am looking for a 3 in one here. I have a macro that will save a file automatically when ran. However, I am looking to use a new macro for a workbook.

I do not have any code right now, as everything I found has not worked.

What I want to do is check if a folder/subfolder exist on a users desktop, however everyone who will have access, has their own user.

Ideally it would be saved on the desktop under Reports/Feb (I can change the macro each Month as needed.

If that path does not exist, I want the macro to create it

The last part would be saving it as todays date & report.

 

[1nk3d]

I have an updated question, some users on excel 2010 are getting a compile error, and when I remove the password, VBA highlights the Date, any ideas?

Try this version of the Test macro:

Public Sub Test()

    Dim desktopSubfolder As String
    Dim fullFileName As String
    
    desktopSubfolder = Create_Desktop_Subfolder("\Reports\" & Format(Date, "Mmm"))
    fullFileName = desktopSubfolder & "\" & Format(Date, "yyyy-mm-dd") & " Report" & Mid(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, "."))
    ThisWorkbook.SaveCopyAs fullFileName
    MsgBox "Saved " & fullFileName
    
End Sub

Another change is that the macro now saves the file in the subfolder for the current month, so there is no need to edit the code from month to month.

 

[John_w]

To fix that error, change Date to VBA.Date. You might need to add the "VBA." prefix for the other VBA intrinsic functions - Format, Mid and InStrRev.

If no joy, check for MISSING references in Tools -> References.

 

[1nk3d]

Is there a way I can test this? As the original code worked fine but user with excel 2010 we’re having issues

 

[John_w]

Debug -> Compile in the VBA editor.

 

[1nk3d]

Seems to work so far, I found an old machine running 2010 so I’m using that to verify the functionality, appears to be working. Will keep you posted, thank you