Solved: seperating folder and filename

[drawworkhome]

hi all,
i am having a heck of time trying to seperate the folder name and file name from the activeworkbook.name.

any recommendations?

thank you.:banghead:

[georgiboy]

I thought "ActiveWorkbook.Name" only give you the filename anyway?

[drawworkhome]

i came up with this.Function split_jobname_and_number()

Dim szBookName As String
Dim a
a = Len(ActiveWorkbook.name)
szBookName = Left(ActiveWorkbook.name, a - 3)

a = Split(szBookName, " ")
writepo.jobnumberTextBox.Value = a(1)
writepo.jobnametextbox.Value = UCase(a(0))

End Function
is there a better, cleaner way?
regards!
ps, georgiboy, you are correct, i saved my workbook with the folder and filename combined. duh!
the workbook name for example is "testjob #1234".

[drawworkhome]

just noticed that i used the variable "a" twice--bad.
e

[GTO]

For stripping the extension, you might wish to use instrrev. This way the code will 'self-adjust' for 4-digit extensions.

Option Explicit

Function split_jobname_and_number()
Dim ary As Variant

ary = Split(Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1))

writepo.jobnumberTextBox.Value = ary(1)
writepo.jobnametextbox.Value = UCase(ary(0))
End Function
Sub exa()
Hope that helps,

Mark

[georgiboy]

Very nice Mark, this is better than my approach...
Dim awName As String

awName = ActiveWorkbook.Name

JobName = UCase(Left(awName, InStr(awName, " ")))
JobNum = Replace(Right(awName, InStrRev(awName, " ")), ".xls", "")

[drawworkhome]

thank you mark.
all info is helpful cause sometimes i dont ask the correct question the first time.
hope all is well.
erik

[GTO]

Thank you George, although in fairness, I am certain I piccked that up from Bob (xld). (Funny how some little things I can remember 'for sure' and other stuff falls outta my head faster than it went in...)

Hi Erik,

All is well, thank you.

Say, I noticed this before and forgot to mention. You could run afoul if by chance the workbook's name does not have a space, as a 'Subscript out of range' error will be produced by ary(1).

Maybe add an IF test like below. This code of course just to demo...


Sub exaxx()
Dim _
ary As Variant, _
lDelim As Long

If MsgBox("Use a space as delimiter? (Select <No> to use asterick)", _
vbYesNo, vbNullString) = vbYes Then
lDelim = 32
Else
lDelim = 42
End If

ary = Split(Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1), Chr(lDelim))

If UBound(ary) - LBound(ary) >= 1 Then
Debug.Print ">" & ary(1) & "<"
End If

Debug.Print ">" & ary(0) & "<"
End Sub

Mark