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

Adding date to filename?
 

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" wrote in message
...
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

Adding date to filename?
 

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

Adding date to filename?
 

"Rick Rothstein (MVP - VB)" 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

Adding date to filename?
 

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

Adding date to filename?
 

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

teepee wrote:

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