+ Reply to Thread
Results 1 to 6 of 6

Set current filename in VBA macro

  1. #1
    Registered User
    Join Date
    04-26-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    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.

  2. #2
    Registered User
    Join Date
    04-06-2010
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Set current filename in VBA macro

    Hi, this should get you started.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-26-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Set current filename in VBA macro

    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.

  4. #4
    Registered User
    Join Date
    04-06-2010
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Set current filename in VBA macro

    Quote Originally Posted by mannaggia View Post
    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
    Please Login or Register  to view this content.
    Quote Originally Posted by mannaggia View Post
    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

  5. #5
    Registered User
    Join Date
    04-26-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Set current filename in VBA macro

    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.

  6. #6
    Registered User
    Join Date
    05-05-2010
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Cool Re: Set current filename in VBA macro

    Quote Originally Posted by parrynz View Post
    In the default filename include the path to the default directory you want to use. Example
    Please Login or Register  to view this content.
    How would this code be modified to be able to grab a cell's value to save as filename?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1