Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use part of a cell for filename (2007)

    Hello,

    I'm using the following code to save an Excel file:

    ActiveWorkbook.SaveAs "C:temp" & Range("H2").Value & "-" & Format(Date, "mmm dd, yyyy")
    (the cell reference refers to a unique number)

    Everything is working great, and it's doing what I want, but now I've found out that my coworkers want to include more information in the name of the file. No problem grabbing another cell reference, but what they need is only part of a cell. And the part of the cell needed varies from one day to the next.

    The cell I will be pulling from is "G2" and is a city and state followed by hyphen and then a short description. I just need the city and state.

    I know I can use a formula in the spreadsheet itself to pull the information I need, but I'm wondering if there is a way to put it into some code and avoid cluttering up the spreadsheet.


    Here are a couple of examples:

    Madison, WI - Run around in circles
    San Diego, CA - Jump up and down
    Truth or Consequences, NM - Sit down on the couch

    I just need the city and state (everything before the hypen)

    Any ideas on grabbing what I need using VBA?

    Thanks!

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

    Re: Use part of a cell for filename (2007)

    The following expression will extract the part of the value of G2 before the hyphen:

    Left(Range("G2"), InStr("Range("G2"), "-") - 1)

    If you want to omit the space after the state, replace -1 with -2.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use part of a cell for filename (2007)

    One method would be to find the comma such as in Madison, WI

    Formula Example:
    Attached Images Attached Images

  4. #4
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use part of a cell for filename (2007)

    I tried this and it highlighted the 2nd "G2" and gave me a "Compile error: Expected: listed separator" error message.

    ActiveWorkbook.SaveAs "C:temp" & Left(Range("G2"),InStr("Range("G2"), "-") - 1)

    (Trying to get just the city and state for now...will add in the rest later since it already works.)

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

    Re: Use part of a cell for filename (2007)

    Sorry, that's what you get if you post air code. It should have been
    <code>
    Left(Range("G2"), InStr(Range("G2"), "-") - 1)</code>

  6. #6
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use part of a cell for filename (2007)

    That worked.

    Thanks a bunch!

  7. #7
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use part of a cell for filename (2007)

    Still working great. Had a request to get rid of the comma and space and just jam it all together (Example: MadisonWI)

    I've been able to extract the characters from a certain point by changing the number at the end of the formula. I experimented a bit and noticed I can add additional numbers to the end of the formula, but I couldn't really tell what they were doing.

    Here is what is working right now:

    Left(Range("G2"), InStr(Range("G2"), "-") - 1 )


    I noticed I can do this:
    Left(Range("G2"), InStr(Range("G2"), "-") - 1 - 1)

    Or this:
    Left(Range("G2"), InStr(Range("G2"), "-") - 1 +4)

    I didn't know if I could tell it how many characters to extract, or when to stop extracting.

    Is that possible?

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

    Re: Use part of a cell for filename (2007)

    InStr(Range("G2"), "-") returns the position of the - in the cell value: if it returns 7, it means that - is the 7th character of the cell value.

    Left(Range("G2"), n) returns the first n characters of the cell value.

    So Left(Range("G2"), InStr(Range("G2"), "-")) would return the part of the cell value up to and including the -. And Left(Range("G2"), InStr(Range("G2"), "-") - 1) returns the part before the -.

    To get the place name, you could use Left(Range("G2"), InStr(Range("G2"), ",") - 1)
    To get the state, you could use Mid(Range("G2"), InStr(Range("G2"), ",") + 2, 2) i.e. get to characters starting 2 positions past the comma.
    You can concatenate the results using &.
    <code>
    Left(Range("G2"), InStr(Range("G2"), ",") - 1) & Mid(Range("G2"), InStr(Range("G2"), ",") + 2, 2)</code>

  9. #9
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use part of a cell for filename (2007)

    Thank you. I appreciate the explanation.

    The second number indicates how many characters to get? Is that right?

    InStr(Range("G2"), ",") + 2, 2)

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

    Re: Use part of a cell for filename (2007)

    Yes, that's true: the syntax of the Mid function is Mid(TextValue, StartPosition, NumberOfCharacters). Click in Mid in the Visual Basic Editor and press F1 for more info.

  11. #11
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use part of a cell for filename (2007)

    This has been working great and I have been using this quite regularly. Thank you very much.

    Another function I'd like to add is saving the file to a specific folder based on the state. I've tried placing bits of this code into the SaveAs path, but it doesn't like the quotation marks. Is it possible to save this to a dynamic location based on the state?

    Thanks again.

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

    Re: Use part of a cell for filename (2007)

    Let's say that if G2 contains Madison, WI - Run around in circles, you want to store the file in C:WI. You could use something like this:

    ActiveWorkbook.SaveAs "C:" & Mid(Range("G2"), InStr(Range("G2"), ",") + 2, 2) & _
    "" & Left(Range("G2"), InStr(Range("G2"), ",") - 1) & Mid(Range("G2"), InStr(Range("G2"), ",") + 2, 2) & ".xls"

    In the example, this would result in a file

    C:WIMadisonWI.xls

  13. #13
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use part of a cell for filename (2007)

    Thank you very much. Got it to work as needed.

Posting Permissions

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