+ Reply to Thread
Results 1 to 12 of 12

Rename file with saveas using string variables in VBA

  1. #1
    Registered User
    Join Date
    03-28-2015
    Location
    Miami Beach, Florida
    MS-Off Ver
    Office 365
    Posts
    6

    Rename file with saveas using string variables in VBA

    Hello,

    1. I need to know how to use save as to save and rename a file on my desktop using string variables. I want the name of the file to be MerchantName (a string variable, whose name is taken from a cell in the excel document) and MerchantDate (a string variable that will be the prior month of this year; with only month and year, no day; so it would be 2/15 for example). These excel files are being stored in a folder and I need to differentiate them by Merchant name and Month of sale.

    I tried to use this code: ActiveWorkbook.SaveAs "MyFile.xls" and converted it to: Activeworkbook.SaveAs MerchantName & MerchantDate & ".xls" but it is not working. I need the correct syntax here....

    2. I am creating this for my business, so it will be saved as a PDF file on different computers with different users. I see in the code when I save the file using a Macro that the following code appears.

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\Jeremiah\Desktop\Master.pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    True

    If the user changes (from Jeremiah) to another username, will the code run incorrectly? How do I create an ambiguous name in the code? I may be able to cut it down to be run on only one computer with one user; but in that case I would need to change the: "C:\Users\Jeremiah\Desktop\Master.pdf" to "C:\Users\"NAME OF USER"\Desktop\Master.pdf". Correct?

    Please let me know. Thank you,

    Jeremy
    Last edited by jyfuller; 03-28-2015 at 06:06 PM.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Rename file with saveas using string variables in VBA

    You can't have slashes in the Book Name - changing them to dashes should work:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    03-28-2015
    Location
    Miami Beach, Florida
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Rename file with saveas using string variables in VBA

    Ok. I will try . Did you see part 2? What if the username changes for saving as pdf file. Will that affect ability to save and cause an error? Also is my syntax for naming and saving the book correct? Please let me know. Thanks alot!

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Rename file with saveas using string variables in VBA

    Hi JY,

    Thanks for the rep!

    The path is everything up to the last backslash - I don't know if each user has his/her own directory? If so you must alter the path for the Fullname.

  5. #5
    Registered User
    Join Date
    03-28-2015
    Location
    Miami Beach, Florida
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Rename file with saveas using string variables in VBA

    No problem. I understand how important reputation is as I also work online .

    I am sending the excel file to another person to use on their computer. They will have a different username for their computer. I have run a macro for save as, and need to know whether to change the name Jeremiah after the second backslash to the username of the person I am sending the file to. Should I change the following for the code when saving on the desktop:

    change the: "C:\Users\Jeremiah\Desktop\Master.pdf" TO "C:\Users\"NAME OF USER"\Desktop\Master.pdf".

    So the name Jeremiah after the second backslash, should change to the username of the user's computer that is using the file. In order for the VBA code to save the file directly on their desktop this must be altered, correct?

    Thank you for your help. The last code change worked beautifully.

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Rename file with saveas using string variables in VBA

    Maybe:


    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    03-28-2015
    Location
    Miami Beach, Florida
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Rename file with saveas using string variables in VBA

    Yes. I added it in as a string variable to the save as code and it worked for saving as an excel file. Thanks.

    One more thing. when I save as a pdf I'm getting an error. Here is the code.

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\" & Username & "\Desktop\" & BatchDate & " " & Merchant & ".pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    True

    Username is the string for username using the Environ code; it gives me this error for pdf. runtime error 2147018887(80071779); what is wrong with the code? Thanks so much.

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Rename file with saveas using string variables in VBA

    Maybe:

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\ Username \Desktop\BatchDate & " " & Merchant & .pdf, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= True

  9. #9
    Registered User
    Join Date
    03-28-2015
    Location
    Miami Beach, Florida
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Rename file with saveas using string variables in VBA

    Ok. I will try it. Ill let you know if it works... Thanks so much

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Rename file with saveas using string variables in VBA

    If you want the path to DeskTop then try
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    03-28-2015
    Location
    Miami Beach, Florida
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Rename file with saveas using string variables in VBA

    Thank u i will try that. Thanks a lot.

  12. #12
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Rename file with saveas using string variables in VBA

    Hope it works!


    *Oops - I was unaware of Jindon's offering, I expect his code to always work
    Last edited by xladept; 03-29-2015 at 02:20 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Attachments : Save and Rename using Sublect string or File name string
    By checksamir in forum Outlook Programming / VBA / Macros
    Replies: 4
    Last Post: 11-13-2014, 01:13 AM
  2. Excel VBA find and replace string in non text file and rename file
    By razzack in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-01-2013, 02:43 PM
  3. [SOLVED] SaveAs does not work with variables for Path and Filename?
    By steveJC in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-03-2012, 04:55 AM
  4. Syntax for SAVEAS code using variables for FileFormat and file suffix
    By vcchin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-20-2010, 11:48 PM
  5. [SOLVED] string variables in file names
    By dr chuck in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-01-2006, 03:00 PM

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