Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating filename (Excel 2000)

    I have two cells range named JDESC and JNAME. I want to create a file save as combining them and a spacer. I have this idea:
    Sub NameAndSave ()
    Dim Createdfilename as Variant
    createdfilename=range("JDESC").Formula & " " & range("JNAME).Formula
    Application.getSaveAsFilename (createdfilename)
    End SUb

    I get precious little help from those hint boxes before they disappear from view. I fill out what I think its asking for. I use the Locals window to follow along but that does not show the source of the problem as far as I can tell....


    Of curse, I get a runtime 1004 Method 'Range' object '_Global' failed. Now what? Isn't a cell a range? Isn't the contents supposed to be the Formula? Isn't the & the concatenation? Why is it hard to build such expressions? What is preventing me from understanding how to describe the step I want VBA to take. Other than the obvious answer, that is....

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Creating filename (Excel 2000)

    The formulas you are using may have restricted characters in them.
    A file name cannot contain: " / : * ? < > | "

    Regards,

    JIm Cone
    San Francisco, CA

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating filename (Excel 2000)

    Do you reallise, that the GetSaveAsFilename method only returns a name and does NOT actually save?

    Also: when using Range("jdesc") without sheet and workbook qualifier, the code falls over when the wrong workbook is selected, because the code tries to find a name called jdesc in the active workbook. So this code is better:

    Sub NameAndSave()
    Dim Createdfilename As Variant
    Dim vFilename As Variant
    Createdfilename = ThisWorkbook.Names("JDESC").RefersToRange.Formula & " " & ThisWorkbook.Names("JNAME").RefersToRange.Formula
    vFilename = Application.GetSaveAsFilename(Createdfilename)
    If TypeName(vFilename) = "Boolean" Then Exit Sub
    If vFilename = "" Then Exit Sub
    ThisWorkbook.SaveAs vFilename
    End Sub

    Replace Thisworkbook with for example Workbooks("AnyOtherBook") when the code is to reside in another workbook than the one that holds the named ranges.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating filename (Excel 2000)

    Thanks. I want the user to acknowledge the save in case s/he needs to change the directory before actually saving...and how was I supposed to know to use the description you used instead of the .formula code? THis is where this stuff drives you crazy. Was it because I used a range name that I could not then use the .formula property? This stuff sometimes does not seem logically organized to a civilian user.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating filename (Excel 2000)

    No, the range("blah").Formula normally should work, but since you omitted what WORKBOOK that range belongs to, VBA assumed it is the ACTIVE workbook. If the active workbook does not contain a named range "Blah" you get the error you described.

    But once you specify the workbook, you also have to specify the worksheet:

    Workbooks("Whatever.xls").Worksheets("WhicheverShe et").Range("Blah").Formula

    But in that case the name MUST refer to a cell on the sheet called "WhichEverSheet". If the name refers to another sheet, you get the same error message again.

    My method doesn't care what sheet the name refers to, it always uses the correct one.

    This is information I've learned the hard way, mostly by programming the NameManager, which can be downloaded from "The Excel MVP page" below.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating filename (Excel 2000)

    Thanks..I just don't seem to see that long qualifying prelude to other range names...perhaps because they're different in some way. Now, it's on to the next stumbling block....In another thread I wrestled with the problem that appears here again, that if I run the program up to this point, and the dialog box is open, I can navigate to some other folder (let's say, this month's folder being populated with workbooks) but then get a "You can't save two files with the same name" argument...as if Excel doesn't "see" that I'm at another place...I think it has something to do with the GetSaveFilename vs FileSaveAs methods. Right?

  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: Creating filename (Excel 2000)

    You can save 2 files with the same name in different locations. Excel will NOT prevent you from doing that.

    Excel WILL prevent you from having 2 FILES OPEN with the SAME NAME even if they are in different folders.

    So if you have a file named FileA open that is from c:FolderQ and you create a NEW file and try to SAVE it into the folder c: and want to name it FileA, excel will NOT let you since you would have 2 FileAs OPEN. If you close the first fileA BEFORE you saveAS the New FileA you can save it without a problem.

    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
  •