Returning the first part of a workbook name

  • Thread starter Thread starter Ed Davis
  • Start date Start date
E

Ed Davis

Is there a way to return the workbook name up until the first space.
All my workbooks have a space in their names.
example:
Sales 09-2009
Loja Work 09-2009

I am looking to get exverything up to the first space.

I tried this code and get a compile error on the Find.

WBook.SaveCopyAs WBook.Path & "\Backup\" & Left(WBook, Find(" ", WBook)) &
"\" & Left(WBook, Find(" ", WBook)) & _
WBook.ActiveSheet.Name & ")" & Format(Now, " dd-mm-yy hh-mm-ss") &
".xls"
 
If WBook is the workbook object, then WBook.Name returns the name of
the workbook.

So everything up to the first space would be

Left$(WBook.Name, WorksheetFunction.Find(" ", WBook.Name) -1)

--JP
 
You might as well use the built-in VBA function InStr instead of calling out
to the FIND worksheet function...

TextBeforeSpace = Left(WBook.Name, InStr(WB, " ")-1)

There is also another way to do this as well...

TextBeforeSpace = Split(WBook.Name)(0)

--
Rick (MVP - Excel)


If WBook is the workbook object, then WBook.Name returns the name of
the workbook.

So everything up to the first space would be

Left$(WBook.Name, WorksheetFunction.Find(" ", WBook.Name) -1)

--JP
 
Good point, my first instinct was to use the OP's function instead of
the VBA equivalent.

--JP
 
