help with a VBA file name function

[goldwhispers]

I need to get the current file name, and if a certain value is in the filename for eg say i am looking for value blue in the file name and the file name is Jeans_Blue_Levi.xls

i want to do a case statement that codes a certain cost centre in to the worksheet, any ideas?

 

[PHV]

Have a look at the InStr function or the Like operator.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[goldwhispers]

i am trying something like this

Sub Pseudo()
Dim Filename As String
Dim Pseudo As Variant
Filename = ThisWorkbook.Name

Select Case Filename
Case like "ASIA": Pseudo = "ASIAR"

End Sub

but it doesn't like the like, is it possible to use it in this context?

 

[xlbo]

dim tempStr as string

Select Case Filename
Case like "ASIA": Pseudo
tempStr = "ASIAR"
Case Else
tempStr = "Something Else"
End Select


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

Select Case Filename
Case Like "*ASIA*": Pseudo = "ASIAR"
end Select

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[goldwhispers]

i like ur sig geoff

thanks to both of you

but i still get a compile syntax error wierd?
Sub Pseudo()
Dim Filename As String
Dim tempStr As String

Filename = ThisWorkbook.Name

Select Case Filename
Case Like "*ASIA*": Pseudo = "ASIAR"
End Select




End Sub

 

[xlbo]

hmmm - doesn't like "like" in the select case statement - how about:

Select Case InStr(1, Filename, "ASIA")
Case 0
'Text not found
Case Else
'Text found
End Select

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

[!]Function[/!] Pseudo()
Dim Filename As String
Dim tempStr As String
Filename = ThisWorkbook.Name
Select Case Filename
Case Like "*ASIA*": Pseudo = "ASIAR"
End Select
End [!]Function[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

nope - that still doesn't compile PHV (XL 2003 / XP)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Because you can't use that in a Select Case statement.

MSFT Help files even say, "..Note that Is and Like can't be used as comparison operators in a Select Case statement."

Use the InStr function. An example ..

Function Pseudo()
    If InStr(1, ThisWorkbook.Name, "ASIA") <> 0 Then
        'was found
    Else
        'was NOT found
    End If
End Function

-----------
Regards,
Zack Barresse

 

[PHV]

doesn't like "like" in the select case statement
Right !
Function Pseudo()
If ThisWorkbook.Name Like "*ASIA*" Then
Pseudo = "ASIAR"
End If
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

<cough>
Filefytr: my post on 4 May 06 10:48
</cough>

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

LOL! I know, I saw it. Too many ways to skin this cat. I just usually run my If/Then right off my test instead of dropping to a nested Select Case. But there is a plethora of examples to choose from here.

-----------
Regards,
Zack Barresse

 

[IknowMe]

Like returns True False So you could Case it like so.

Sub Pseudo()
  Dim Filename As String
  Dim Pseudo As Variant
  Filename = ThisWorkbook.Name

  Select Case True
    Case FileName Like "ASIA"
        Pseudo = "ASIA"
    Case FileName Like "Book1"
        Pseudo = "Book1"
    Case Else
        Pseudo = "Something Else"
  End Select
End Sub

Not much cleaner than an If Then but if you place your most common cases near the top it runs quicker.

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[IknowMe]

All right that's not gonna compile either but only because the syntax is bad not the logic. This will work function it out or whatever you wish. Sorry about the bad snip above

Filename = ThisWorkbook.Name

Select Case True
  Case Filename Like "ASIA"
    Pseudo = "ASIA"
  Case Filename Like "Book1"
    Pseudo = "Book1"
  Case Else
    Pseudo = "Something Else"
End Select
MsgBox Pseudo

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.