Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save text file with value from Excel Cell stamped with date and time
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) 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 FileNameAndPath = "c:\Dir1\Dir2\etc\FileName.txt" With Worksheets("Sheet1") 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 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save text file with value from Excel Cell stamped with date and time
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" wrote in message ... 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) 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 FileNameAndPath = "c:\Dir1\Dir2\etc\FileName.txt" With Worksheets("Sheet1") 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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save text file with value from Excel Cell stamped with date andtime
On Jul 26, 10:54*pm, "Rick Rothstein \(MVP - VB\)"
wrote: 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" wrote in message ... 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) 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 * FileNameAndPath = "c:\Dir1\Dir2\etc\FileName.txt" * With Worksheets("Sheet1") * * 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 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- Hide quoted text - - 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save text file with value from Excel Cell stamped with date and time
Just change the assignment for the FileNameAndPath variable from this...
to this... FileNameAndPath = "c:\Dir1\Dir2\etc\" & .Range("E5").Value & "(" & _ Date$ & ")(" & Replace(Time$, ":", "-") & ").txt" 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/de...ing_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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save text file with value from Excel Cell stamped with date andtime
On Jul 27, 6:16*am, "Rick Rothstein \(MVP - VB\)"
wrote: Just change the assignment for the FileNameAndPath variable from this.... to this... FileNameAndPath = "c:\Dir1\Dir2\etc\" & .Range("E5").Value & "(" & _ Date$ & ")(" & Replace(Time$, ":", "-") & ").txt" 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/de...ary/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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - CSV file, trying to save a date as text, reverts back to d | Excel Worksheet Functions | |||
customer date time save file | Excel Discussion (Misc queries) | |||
Save File with Date and Time Stamp | Excel Programming | |||
Macro to save Excel file with date and time in the file name? | Excel Programming | |||
Date stamped comment box | Excel Programming |