Extracting portion of filename
 

I have a macro that creates a series of workbooks named in the format
"Daily yyyy mm dd" which works fine.
My daily worksheets require a column headed by the date and another
column headed by the preceding day's date. That column links to the
prior day's worksheet to bring forward end of the day values.
I'm a newbie (retired CPA) at VBA and would appreciate suggestions
about how to extract the right 10 characters of the file name and
convert them to a date in cell c1 on sheet 1.
Many thanks
Don

Extracting portion of filename
 

this may or may not work depending on your filename. if it's exactly like you
show, this should work.

Sub test()
Dim fn As String
Dim dt As Variant
fn = "test 2008 07 12"
dt = Split(fn, " ")
Range("A2").Value = DateSerial(dt(1), dt(2), dt(3))
End Sub
--


Gary


"HappySenior" wrote in message
...
I have a macro that creates a series of workbooks named in the format
"Daily yyyy mm dd" which works fine.
My daily worksheets require a column headed by the date and another
column headed by the preceding day's date. That column links to the
prior day's worksheet to bring forward end of the day values.
I'm a newbie (retired CPA) at VBA and would appreciate suggestions
about how to extract the right 10 characters of the file name and
convert them to a date in cell c1 on sheet 1.
Many thanks
Don

Extracting portion of filename
 

Range("C1").Value = DateValue(Right$(Replace(ActiveWorkbook.Name,
".xls", ""), 10))


--
__________________________________
HTH

Bob

"HappySenior" wrote in message
...
I have a macro that creates a series of workbooks named in the format
"Daily yyyy mm dd" which works fine.
My daily worksheets require a column headed by the date and another
column headed by the preceding day's date. That column links to the
prior day's worksheet to bring forward end of the day values.
I'm a newbie (retired CPA) at VBA and would appreciate suggestions
about how to extract the right 10 characters of the file name and
convert them to a date in cell c1 on sheet 1.
Many thanks
Don

Extracting portion of filename
 

Assuming you have the filename in a String variable and that the filename
has an extension (assumed to be CSV for the following example, but the code
will work for any 3-character extension)...

Filename = "Daily 2008 07 12.csv"
TheDate = CDate(Replace(Mid(Filename, Len(Filename) - 13, 10), " ", "-"))

Rick


"HappySenior" wrote in message
...
I have a macro that creates a series of workbooks named in the format
"Daily yyyy mm dd" which works fine.
My daily worksheets require a column headed by the date and another
column headed by the preceding day's date. That column links to the
prior day's worksheet to bring forward end of the day values.
I'm a newbie (retired CPA) at VBA and would appreciate suggestions
about how to extract the right 10 characters of the file name and
convert them to a date in cell c1 on sheet 1.
Many thanks
Don

Extracting portion of filename
 

Thank Rick, but Bob Phillips has a great solution that works with the
open xls file and does not require creating any variables. Actually, I
realized that the macro I used to create the file only needed an
instruction to post the date value in cell c1 and then post the
previous day's date in cell e1 so that values from the prior day can
be compared.

My original problem was how to extract data from the filename and I
knew of no way to set up the filename as part of the macro. Appreciate
your help. Don

On Jul 12, 9:38*am, "Rick Rothstein \(MVP - VB\)"
wrote:
Assuming you have the filename in a String variable and that the filename
has an extension (assumed to be CSV for the following example, but the code
will work for any 3-character extension)...

Filename = "Daily 2008 07 12.csv"
TheDate = CDate(Replace(Mid(Filename, Len(Filename) - 13, 10), " ", "-"))

Rick

"HappySenior" wrote in message

...



I have a macro that creates a series of workbooks named in the format
"Daily yyyy mm dd" which works fine.
My daily worksheets require a column headed by the date and another
column headed by the preceding day's date. That column links to the
prior day's worksheet to bring forward end of the day values.
I'm a newbie (retired CPA) at VBA and would appreciate suggestions
about how to extract the right 10 characters of the file name and
convert them to a date in cell c1 on sheet 1.
Many thanks
Don- Hide quoted text -

