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

On Fri, 17 Aug 2007 14:39:06 -0500, "C Brandt" <(E-Mail Removed)> 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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Fri, 17 Aug 2007 14:39:06 -0500, "C Brandt" <(E-Mail Removed)>
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

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > On Fri, 17 Aug 2007 14:39:06 -0500, "C Brandt" <(E-Mail Removed)>
> 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

On Fri, 17 Aug 2007 16:15:33 -0500, "C Brandt" <(E-Mail Removed)> 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

Thanks

Craig


"Ron Rosenfeld" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
> On Fri, 17 Aug 2007 16:15:33 -0500, "C Brandt" <(E-Mail Removed)>
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