Use part of a cell for filename (2007)

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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.)

Reply | Quote

Sorry, that’s what you get if you post air code. It should have been

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

Reply | Quote

That worked.

Thanks a bunch!

Reply | Quote

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?

Reply | Quote

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 &.

Left(Range("G2"), InStr(Range("G2"), ",") - 1) & Mid(Range("G2"), InStr(Range("G2"), ",") + 2, 2)

Reply | Quote

Thank you. I appreciate the explanation.

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

InStr(Range(“G2”), “,”) + 2, 2[/i])

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Formula Example:

Reply | Quote