Saveas Macro with current date

Hi valkok,

Which code module is the procedure sitting in? ie. A standard code module, a sheet class module, a userform class module, the ThisWorkbook class module?

How are you calling the procedure? eg. From an forms button?

It depends on what SaveFile is.

Private Sub SaveFile_Click()

If it is an ActiveX control sitting on a userform or a sheet, then the code needs to go in that userform or sheet's module.

If it is a forms control sitting on a sheet, then the code needs to go in a standard code module.

I'm pretty sure you have a Forms control on the sheet, so my suggestion would be for you to put the code in a standard code module (in the Modules folder).

Please can you attach the file to the thread (clear out any confidential information)?

Okay, you need to do two things.

Firstly, make the procedure public by changing the 'private' to 'public' like this:

Public Sub SaveFile_Click()

Then, right click on the Save File Button > Assign Macro > choose your SaveFile procedure from the list.


Also, in my post above I've used code tags so that my code looks nice in my post. The forum rules here ask you to do the same (you should have in post #1). To see how to use the code tags click on the quote button to the bottom right of my post.

It's a start, but now I have a compile error. Variable not defined. It highlighted the ActiveDocument portion of the code.

Should that ActiveDocument be ActiveWorkbook?

Yes, that's a separate issue. In Excel, we use ActiveWorkbook rather than ActiveDocument (you would use that in, for example, Microsoft Word).

The code to save to the workbook to the desktop is a bit more complicated. You would replace your current, standard module code with this.

'adapted from http://www.codenewsgroups.net/vb/t3409-get-current-user-desktop-path.aspx

'declarations...
Private Declare Function SHGetSpecialFolderLocation Lib "shell32.dll" _
    (ByVal hwndOwner As Long, _
    ByVal nFolder As Long, pidl As Long) As Long
'   Ret: 0=success

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
    (pidl As Long, ByVal pszPath As String) As Long
Private Declare Function GlobalFree Lib "kernel32" _
    (ByVal hMem As Long) As Long



'implementation...
Private Function GetShellFolderPath(ByVal CSIDL As Long) As String
    
    Const MAX_PATH As Long = 260
    
    Dim pID As Long
    Dim sTmp As String

    If SHGetSpecialFolderLocation(0&, CSIDL, pID) = 0& Then
        sTmp = String(MAX_PATH + 2, 0)
        If SHGetPathFromIDList(ByVal pID, sTmp) <> 0& Then
            GetShellFolderPath = Left$(sTmp, InStr(1, sTmp, vbNullChar) - 1)
        End If
    End If
    If pID <> 0& Then GlobalFree pID
End Function



Public Sub SaveFile_Click()

    Const CSIDL_DESKTOP As Long = &H0
    
    ThisWorkbook.SaveAs Filename:=GetShellFolderPath(CSIDL_DESKTOP) & "\VendPerf" & Format(Date, "mm-dd-yy") & ".xls", FileFormat:=56
    
End Sub

Would it make it easier to create a folder on the C: drive and save it there? One other thing that I need to consider is that these same people will be saving multiple times. I only want to create the new folder once (if it isn't there already). After that, I just want them to save to the already existing folder.

Sure, it would... but, now I've shown you the code to locate the desktop, are you happy with that?

Ah, no problem. Give this a try. Change VendorPerformanceReports to whatever folder name you want. It will only create the folder once.

'http://www.vbforums.com/showthread.php?t=537283
Private Declare Function MakeSureDirectoryPathExists Lib "imagehlp.dll" _
   (ByVal lpPath As String) As Long

Private Function CreateFolder(ByRef strFolder As String) As Boolean
   CreateFolder = MakeSureDirectoryPathExists(strFolder & "\")
End Function


Public Sub SaveFile_Click()

    Const strFOLDERPATH As String = "C:\VendorPerformanceReports"
    
    CreateFolder strFOLDERPATH
    ThisWorkbook.SaveAs Filename:=strFOLDERPATH & "\VendPerf" & Format(Date, "mm-dd-yy") & ".xls", FileFormat:=56
    
End Sub