Date Formating and building character strings
 

I have data files with the names based on dates ordered such that they
naturally appear in order:
Acct 2007-07-23.txt, etc.
In my spreadsheet, i search for complete data and when I find that I am
missing data for a specific date, I would like to enter that date, and have
a procedure work out the filename and load the specfic file.
The yyyy-mm-dd format used to ensure that the files fall in the order of
generation is a little unusual and users don't always catch this and enter
the date incorrectly.
I would simply like to ask for the date needed, and generate the file name
behind the user interface.

I know this doesn't work:
Date needed : 7/23/2007
DN=7/23/2007
Filename = "Acct" & year(DN) & "-" & Month(DN) & "_" & Day(DN) & ".txt"

Any suggestions would be appreciated.
Craig

Date Formating and building character strings
 

On Fri, 17 Aug 2007 14:39:06 -0500, "C Brandt" wrote:

I have data files with the names based on dates ordered such that they
naturally appear in order:
Acct 2007-07-23.txt, etc.
In my spreadsheet, i search for complete data and when I find that I am
missing data for a specific date, I would like to enter that date, and have
a procedure work out the filename and load the specfic file.
The yyyy-mm-dd format used to ensure that the files fall in the order of
generation is a little unusual and users don't always catch this and enter
the date incorrectly.
I would simply like to ask for the date needed, and generate the file name
behind the user interface.

I know this doesn't work:
Date needed : 7/23/2007
DN=7/23/2007
Filename = "Acct" & year(DN) & "-" & Month(DN) & "_" & Day(DN) & ".txt"

Any suggestions would be appreciated.
Craig


="Acct "&TEXT(DN,"yyyy-dd-mm") & ".txt"
--ron

Date Formating and building character strings
 

Ron:

Thanks for the speedy reply. Unfortunately it chokes with TEXT highlighted
and gives me a popup with "Compile Error, SUB or Function not defined.
(Code Follows)

Any clues?

Thanks,
Craig


Sub test()
' Cells(3,2) has the date (7/23/07)
DN = Cells(3, 2)
Filename = "Acct " & Text(DN, "yyyy-dd-mm") & ".txt"
End Sub


"Ron Rosenfeld" wrote in message
...
On Fri, 17 Aug 2007 14:39:06 -0500, "C Brandt"
wrote:

I have data files with the names based on dates ordered such that they
naturally appear in order:
Acct 2007-07-23.txt, etc.
In my spreadsheet, i search for complete data and when I find that I am
missing data for a specific date, I would like to enter that date, and
have
a procedure work out the filename and load the specfic file.
The yyyy-mm-dd format used to ensure that the files fall in the order of
generation is a little unusual and users don't always catch this and
enter
the date incorrectly.
I would simply like to ask for the date needed, and generate the file
name
behind the user interface.

I know this doesn't work:
Date needed : 7/23/2007
DN=7/23/2007
Filename = "Acct" & year(DN) & "-" & Month(DN) & "_" & Day(DN) & ".txt"

Any suggestions would be appreciated.
Craig


="Acct "&TEXT(DN,"yyyy-dd-mm") & ".txt"
--ron

Date Formating and building character strings
 

Ron gave you a formula that will work in a cell in a worksheet (=text()).

In code, you'd use:
Filename = "Acct " & Format(DN, "yyyy-dd-mm") & ".txt"

C Brandt wrote:

Ron:

Thanks for the speedy reply. Unfortunately it chokes with TEXT highlighted
and gives me a popup with "Compile Error, SUB or Function not defined.
(Code Follows)

Any clues?

Thanks,
Craig

Sub test()
' Cells(3,2) has the date (7/23/07)
DN = Cells(3, 2)
Filename = "Acct " & Text(DN, "yyyy-dd-mm") & ".txt"
End Sub

"Ron Rosenfeld" wrote in message
...
On Fri, 17 Aug 2007 14:39:06 -0500, "C Brandt"
wrote:

I have data files with the names based on dates ordered such that they
naturally appear in order:
Acct 2007-07-23.txt, etc.
In my spreadsheet, i search for complete data and when I find that I am
missing data for a specific date, I would like to enter that date, and
have
a procedure work out the filename and load the specfic file.
The yyyy-mm-dd format used to ensure that the files fall in the order of
generation is a little unusual and users don't always catch this and
enter
the date incorrectly.
I would simply like to ask for the date needed, and generate the file
name
behind the user interface.

I know this doesn't work:
Date needed : 7/23/2007
DN=7/23/2007
Filename = "Acct" & year(DN) & "-" & Month(DN) & "_" & Day(DN) & ".txt"

Any suggestions would be appreciated.
Craig


="Acct "&TEXT(DN,"yyyy-dd-mm") & ".txt"
--ron

--

Dave Peterson

Date Formating and building character strings
 

On Fri, 17 Aug 2007 16:15:33 -0500, "C Brandt" wrote:

Ron:

Thanks for the speedy reply. Unfortunately it chokes with TEXT highlighted
and gives me a popup with "Compile Error, SUB or Function not defined.
(Code Follows)

Any clues?

Thanks,
Craig

The formula I gave you is for a worksheet cell.

In VBA, in place of the TEXT function, you can use the FORMAT function.


Sub foo()
Const DN As Date = #7/23/2006#
Debug.Print "Acct " & Format(DN, "yyyy-mm-dd") & ".txt"
End Sub

-- Acct 2006-07-23.txt
--ron

Date Formating and building character strings
 

Thanks

Craig


"Ron Rosenfeld" wrote in message
...
On Fri, 17 Aug 2007 16:15:33 -0500, "C Brandt"
wrote:

Ron:

Thanks for the speedy reply. Unfortunately it chokes with TEXT
highlighted
and gives me a popup with "Compile Error, SUB or Function not defined.
(Code Follows)

Any clues?

Thanks,
Craig

The formula I gave you is for a worksheet cell.

In VBA, in place of the TEXT function, you can use the FORMAT function.


Sub foo()
Const DN As Date = #7/23/2006#
Debug.Print "Acct " & Format(DN, "yyyy-mm-dd") & ".txt"
End Sub

-- Acct 2006-07-23.txt
--ron