View Full Version : Solved: seperating folder and filename
drawworkhome
02-07-2010, 08:49 PM
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
02-07-2010, 09:11 PM
I thought "ActiveWorkbook.Name" only give you the filename anyway?
drawworkhome
02-07-2010, 09:11 PM
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
02-07-2010, 09:17 PM
just noticed that i used the variable "a" twice--bad.
e
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
02-07-2010, 09:33 PM
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
02-07-2010, 09:41 PM
thank you mark.
all info is helpful cause sometimes i dont ask the correct question the first time.
hope all is well.
erik
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.