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

Try something like the following (untested)

fName = activeworkbook.name
For I = 1 to len(fName)
if mid(fname, i, 1) = "_" then
tempS = mid(fName, I+1
exit for
end if
next
for I = 1 to len(tempS)
if mid(temps, i,1) = "_" then
dateS = left(temps, i-1)
exit for
end if
next
msgbox dateS

Bob Flanagan
Macro Systems
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, cell 302-584-1771
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel


"Pam" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

On Mar 27, 9:49 am, "Bob Flanagan" <nore...@noreply.net> wrote:
> Try something like the following (untested)
>
> fName = activeworkbook.name
> For I = 1 to len(fName)
> if mid(fname, i, 1) = "_" then
> tempS = mid(fName, I+1
> exit for
> end if
> next
> for I = 1 to len(tempS)
> if mid(temps, i,1) = "_" then
> dateS = left(temps, i-1)
> exit for
> end if
> next
> msgbox dateS
>
> Bob Flanagan
> Macro Systems
> 144 Dewberry Drive
> Hockessin, Delaware, U.S. 19707
>
> Phone: 302-234-9857, cell 302-584-1771http://www.add-ins.com
> Productivity add-ins and downloadable books on VB macros for Excel
>
> "Pam" <pamela...@aol.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > 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 -

If ALL of your examples have the underscore "_" in them, you can use
the "Text to Columns" option in Excel (located in the Data Menu).
VBA's way of doing this same operation is the SPLIT function. So, you
could do something like this as well (nest the code in a For...Next
loop if you have multiple entries):

Sub dateOnly()
Dim txtVal
Dim dateVal

txtVal = Range("A1").Value
dateVal = Split(txtVal, "_")(1)

End Sub

Matt

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
>
>

Pam, here's a solution using VBA:
Dim var1() As String
Dim Dt As Long
Var1 = Split(ActiveWorkbook.Name,"_")
Dt = Var1(1)

--
Charles Chickering

"A good example is twice the value of good advice."


"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
>
>

On Mar 27, 5:56 pm, Jay <J...@discussions.microsoft.com> 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

Hi Pam -

Test this code on your actual workbook names and see what happens.

For i = 1 To InStr(workbook_name, ".xls") - 1
digit = Mid(workbook_name, 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
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")

--
Jay


"Pam" wrote:

> On Mar 27, 5:56 pm, Jay <J...@discussions.microsoft.com> 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
>
>

On Mar 29, 12:56 pm, Jay <J...@discussions.microsoft.com> wrote:
> Hi Pam -
>
> Test this code on your actual workbook names and see what happens.
>
> For i = 1 To InStr(workbook_name, ".xls") - 1
> digit = Mid(workbook_name, 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
> 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")
>
> --
> Jay
>
>
>
> "Pam" wrote:
> > On Mar 27, 5:56 pm, Jay <J...@discussions.microsoft.com> 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- Hide quoted text -
>
> - Show quoted text -

Hi Jay:

I wanted to get back to you sooner to let you know your help is
appreciate.
I ended up using the first snippet of code you gave me once I tweak
it. I
think the problem was the 3G_ then name then date so what id did
was replace part of the workbook_name and it worked fine for what I
was trying
to do.

getdate = Replace(workbook_name, "3G_", "")



For I = 1 To InStr(getdate, ".xls") - 1
digit = Mid(getdate, 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
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")


Thank you very much!
Pam

On 27 Mar 2007 09:25:38 -0700, "Pam" <(E-Mail Removed)> 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

Here's another way to extract the date string. It depends on the string being
six digits in length and followed by an underscore.

The string is stored at colMatches(0) as a string, and can be used however.

==============================
Option Explicit

Sub GetDate()
Const str As String = "MY Sheet_070327_US.xls"
Const sPattern As String = "\d{6}(?=_)"

Dim objRegExp As Object
Dim colMatches As Object

Set objRegExp = CreateObject("VBScript.RegExp")
With objRegExp
.Pattern = sPattern
.IgnoreCase = True
.Global = True

If .Test(str) = True Then
Set colMatches = .Execute(str)
Debug.Print colMatches(0)
End If
End With

End Sub
==============================
--ron