Thread: Set current filename in VBA macro

Is there any way to set the filename in a VBA macro, so that when a user clicks Save or SaveAs, it defaults it to that filename?

We are generating an invoice number in a macro that automatically runs when a workbook is created. We would like the filename to automatically include that number when the user saves the file.

I tried setting .Name and .FullName, but both are read-only.

I know I could have it do a SaveAs after the invoice number is generated, but I don't necessarily want the file saved every time. For example, if someone opens it, then decides to not create the invoice, I don't want a blank invoice saved. I only want it saved when they click Save or Save As, but I want to default the filename.

Hi, this should get you started.

 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strMyDefaultName As String, SaveFileName As Variant, RetVal As Variant

    'Prevent this code from running itself
    Application.EnableEvents = False
    
    'Prevent default dialog box appearing
    Cancel = True
    
    'Your text you want to use as a default
    strMyDefaultName = "Invoice123.xls"
    
    'Ask user for filename/path to save
    SaveFileName = Application.GetSaveAsFilename(strMyDefaultName, "Microsoft Excel Workbook (*.xls),*.xls")
    
    'User clicked cancel
    If SaveFileName = False Then
        GoTo CleanUp
    End If
    
     ActiveWorkbook.SaveAs Filename:=SaveFileName

CleanUp:
    'Turn events back on
    Application.EnableEvents = False

End Sub

Thanks, that works with generating the filename.

My next problem is that the above code seems to default to saving it on the Desktop.

To complicate matters, I need this file to be saved in a SharePoint document library.

Originally Posted by mannaggia

My next problem is that the above code seems to default to saving it on the Desktop.

In the default filename include the path to the default directory you want to use. Example

 strMyDefaultName = "C:\Temp\Invoice123.xls"

Originally Posted by mannaggia

To complicate matters, I need this file to be saved in a SharePoint document library.

A different kettle of fish altogether. Sharepoint is an application not simply a location so you would need to interact with it. I use Sharepoint at work but it's a tad flaky and I wouldn't want to be tasked with using automation with it. I cannot help you with that piece but you may want to try searching for answers on a specific Sharepoint forum.

regards,
Graham

That works with SharePoint too. SharePoint looks like a WebDAV share, so I just did something like this:

strMyDefaultName = "http://MySharepointServer.mydomain.com/SiteDirectory/AR/Invoice%20Library/Invoice123.xls"

I thought I tried this before I posted my second question, but I guess not because it works.

I also needed to save as .xlsm instead of .xlsx (and add FileFormat:=52 to the SaveAs call) since I am using Excel 2007. At least it appears this is required.

Unfortunately, it appears that I need to code the path to the SharePoint Library in my macro, although I am clicking "New" in the SharePoint library. I need to do a little more debugging to see if I can avoid that one.

Originally Posted by parrynz

In the default filename include the path to the default directory you want to use. Example

 strMyDefaultName = "C:\Temp\Invoice123.xls"

How would this code be modified to be able to grab a cell's value to save as filename?