On Mar 27, 5:56 pm, Jay wrote:
Hi Pam -

Here is another version that selects the date from the filename as the first
6 consecutive numeric characters. It can therefore tolerate filenames that
don't conform to your example as well as extra, non-date numeric characters.
It also provides a conversion from the date substring to an excel date value.

Sub Pam()

fname = "MY Sheet_070331_US.xls"

For i = 1 To InStr(fname, ".xls") - 1
digit = Mid(fname, i, 1)
If IsNumeric(digit) Then
If IsEmpty(sDate) Then
sDate = digit
digitCount = digitCount + 1
lastDigitPosition = i
Else
If i - lastDigitPosition = 1 Then
sDate = sDate & digit
digitCount = digitCount + 1
If digitCount = 6 Then Exit For
End If
lastDigitPosition = i
Else
sDate = Empty
digitCount = 0
End If
End If
Else
lastDigitPosition = i
sDate = emtpy
digitCount = 0
End If
Next i

MsgBox "The date string is: " & sDate & Chr(13) & Chr(13) & _
"The converted date is: " & Format(DateSerial(Val(Left(sDate, 2)), _
Val(Mid(sDate, 3, 2)), Val(Right(sDate, 2))), "yy/mm/dd")

End Sub
--
Jay



"Pam" wrote:
Hi

I was wondering if anyone new exactly how to use the chop function
to chop up a string. Basically what I am trying to do is chop up thre
namer of
a file which contains the filename with date. I want to get the date
for that file
name; Here is an example

MY Sheet_070327_US.xls

I want to chop it up so I just end up with the date.

Your help is appreicated. Thanking you in advance

Pam- Hide quoted text -

- Show quoted text -

Hi Jay:

Thank You for your response.

I Tried your suggestion I got it to work using the example name forthe
book and it works fine but
I should have given the real name because I am having a problem.

The name of the book is 3G_Test_Analysis_CPs_070320_USA.xls.
The problem I am having is when I put is the pring statements for
digit it
shows the digits but it does not start counting until it gets to 7,
there fore I get
a date 70323 instead of 070323. I know it is because of the name
starting with
a number. Below is the code I had to tweak to get to work.
The workbook name changes everyday by date difference only.


For i = 1 To InStr(workbook_name, ".xls") - 1
digit = Mid(workbook_name, i, 1)
If IsNumeric(digit) Then
MsgBox ("What is digit" & digit)

If IsEmpty(sDate) Then
sDate = digit
digitCount = digitCount + 1
lastDigitPosition = i
Else
If i - lastDigitPosition = 1 Then
sDate = sDate & digit
digitCount = digitCount + 1


MsgBox ("What is digit count" & digitCount)
MsgBox ("What is date" & sDate)
If digitCount = 6 Then 'if I change
the number to 5 it will hit this but it should be 6
MsgBox ("What is last position" & i)
Exit For
End If
lastDigitPosition = i
Else
sDate = Empty
digitCount = 0
End If
End If

End If

Next i



MsgBox "The date string is: " & sDate & Chr(13) & Chr(13) & _
"The converted date is: " & Format(DateSerial(Val(Left(sDate, 2)), _
Val(Mid(sDate, 3, 2)), Val(Right(sDate, 2))), "mm/dd/yyyy")


Your help is greatly appreciated!
Pam 03/28/07