Extract data

[Excel2006]

Hi

Please could some tell me using vba code how to extract

1758

form the following info.

c:\pot\1750 GASplane\qq1758.xls

I have tried using find and mid commands but always got an error.

I need to do this for several dirs and filenames.

Thx.

 

[DonkeyOte]

can you provide some more info in terms of the other searches you need to do -- ie will the string you want always be 4 characters in length and always precede the .xls ?

 

[Andrew Poulsom]

Is the number always in the same position?

Sub Test()
    Const Str As String = "c:\pot\1750 GASplane\qq1758.xls"
    Dim TempArr As Variant
    TempArr = Split(Str, "\")
    MsgBox Mid(TempArr(UBound(TempArr)), 3, 4)
End Sub

 

[jindon]

Also

Sub test()
Dim myFilePath As String
myFilePath = "c:\pot\1750 GASplane\qq1758.xls"
With CreateObject("VBScript.RegExp")
    .Pattern = "\d+"
    If .test(myFilePath) Then MsgBox .execute(myFilePath)(0)
End With
End sub

 

[Andrew Poulsom]

Jindon, that returns the first number, 1750.

 

[jindon]

OOps
Thanks Andrew

Sub test()
Dim myFilePath As String
myFilePath = "c:\pot\1750 GASplane\qq1758.xls"
With CreateObject("VBScript.RegExp")
    .Pattern = ".*(\d+)\.xls"
    If .test(myFilePath) Then MsgBox .replace(myFilePath, "$1")
End With
End sub

 

[Andrew Poulsom]

Jindon, that returns 8.

 

[jindon]

Jindon, that returns 8.

Is that right ?....
I don't know why...
How about

Sub test()
Dim myFilePath As String, m As Object
myFilePath = "c:\pot\1750 GASplane\qq1758.xls"
With CreateObject("VBScript.RegExp")
    .Pattern = "d+"
    .Global = True
    Set m = .execute(myFilePath)
    If m.Count > 0 Then MsgBox m.item(m.count-1)
End With
End sub

 

[Andrew Poulsom]

Nothing at all.

 

[jindon]

Andrew,
Thanks very much for testing the code.
I need to go now, so I will post tomorrow.