Save text file with value from Excel Cell stamped with date and time

[Rushna]

Hello All,
The macro below provided by Mr. Rick Rothstein works perfect. It
export the worksheet to a text file. I wish to change it further to
suit to my needs.
I use excel 2003 (Windows XP)



Changes required:
The file should be saved in the given Folder Path using the Cell
Value
(E5) with date and time stamped e.g if cell value (E5) in the excel
file is 1234 then the file should be saved as "1234 (date)
(time)".txt . If the value in E5 is 3456 then it should be saved as
"3456 (date) (time).txt"

At present it saves as filename.txt and it overwirtes the previous
version too. I need to have all the old files with date and time
stamped for future references using the Cell Value in E5.


Can someone help me please?


Thanks in advance


Rushna

 

[Rick Rothstein \(MVP - VB\)]

Just change the assignment for the FileNameAndPath variable from this...


to this...

FileNameAndPath = "c:\Dir1\Dir2\etc\" & .Range("E5").Value & "(" & _
Date$ & ")(" & Replace(Time$, ":", "-") & ").txt"

Of course, you will have to fix up the directory path to point to a valid
folder on your system. A couple of things to note. First, do *not* remove
the $ sign from the Date$ and Time$ function calls... they make the function
return the date and time in a specific format that the code depends on.
Second, you can't have slashes (/) or colons ) in a filename, so I used
dashes in both places (Date$ automatically outputs the date with dashes; I
purposefully replaced the colons with dashes using the Replace function
call... you can change the dash to any other valid filename character if you
wish). Also, you post did not make it clear whether you wanted the date and
time enclosed in parentheses or not; but, since your examples showed them, I
included them.

Rick

 

[Rushna]

Just change the assignment for the FileNameAndPath variable from this...

to this...

    FileNameAndPath = "c:\Dir1\Dir2\etc\" & .Range("E5").Value & "(" & _
                      Date$ & ")(" & Replace(Time$,":", "-") & ").txt"

Of course, you will have to fix up the directory path to point to a valid
folder on your system. A couple of things to note. First, do *not* remove
the $ sign from the Date$ and Time$ function calls... they make the function
return the date and time in a specific format that the code depends on.
Second, you can't have slashes (/) or colons ) in a filename, so I used
dashes in both places (Date$ automatically outputs the date with dashes; I
purposefully replaced the colons with dashes using the Replace function
call... you can change the dash to any other valid filename character if you
wish). Also, you post did not make it clear whether you wanted the date and
time enclosed in parentheses or not; but, since your examples showed them, I
included them.

Rick











- Show quoted text -

Hi Rick,
I replaced the FileNameAndPath and when I run the macro it
highlights .Range("E5") and gives the following error:
Invalid or unqualified reference

I wish to have the name to be in the following format:
1234 27072008 11:36 pm.txt

Thanks for your time

Rushna

 

[Rick Rothstein \(MVP - VB\)]

Just change the assignment for the FileNameAndPath variable from this...

I replaced the FileNameAndPath and when I run the macro it
highlights .Range("E5") and gives the following error:
Invalid or unqualified reference

I forgot to mention that you needed to move the line down one so it was
inside the With/End With block. See the full code with all the modifications
after my signature (you can copy/paste it).

I wish to have the name to be in the following format:
1234 27072008 11:36 pm.txt

You can't have it in that format... the colon ) is an illegal character in
a filename. I'll use a dash, but as I said in my previous post, you can
change the dash to some other **legal** filename character. Here is a link
describing how to make a legal filename...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/fs/naming_a_file.asp

Rick

Sub WriteDataOut()
Dim X As Long
Dim FF As Long
Dim LastRow As Long
Dim Dte As String
Dim Record As String
Dim TotalFile As String
Dim FileNameAndPath As String
With Worksheets("Sheet1")
FileNameAndPath = "c:\Dir1\Dir2\etc\" & .Range("E5").Value & _
Format(Now, " ddmmyyyy ") & _
Format(Now, "hh-mm am/pm") & ".txt"
LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
For X = 10 To LastRow
Record = Space$(66)
Mid$(Record, 1) = .Cells(X, "C").Value
Mid$(Record, 10) = Format$(.Cells(X, "F").Value, "0000000000")
Dte = .Cells(X, "H").Value
Mid$(Record, 20) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2)
Dte = .Cells(X, "H").Value
Mid$(Record, 28) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2)
Mid$(Record, 36) = Format$(100 * Abs(.Cells(X, "K").Value) _
, "000000000000000")
Mid$(Record, 51) = .Cells(X, "L").Value
Mid$(Record, 56) = .Cells(X, "Q").Value
TotalFile = TotalFile & vbCrLf & Record
Next
FF = FreeFile
Open FileNameAndPath For Output As #FF
Print #FF, TotalFile
Close #FF
End With
End Sub

 

[Rushna]

I forgot to mention that you needed to move the line down one so it was
inside the With/End With block. See the full code with all the modifications
after my signature (you can copy/paste it).


You can't have it in that format... the colon ) is an illegal characterin
a filename. I'll use a dash, but as I said in my previous post, you can
change the dash to some other **legal** filename character. Here is a link
describing how to make a legal filename...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/file....

Rick

Sub WriteDataOut()
  Dim X As Long
  Dim FF As Long
  Dim LastRow As Long
  Dim Dte As String
  Dim Record As String
  Dim TotalFile As String
  Dim FileNameAndPath As String
  With Worksheets("Sheet1")
    FileNameAndPath = "c:\Dir1\Dir2\etc\" & .Range("E5").Value & _
                            Format(Now, " ddmmyyyy ") & _
                            Format(Now, "hh-mm am/pm") & ".txt"
    LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
    For X = 10 To LastRow
      Record = Space$(66)
      Mid$(Record, 1) = .Cells(X, "C").Value
      Mid$(Record, 10) = Format$(.Cells(X, "F").Value, "0000000000")
      Dte = .Cells(X, "H").Value
      Mid$(Record, 20) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2)
      Dte = .Cells(X, "H").Value
      Mid$(Record, 28) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2)
      Mid$(Record, 36) = Format$(100 * Abs(.Cells(X, "K").Value) _
                                              , "000000000000000")
      Mid$(Record, 51) = .Cells(X, "L").Value
      Mid$(Record, 56) = .Cells(X, "Q").Value
      TotalFile = TotalFile & vbCrLf & Record
    Next
    FF = FreeFile
    Open FileNameAndPath For Output As #FF
    Print #FF, TotalFile
    Close #FF
  End With
End Sub

Thanks Rick. Works great.
You are really helpful

Rushna