- Show quoted text -

Extracting portion of filename
 

Bob, Great solution! - Fits my stated problem perfectly and works
great!
This newbie realized after testing it that all I had to do was modify
my macro that creates the worksheets to go to C1 and enter the date
variable I created and then go to e1 to post the same date -1 so that
I would be able to compute the change in values for two consecutive
days in a 365 day operation.

My next variation will be to create worksheets for businesses that are
not open on certain days like weekends or holidays. The holiday part
will require a holiday schedule for the business, the rest should be
easy. I find it to be fun to tease my mind, now if only I could make
some $ from it...
Don.

On Jul 12, 9:31*am, "Bob Phillips" wrote:
* * Range("C1").Value = DateValue(Right$(Replace(ActiveWorkbook.Name,
".xls", ""), 10))

--
__________________________________
HTH

Bob

"HappySenior" wrote in message

...



I have a macro that creates a series of workbooks named in the format
"Daily yyyy mm dd" which works fine.
My daily worksheets require a column headed by the date and another
column headed by the preceding day's date. That column links to the
prior day's worksheet to bring forward end of the day values.
I'm a newbie (retired CPA) at VBA and would appreciate suggestions
about how to extract the right 10 characters of the file name and
convert them to a date in cell c1 on sheet 1.
Many thanks
Don- Hide quoted text -

- Show quoted text -

Extracting portion of filename
 

On Jul 12, 9:26*am, "Gary Keramidas" <GKeramidasATmsn.com wrote:
this may or may not work depending on your filename. if it's exactly like you
show, this should work.

Sub test()
Dim fn As String
Dim dt As Variant
fn = "test 2008 07 12"
dt = Split(fn, " ")
Range("A2").Value = DateSerial(dt(1), dt(2), dt(3))
End Sub
--

Gary

"HappySenior" wrote in message

...



I have a macro that creates a series of workbooks named in the format
"Daily yyyy mm dd" which works fine.
My daily worksheets require a column headed by the date and another
column headed by the preceding day's date. That column links to the
prior day's worksheet to bring forward end of the day values.
I'm a newbie (retired CPA) at VBA and would appreciate suggestions
about how to extract the right 10 characters of the file name and
convert them to a date in cell c1 on sheet 1.
Many thanks
Don- Hide quoted text -

- Show quoted text -
Gary, your solution is fine except I needed a way to macro enter the
filename in your variable fn. See my note to Bob Phillips. I have
learned from both of you.

Extracting portion of filename
 

Look up the NETWORKDAYS and WORKDAY ATP functions in help.

--
__________________________________
HTH

Bob

"HappySenior" wrote in message
...
Bob, Great solution! - Fits my stated problem perfectly and works
great!
This newbie realized after testing it that all I had to do was modify
my macro that creates the worksheets to go to C1 and enter the date
variable I created and then go to e1 to post the same date -1 so that
I would be able to compute the change in values for two consecutive
days in a 365 day operation.

My next variation will be to create worksheets for businesses that are
not open on certain days like weekends or holidays. The holiday part
will require a holiday schedule for the business, the rest should be
easy. I find it to be fun to tease my mind, now if only I could make
some $ from it...
Don.

On Jul 12, 9:31 am, "Bob Phillips" wrote:
Range("C1").Value = DateValue(Right$(Replace(ActiveWorkbook.Name,
".xls", ""), 10))

--
__________________________________
HTH

Bob

"HappySenior" wrote in message

...



I have a macro that creates a series of workbooks named in the format
"Daily yyyy mm dd" which works fine.
My daily worksheets require a column headed by the date and another
column headed by the preceding day's date. That column links to the
prior day's worksheet to bring forward end of the day values.
I'm a newbie (retired CPA) at VBA and would appreciate suggestions
about how to extract the right 10 characters of the file name and
convert them to a date in cell c1 on sheet 1.
Many thanks
Don- Hide quoted text -

- Show quoted text -