Adding date to filename?

Hello

Please could I beg a little advice from the experts here

I'm using the following code to export data without quotation marks to text
files.

Sub Export()
Dim r As Range, c As Range
Dim sTemp As String
Open "MyOutput.txt" For Output As #1
For Each r In Selection.Rows
sTemp = ""
For Each c In r.Cells
sTemp = sTemp & c.Text & Chr(9)
Next c

'Get rid of trailing tabs
While Right(sTemp, 1) = Chr(9)
sTemp = Left(sTemp, Len(sTemp) - 1)
Wend
Print #1, sTemp
Next r
Close #1
End Sub

It works fine so long as the required cells are selected, but does anyone
know how to amend the VBA to add the time and date to the filename? I'd be
soooo grateful.

I tried

Open "MyOutput" & Format(Date, "yyyymmdd") & ".txt" For Output As #1

but that just crashed it. Any ideas?
Many thanks
tp

Rick
Tue Apr 15 11:55:11 CDT 2008

> Open "MyOutput" & Format(Date, "yyyymmdd") & ".txt" For Output As #1

I don't see anything wrong with that line (well, except that you hard-coded
the #1 instead of using the FreeFile function, but that wouldn't stop the
line from working unless channel #1 were still open from a previous call to
the Open statement), although I would always include the path with the
filename. You say it crashed... describe the crash in more detail
(especially if there were any error messages generated).

Rick


"teepee" <nomail@nomail.com> wrote in message
news:4804da51@newsgate.x-privat.org...
> Hello
>
> Please could I beg a little advice from the experts here
>
> I'm using the following code to export data without quotation marks to
> text files.
>
> Sub Export()
> Dim r As Range, c As Range
> Dim sTemp As String
> Open "MyOutput.txt" For Output As #1
> For Each r In Selection.Rows
> sTemp = ""
> For Each c In r.Cells
> sTemp = sTemp & c.Text & Chr(9)
> Next c
>
> 'Get rid of trailing tabs
> While Right(sTemp, 1) = Chr(9)
> sTemp = Left(sTemp, Len(sTemp) - 1)
> Wend
> Print #1, sTemp
> Next r
> Close #1
> End Sub
>
> It works fine so long as the required cells are selected, but does anyone
> know how to amend the VBA to add the time and date to the filename? I'd be
> soooo grateful.
>
> I tried
>
> Open "MyOutput" & Format(Date, "yyyymmdd") & ".txt" For Output As #1
>
> but that just crashed it. Any ideas?
> Many thanks
> tp
>

Joel
Tue Apr 15 11:56:01 CDT 2008

The code works on my PC. give the following filename

MyOutput20080415.txt


"teepee" wrote:

> Hello
>
> Please could I beg a little advice from the experts here
>
> I'm using the following code to export data without quotation marks to text
> files.
>
> Sub Export()
> Dim r As Range, c As Range
> Dim sTemp As String
> Open "MyOutput.txt" For Output As #1
> For Each r In Selection.Rows
> sTemp = ""
> For Each c In r.Cells
> sTemp = sTemp & c.Text & Chr(9)
> Next c
>
> 'Get rid of trailing tabs
> While Right(sTemp, 1) = Chr(9)
> sTemp = Left(sTemp, Len(sTemp) - 1)
> Wend
> Print #1, sTemp
> Next r
> Close #1
> End Sub
>
> It works fine so long as the required cells are selected, but does anyone
> know how to amend the VBA to add the time and date to the filename? I'd be
> soooo grateful.
>
> I tried
>
> Open "MyOutput" & Format(Date, "yyyymmdd") & ".txt" For Output As #1
>
> but that just crashed it. Any ideas?
> Many thanks
> tp
>
>
>

teepee
Tue Apr 15 14:57:02 CDT 2008


"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote

> I don't see anything wrong with that line (well, except that you
> hard-coded the #1 instead of using the FreeFile function, but that
> wouldn't stop the line from working unless channel #1 were still open from
> a previous call to the Open statement), although I would always include
> the path with the filename. You say it crashed... describe the crash in
> more detail (especially if there were any error messages generated).

Wow this is weird. Suddenly it's working for me too? Must have been operator
error. Sorry

However when I try

Open "MyOutput" & Format(Date, "yyyymmdd") & Format(Time, "hhmmss") &
".txt" For Output As #1

I get syntax error

teepee
Tue Apr 15 15:12:17 CDT 2008


"teepee" <nomail@nomail.com> wrote

> However when I try
>
> Open "MyOutput" & Format(Date, "yyyymmdd") & Format(Time, "hhmmss") &
> ".txt" For Output As #1
>
> I get syntax error


Ah got it.

Open "MyOutput" & Format(Date, "dd-mm-yy") + "_" + Format(Time, "hh-mm-ss")
& ".txt" For Output As #1

Thanks for all your help

tp

Dave
Tue Apr 15 16:05:35 CDT 2008

Or
Open "MyOutput" & Format(now, "dd-mm-yy_hh-mm-ss") & ".txt" For Output As #1

teepee wrote:
>
> "teepee" <nomail@nomail.com> wrote
>
> > However when I try
> >
> > Open "MyOutput" & Format(Date, "yyyymmdd") & Format(Time, "hhmmss") &
> > ".txt" For Output As #1
> >
> > I get syntax error
>
> Ah got it.
>
> Open "MyOutput" & Format(Date, "dd-mm-yy") + "_" + Format(Time, "hh-mm-ss")
> & ".txt" For Output As #1
>
> Thanks for all your help
>
> tp
>

--

Dave Peterson