Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SaveAs Code (2003)

    Hello All

    Please could you help me with some code. I'm trying to save an excel workbook using some code but I want the file name to equal the value of a cell. I've got this far but I'm not sure how to include the variable "name" in the SaveAs line

    Range("G2").Select
    name = ActiveCell
    ActiveWorkbook.SaveAs Filename:="H:winwordHaydenNew Foldername.xls,"

    Many thanks as always

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

    Re: SaveAs Code (2003)

    "Name" is not a very good name for a variable, since it is a property of many objects. Try this:

    Dim strName As String
    strName = Range("G2")
    ActiveWorkbook.SaveAs Filename:="HWinwordHaydenNew Folder" & strName & ".xls"

    Note that it isn't necessary to select G2, you only refer to it.

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SaveAs Code (2003)

    ActiveWorkbook.SaveAs Filename:="H:winwordHaydenNew Folder" & Range("G2").Value & ".xls"

    should do it. There is no need to to use "Select" or "ActiveCell" in your code.

    Alan

  4. #4
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SaveAs Code (2003)

    Hi,

    Activeworkbook.SaveAs ("H:winwordHaydenNew Folder" & range("g2").value & ".xls")

    Wolf

  5. #5
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SaveAs Code (2003)

    Thanks very much Hans and Alan

    Just one more thing please, how can I get my Personal.xls to open automatically when I start Excel?

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

    Re: SaveAs Code (2003)

    If Personal.xls is in the right location, it'll always be opened automatically with Excel, but it should be invisible. You can check that it's loaded in the Visual Basic Editor, in the Project Explorer (the treeview on the left hand side).

    The standard location for Personal.xls is Cocuments and Settings<username>Application DataMicrosoftExcelXLSTART. If you have specified a folder in the "At startup, open all files in:" box in the General tab of Tools | Options... in Excel itself, Personal.xls can be in that folder.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SaveAs Code (2003)

    Thanks again Hans,

    The General tab ..... Tools > Options bit is what I was missing.

    Many thanks

Posting Permissions

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