Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    paste into 'save as' dialog box (2000 / 2003)

    I want to name my workbook after the contents of a cell in sheet 1. When I copy and paste, it won't accept the pasted text.
    I would use the method in a macro, but the macro recorder dos'nt help.
    Is there a way to paste into a dialog box, such as 'save as' using a macro or (simple) VBA?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: paste into 'save as' dialog box (2000 / 2003)

    Could it be that the name in the cell is not valid? File and folder names can't contain any of the following characters: <code> / ? * : " < > | </code>. Apart from that, you can use code like this:

    ActiveWorkbook.SaveAs Filename:=Worksheets("Sheet1").Range("A3")

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: paste into 'save as' dialog box (2000 / 2003)

    Thanks Hans, just the help I needed.
    The filename is, being of the form '2005 Workbook AB9876' which works as a filename when saving by hand.

  4. #4
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: paste into 'save as' dialog box (2000 / 2003)

    Hello again. Sorry to not have thought ahead and asked this one at the same time. I need to save to a particular folder, in this case a shared folder on the server. How can I adapt the code to save to a named folder, which will always be the same, as well as pick up the filename from the range?

    Thanks in advance

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: paste into 'save as' dialog box (2000 / 2003)

    If Worksheets("Sheet1").Range("A3") contains just the filename you could just use something like:

    ActiveWorkbook.SaveAs Filename:="C:myPath" & Worksheets("Sheet1").Range("A3")

    Or even read the path ffrom a variable or a cell.

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: paste into 'save as' dialog box (2000 / 2003)

    You answered my question perfectly. I tried several options, but the '&' between the path name and filename I would never have found on my own. Many thanks.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: paste into 'save as' dialog box (2000 / 2003)

    The ampersand (&) is used to combine (concatenate) text strings in both VB and in excel (as well as many other programs)

    FYI, they symbol itself is a "stylized" combination of a script "Et" which is latin for "and" (some fonts actually look much more like and "E" and a "t". (I am a wealth of useless knowledge... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>)

    Steve

Posting Permissions

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