+ Reply to Thread
Results 1 to 10 of 10

I need to use a date as part of my .xls filename.

  1. #1
    Registered User
    Join Date
    03-10-2011
    Location
    North Carolina, USA
    MS-Off Ver
    Excel 2007
    Posts
    6

    I need to use a date as part of my .xls filename.

    Greetings all, thank you for your help in advance!

    I have a macro that creates a separate .xls file for each of my worksheets.
    That macro uses cell B1, the worksheet name, and cell B13 to create the file's name.
    For example if in B1 the value was 1000 and the worksheet was named Charles and B13 has the value of GL the filename would be 1000_Charles_GL.xls

    My problem is that if either B1 or B13 is in the DATE format, I get Run-time error 1004.
    If you want to see it for yourself, I did a screen grab:
    http://dclicc.squarespace.com/storag...ments/1004.bmp

    I think the problem is that windows (I have XP) will not allow a file to be created with "/" marks, and excel wants to use them? I have tried changing the format to dates with dashes, but it doesn't help. If I change the catergory/format to general it will work, but of course that would give me a useless number. I DO NEED the date in the file name.

    I posted the macro below if that will help.

    Again, thanks!
    - Skot
    Please Login or Register  to view this content.
    Last edited by Skot; 03-11-2011 at 11:39 AM. Reason: Added Code Tags

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: I need to use a date as part of my .xls filename.

    Hello Skot,

    Welcome to the Forum!

    This will replace the forward slash ( "/") characters with underscores ("_") in the date.

    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: I need to use a date as part of my .xls filename.

    Please Login or Register  to view this content.



  4. #4
    Registered User
    Join Date
    03-10-2011
    Location
    North Carolina, USA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: I need to use a date as part of my .xls filename.

    Leith,

    Your code did allow the value of B13 (a date) to go into the file name, so I see it can be done. WOO HOO!

    HOWEVER, now, only the value of B1 and B13 of the ACTIVE WORKSHEET is now being used. Which means for every .xls file created the parts from B1 and B13 are the same.

    My directory looks like this:

    1590_John_1_16_2011.xls
    1590_Carol_1_16_2011.xls
    1590_Paul_1_16_2011.xls
    1590_Mark_1_16_2011.xls
    1590_Leon_1_16_2011.xls
    etc...

    When it should look like this...

    1590_John_1_16_2011.xls
    1631_Carol_1_12_2011.xls
    2159_Paul_1_10_2011.xls
    0090_Mark_1_13_2011.xls
    1430_Leon_1_14_2011.xls

    Again, thank you for your time, it is clear you are very talented!
    -Skot
    Last edited by Skot; 03-11-2011 at 09:25 AM.

  5. #5
    Registered User
    Join Date
    03-10-2011
    Location
    North Carolina, USA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: I need to use a date as part of my .xls filename.

    SNB,

    This did not work. I defined sh as a worksheet, but still Excel had problems with the .SaveAs line. "Compile error Syntax Error"
    THANK YOU for your help.

    - Skot
    Last edited by Skot; 03-11-2011 at 09:25 AM.

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: I need to use a date as part of my .xls filename.

    Skot, please don't quote whole replies unless necessary.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  7. #7
    Registered User
    Join Date
    03-10-2011
    Location
    North Carolina, USA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: I need to use a date as part of my .xls filename.

    Quote Originally Posted by royUK View Post
    don't quote whole replies
    I seem to have made every mistake possible. Sorry everyone!

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: I need to use a date as part of my .xls filename.

    Your problem with Leith's code had been solved by mine: cfr the dots before .
    Range()
    You could have removed the superfluous comma in the SaveAs-line yourself.

    Please Login or Register  to view this content.
    Last edited by snb; 03-11-2011 at 09:55 AM.

  9. #9
    Registered User
    Join Date
    03-10-2011
    Location
    North Carolina, USA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: I need to use a date as part of my .xls filename.

    Quote Originally Posted by snb View Post
    you could have removed the superfluous comma in the saveas-line yourself.
    oh! I see! Thank you!

  10. #10
    Registered User
    Join Date
    03-10-2011
    Location
    North Carolina, USA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: I need to use a date as part of my .xls filename.

    Okay, now Excel is just messing with me.
    In my test file (named test2.xls - very original eh?) I was able to make the following code work PERFECTLY!

    Please Login or Register  to view this content.

    I was happy, I was doing the dance of joy, LIFE WAS GOOD!

    Then I tried my macro in a "read-world" file and I get

    EDIT: If you want to see the error for yourself:
    http://dclicc.squarespace.com/storag.../1004_copy.bmp

    Run-Time error '1004':
    Method 'Copy' of object'_worksheet' failed
    I click on Debug and the line
    w.Copy
    is in yellow!!!!!!

    The difference between my test file and my real world file was minimal.
    I though maybe the real world file had too many tabs, but I tried again and again, with fewer and fewer worksheets and that wasn't it.


    What did I do wrong?
    Thanks again.
    -Skot


    ANSWER: I had a hidden worksheet. Once that was visible, it works all day long! THANK YOU ALL!

    PS - How do you guys keep your sanity?
    Last edited by Skot; 03-11-2011 at 11:38 AM.

+ 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