I tried this and got a compile error variable not defined on the
TextBeforeSpace.
This is what I added to my code:
What I want to do is to save a copy of the workbook in the following manner.
(backup\Newname\NewName mm-dd-yy hh-mm-ss

Dim NewName as String
TextBeforeSpace = Left(WBook.Name, InStr(WBook, " ") - 1)
NewName = TextBeforeSpace

WBook.SaveCopyAs WBook.Path & "\Backup\" & NewName & "\" & NewName & "Normal
Close (" & WBook.ActiveSheet.Name & ")" & Format(Now, " dd-mm-yy hh-mm-ss")
& ".xls"
--
Thank You in Advance
Ed Davis
Rick Rothstein said:
You might as well use the built-in VBA function InStr instead of calling
out to the FIND worksheet function...

TextBeforeSpace = Left(WBook.Name, InStr(WB, " ")-1)

There is also another way to do this as well...

TextBeforeSpace = Split(WBook.Name)(0)

--
Rick (MVP - Excel)


If WBook is the workbook object, then WBook.Name returns the name of
the workbook.

So everything up to the first space would be

Left$(WBook.Name, WorksheetFunction.Find(" ", WBook.Name) -1)

--JP
Click to expand...
 
Tried this and got the following error:
Run-time error '1004':
Unable to get the Find property of the WorksheetFunction Class
This is the code I put in.

NewName = Left$(WBook.Name, WorksheetFunction.Find(" ", WBook.Name) - 1)

--
Thank You in Advance
Ed Davis
If WBook is the workbook object, then WBook.Name returns the name of
the workbook.

So everything up to the first space would be

Left$(WBook.Name, WorksheetFunction.Find(" ", WBook.Name) -1)

--JP
 
I made up the variable name TextBeforeSpace just to make the functionality
more descriptive... you can assign it to any variable you want. So....

NewName = Left(WBook.Name, InStr(WBook, " ") - 1)

or, if you wanted to go with my alternative function...

NewName = Split(WBook.Name)(0)

Just so you know, you got the error because you have an Option Explicit
statement at the top of your procedure (this is a GOOD thing to do, so don't
remove it)... it alerts you when you try to use a variable that has not been
declared. Your NewName variable does not raise any errors because you
Dim'med it as a String; my TextBeforeSpace variable raised the error because
you did not Dim it before using it.

--
Rick (MVP - Excel)


Ed Davis said:
I tried this and got a compile error variable not defined on the
TextBeforeSpace.
This is what I added to my code:
What I want to do is to save a copy of the workbook in the following
manner. (backup\Newname\NewName mm-dd-yy hh-mm-ss

Dim NewName as String
TextBeforeSpace = Left(WBook.Name, InStr(WBook, " ") - 1)
NewName = TextBeforeSpace

WBook.SaveCopyAs WBook.Path & "\Backup\" & NewName & "\" & NewName &
"Normal Close (" & WBook.ActiveSheet.Name & ")" & Format(Now, " dd-mm-yy
hh-mm-ss") & ".xls"
Click to expand...
 
I also tried this:
NewName = Left(WBook.Name, InStr(WBook, " ") - 1)

I get this error:
Run-time error '438'
Object doesn't support this property or method



--
Thank You in Advance
Ed Davis
Ed Davis said:
I tried this and got a compile error variable not defined on the
TextBeforeSpace.
This is what I added to my code:
What I want to do is to save a copy of the workbook in the following
manner. (backup\Newname\NewName mm-dd-yy hh-mm-ss

Dim NewName as String
TextBeforeSpace = Left(WBook.Name, InStr(WBook, " ") - 1)
NewName = TextBeforeSpace

WBook.SaveCopyAs WBook.Path & "\Backup\" & NewName & "\" & NewName &
"Normal Close (" & WBook.ActiveSheet.Name & ")" & Format(Now, " dd-mm-yy
hh-mm-ss") & ".xls"
Click to expand...
 
What is WBook declared (Dim'med) as? It should be this...

Dim WBook As Workbook

Then, in your code someplace, you would need to set a reference for it;
perhaps this for the current workbook...

Set WBook = ThisWorkbook

or for another workbook...

Set WBook = Workbooks("Some Other Workbook.xls")

where you would use your workbooks actual filename inside the quote marks.

--
Rick (MVP - Excel)


Ed Davis said:
I also tried this:
NewName = Left(WBook.Name, InStr(WBook, " ") - 1)

I get this error:
Run-time error '438'
Object doesn't support this property or method
Click to expand...
 
I do have Wbook dim'med as workbook
It is working in the same procedure a few times.
I have newname dim'med as string
 
Make sure you specify the property you want (wbook.name in both spots).

TextBeforeSpace = Left(WBook.Name, InStr(WBook.Name, " ") - 1)

I think I'd use:

TextBeforeSpace = Left(WBook.Name & " ", InStr(WBook.name & " ", " ") - 1)

just in case there are no spaces in the name to start. (No, I didn't read the
whole thread.)

Ed said:
I tried this and got a compile error variable not defined on the
TextBeforeSpace.
This is what I added to my code:
What I want to do is to save a copy of the workbook in the following manner.
(backup\Newname\NewName mm-dd-yy hh-mm-ss

Dim NewName as String
TextBeforeSpace = Left(WBook.Name, InStr(WBook, " ") - 1)
NewName = TextBeforeSpace

WBook.SaveCopyAs WBook.Path & "\Backup\" & NewName & "\" & NewName & "Normal
Close (" & WBook.ActiveSheet.Name & ")" & Format(Now, " dd-mm-yy hh-mm-ss")
& ".xls"
Click to expand...
 
Okay, if I understand your problem correctly, you are Dim'ming WBook in one
procedure but trying to use it in a different procedure, right? If so, move
the Dim statement for WBook out of the procedure and put it in the
(General)(Declaration) section of the code window (that is, put it at the
top of the code window outside of any procedures); that way any procedure
will be able to see it. You can also add a Module to your project and
declare it there instead (this will give it an even wider scope). You can
read a little more about scoping issues by putting this...

"Understanding Scope and Visibility"

(with the quote marks) in the search field of the VB editor and then select
the item of the same name from the list that appears.
 
Dave pointed out the problem which I completely overlooked... I forgot to
specify the Name property on the second WBook reference (actually, my
original code used the wrong variable name as well, but I see you fixed that
part of my code). The code line should have been...

NewName = Left(WBook.Name, InStr(WBook.Name, " ") - 1)

That should fix the error.... sorry for any confusion my error caused you.

--
Rick (MVP - Excel)


Ed Davis said:
I also tried this:
NewName = Left(WBook.Name, InStr(WBook, " ") - 1)

I get this error:
Run-time error '438'
Object doesn't support this property or method
Click to expand...
 
Got it to work using Dave Peterson's code thank you everyone for your help.
It is greatly appreciated.
 
Back
Top