| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save with filename ACJRNyyyymmddhhmm
Good morning
I am currently using the following code to save a worksheet as a custom .CSV filetype: intFile = FreeFile Open "c:\test.txt" For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile As you can see, it saves the file as "test.txt". However, as I will need to run this macro daily, I need to be able to save the file with the following filename: "ACJRNyyyymmddhhmm.txt" eg: today would be (assuming I ran the macro at the time I wrote this query) "ACJRN200909231006.txt" Could someone please suggest a method that would achieve this desired filename each day? Please note, it is critical that I keep the formatting, within the file, that has been achieved by the above code. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save with filename ACJRNyyyymmddhhmm
Hi again, please find the modified macro...
Dim intFile As Integer, strFile As String intFile = FreeFile strFile = "ACJRN" & Format(Now, "YYYYMMDDhhmm") & ".txt" Open strFile For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile If this post helps click Yes --------------- Jacob Skaria "PVANS" wrote: Good morning I am currently using the following code to save a worksheet as a custom .CSV filetype: intFile = FreeFile Open "c:\test.txt" For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile As you can see, it saves the file as "test.txt". However, as I will need to run this macro daily, I need to be able to save the file with the following filename: "ACJRNyyyymmddhhmm.txt" eg: today would be (assuming I ran the macro at the time I wrote this query) "ACJRN200909231006.txt" Could someone please suggest a method that would achieve this desired filename each day? Please note, it is critical that I keep the formatting, within the file, that has been achieved by the above code. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save with filename ACJRNyyyymmddhhmm
With the folder name...
Dim intFile As Integer, strFile As Stringm, strFolder As String intFile = FreeFile strFolder = "c:\" strFile = "ACJRN" & Format(Now, "YYYYMMDDhhmm") & ".txt" Open strFolder & strFile For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Hi again, please find the modified macro... Dim intFile As Integer, strFile As String intFile = FreeFile strFile = "ACJRN" & Format(Now, "YYYYMMDDhhmm") & ".txt" Open strFile For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile If this post helps click Yes --------------- Jacob Skaria "PVANS" wrote: Good morning I am currently using the following code to save a worksheet as a custom .CSV filetype: intFile = FreeFile Open "c:\test.txt" For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile As you can see, it saves the file as "test.txt". However, as I will need to run this macro daily, I need to be able to save the file with the following filename: "ACJRNyyyymmddhhmm.txt" eg: today would be (assuming I ran the macro at the time I wrote this query) "ACJRN200909231006.txt" Could someone please suggest a method that would achieve this desired filename each day? Please note, it is critical that I keep the formatting, within the file, that has been achieved by the above code. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save with filename ACJRNyyyymmddhhmm
Hi Jacob,
Once again, thank you so much for the assistance. It is saving the file perfectly. If its okay, I would like to ask one further query with relation to the code you have so kindly provided. In the original worksheet, the following columns contain the following: O = the date (with no slashes) eg: 22092009 P = ~ Q = <blank R = ~ S = ~ T = ~ When I run your code, and then open the file, the date now has slashes in (eg 22/09/2009), and the <blank has vanished. I need both of these to remain in their original format, ie: the date with no slashes, and the <blank still remaining (eg: ~ ~~~). I have looked at your code to try and determine why these are both changing, but can not identify the reason. Would you please help me with this? Once again, thanks so much for all your help. Your replies are always friendly and prompt and of great value. Regards "Jacob Skaria" wrote: With the folder name... Dim intFile As Integer, strFile As Stringm, strFolder As String intFile = FreeFile strFolder = "c:\" strFile = "ACJRN" & Format(Now, "YYYYMMDDhhmm") & ".txt" Open strFolder & strFile For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Hi again, please find the modified macro... Dim intFile As Integer, strFile As String intFile = FreeFile strFile = "ACJRN" & Format(Now, "YYYYMMDDhhmm") & ".txt" Open strFile For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile If this post helps click Yes --------------- Jacob Skaria "PVANS" wrote: Good morning I am currently using the following code to save a worksheet as a custom .CSV filetype: intFile = FreeFile Open "c:\test.txt" For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile As you can see, it saves the file as "test.txt". However, as I will need to run this macro daily, I need to be able to save the file with the following filename: "ACJRNyyyymmddhhmm.txt" eg: today would be (assuming I ran the macro at the time I wrote this query) "ACJRN200909231006.txt" Could someone please suggest a method that would achieve this desired filename each day? Please note, it is critical that I keep the formatting, within the file, that has been achieved by the above code. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save with filename ACJRNyyyymmddhhmm
When I run your code, and then open the file, the date now has slashes in
(eg 22/09/2009), and the <blank has vanished. I need both of these to remain in their original format, ie: the date with no slashes, and the <blank still remaining (eg: ~ ~~~). --Unless dates are formatted before output; the dates will be reflected in the system format. --Blankcell and space are different entities. We will have to handle that in code Try the below with the discussed modifications. Dim intFile As Integer, strFile As String, strFolder As String Dim strData As String, rngRow As Range, cell As Range intFile = FreeFile strFolder = "c:\" strFile = "ACJRN" & Format(Now, "YYYYMMDDhhmm") & ".txt" Open strFolder & strFile For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells If Trim(cell.Text) = "" Then strData = strData & Space(1) ElseIf IsDate(cell) Then strData = strData & Format(cell.Value, "ddmmyyyy") Else strData = strData & Trim(cell) End If Next Print #intFile, strData Next Close #intFile If this post helps click Yes --------------- Jacob Skaria "PVANS" wrote: Hi Jacob, Once again, thank you so much for the assistance. It is saving the file perfectly. If its okay, I would like to ask one further query with relation to the code you have so kindly provided. In the original worksheet, the following columns contain the following: O = the date (with no slashes) eg: 22092009 P = ~ Q = <blank R = ~ S = ~ T = ~ When I run your code, and then open the file, the date now has slashes in (eg 22/09/2009), and the <blank has vanished. I need both of these to remain in their original format, ie: the date with no slashes, and the <blank still remaining (eg: ~ ~~~). I have looked at your code to try and determine why these are both changing, but can not identify the reason. Would you please help me with this? Once again, thanks so much for all your help. Your replies are always friendly and prompt and of great value. Regards "Jacob Skaria" wrote: With the folder name... Dim intFile As Integer, strFile As Stringm, strFolder As String intFile = FreeFile strFolder = "c:\" strFile = "ACJRN" & Format(Now, "YYYYMMDDhhmm") & ".txt" Open strFolder & strFile For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Hi again, please find the modified macro... Dim intFile As Integer, strFile As String intFile = FreeFile strFile = "ACJRN" & Format(Now, "YYYYMMDDhhmm") & ".txt" Open strFile For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile If this post helps click Yes --------------- Jacob Skaria "PVANS" wrote: Good morning I am currently using the following code to save a worksheet as a custom .CSV filetype: intFile = FreeFile Open "c:\test.txt" For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile As you can see, it saves the file as "test.txt". However, as I will need to run this macro daily, I need to be able to save the file with the following filename: "ACJRNyyyymmddhhmm.txt" eg: today would be (assuming I ran the macro at the time I wrote this query) "ACJRN200909231006.txt" Could someone please suggest a method that would achieve this desired filename each day? Please note, it is critical that I keep the formatting, within the file, that has been achieved by the above code. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save with filename ACJRNyyyymmddhhmm
Hi Jacob,
sorry for the delay in my response, have been quite busy at work today with several different projects. My further apologies in terms of the information I supplied when I said <Blank. It is not <Blank but instead a Space. I know that in your response you said that they are different entities. Nevertheless, I ran your code, and the date has been correctly formatted without slashes. However, due to my incorrect information, I am still lacking the required space between the tilda in column P and the remaining three ~ in columns R,S, and T. The space of course appearing in the original document in column Q. Is there a method to achieve this? Thanks once again "Jacob Skaria" wrote: When I run your code, and then open the file, the date now has slashes in (eg 22/09/2009), and the <blank has vanished. I need both of these to remain in their original format, ie: the date with no slashes, and the <blank still remaining (eg: ~ ~~~). --Unless dates are formatted before output; the dates will be reflected in the system format. --Blankcell and space are different entities. We will have to handle that in code Try the below with the discussed modifications. Dim intFile As Integer, strFile As String, strFolder As String Dim strData As String, rngRow As Range, cell As Range intFile = FreeFile strFolder = "c:\" strFile = "ACJRN" & Format(Now, "YYYYMMDDhhmm") & ".txt" Open strFolder & strFile For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells If Trim(cell.Text) = "" Then strData = strData & Space(1) ElseIf IsDate(cell) Then strData = strData & Format(cell.Value, "ddmmyyyy") Else strData = strData & Trim(cell) End If Next Print #intFile, strData Next Close #intFile If this post helps click Yes --------------- Jacob Skaria "PVANS" wrote: Hi Jacob, Once again, thank you so much for the assistance. It is saving the file perfectly. If its okay, I would like to ask one further query with relation to the code you have so kindly provided. In the original worksheet, the following columns contain the following: O = the date (with no slashes) eg: 22092009 P = ~ Q = <blank R = ~ S = ~ T = ~ When I run your code, and then open the file, the date now has slashes in (eg 22/09/2009), and the <blank has vanished. I need both of these to remain in their original format, ie: the date with no slashes, and the <blank still remaining (eg: ~ ~~~). I have looked at your code to try and determine why these are both changing, but can not identify the reason. Would you please help me with this? Once again, thanks so much for all your help. Your replies are always friendly and prompt and of great value. Regards "Jacob Skaria" wrote: With the folder name... Dim intFile As Integer, strFile As Stringm, strFolder As String intFile = FreeFile strFolder = "c:\" strFile = "ACJRN" & Format(Now, "YYYYMMDDhhmm") & ".txt" Open strFolder & strFile For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Hi again, please find the modified macro... Dim intFile As Integer, strFile As String intFile = FreeFile strFile = "ACJRN" & Format(Now, "YYYYMMDDhhmm") & ".txt" Open strFile For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile If this post helps click Yes --------------- Jacob Skaria "PVANS" wrote: Good morning I am currently using the following code to save a worksheet as a custom .CSV filetype: intFile = FreeFile Open "c:\test.txt" For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile As you can see, it saves the file as "test.txt". However, as I will need to run this macro daily, I need to be able to save the file with the following filename: "ACJRNyyyymmddhhmm.txt" eg: today would be (assuming I ran the macro at the time I wrote this query) "ACJRN200909231006.txt" Could someone please suggest a method that would achieve this desired filename each day? Please note, it is critical that I keep the formatting, within the file, that has been achieved by the above code. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save with filename ACJRNyyyymmddhhmm
OK. Try the below...
Dim intFile As Integer, strFile As String, strFolder As String Dim strData As String, rngRow As Range, cell As Range intFile = FreeFile strFolder = "c:\" strFile = "ACJRN" & Format(Now, "YYYYMMDDhhmm") & ".txt" Open strFolder & strFile For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells If IsDate(cell) Then strData = strData & Format(cell.Value, "ddmmyyyy") Else strData = strData & cell End If Next Print #intFile, strData Next Close #intFile If this post helps click Yes --------------- Jacob Skaria "PVANS" wrote: Hi Jacob, sorry for the delay in my response, have been quite busy at work today with several different projects. My further apologies in terms of the information I supplied when I said <Blank. It is not <Blank but instead a Space. I know that in your response you said that they are different entities. Nevertheless, I ran your code, and the date has been correctly formatted without slashes. However, due to my incorrect information, I am still lacking the required space between the tilda in column P and the remaining three ~ in columns R,S, and T. The space of course appearing in the original document in column Q. Is there a method to achieve this? Thanks once again "Jacob Skaria" wrote: When I run your code, and then open the file, the date now has slashes in (eg 22/09/2009), and the <blank has vanished. I need both of these to remain in their original format, ie: the date with no slashes, and the <blank still remaining (eg: ~ ~~~). --Unless dates are formatted before output; the dates will be reflected in the system format. --Blankcell and space are different entities. We will have to handle that in code Try the below with the discussed modifications. Dim intFile As Integer, strFile As String, strFolder As String Dim strData As String, rngRow As Range, cell As Range intFile = FreeFile strFolder = "c:\" strFile = "ACJRN" & Format(Now, "YYYYMMDDhhmm") & ".txt" Open strFolder & strFile For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells If Trim(cell.Text) = "" Then strData = strData & Space(1) ElseIf IsDate(cell) Then strData = strData & Format(cell.Value, "ddmmyyyy") Else strData = strData & Trim(cell) End If Next Print #intFile, strData Next Close #intFile If this post helps click Yes --------------- Jacob Skaria "PVANS" wrote: Hi Jacob, Once again, thank you so much for the assistance. It is saving the file perfectly. If its okay, I would like to ask one further query with relation to the code you have so kindly provided. In the original worksheet, the following columns contain the following: O = the date (with no slashes) eg: 22092009 P = ~ Q = <blank R = ~ S = ~ T = ~ When I run your code, and then open the file, the date now has slashes in (eg 22/09/2009), and the <blank has vanished. I need both of these to remain in their original format, ie: the date with no slashes, and the <blank still remaining (eg: ~ ~~~). I have looked at your code to try and determine why these are both changing, but can not identify the reason. Would you please help me with this? Once again, thanks so much for all your help. Your replies are always friendly and prompt and of great value. Regards "Jacob Skaria" wrote: With the folder name... Dim intFile As Integer, strFile As Stringm, strFolder As String intFile = FreeFile strFolder = "c:\" strFile = "ACJRN" & Format(Now, "YYYYMMDDhhmm") & ".txt" Open strFolder & strFile For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Hi again, please find the modified macro... Dim intFile As Integer, strFile As String intFile = FreeFile strFile = "ACJRN" & Format(Now, "YYYYMMDDhhmm") & ".txt" Open strFile For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile If this post helps click Yes --------------- Jacob Skaria "PVANS" wrote: Good morning I am currently using the following code to save a worksheet as a custom .CSV filetype: intFile = FreeFile Open "c:\test.txt" For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile As you can see, it saves the file as "test.txt". However, as I will need to run this macro daily, I need to be able to save the file with the following filename: "ACJRNyyyymmddhhmm.txt" eg: today would be (assuming I ran the macro at the time I wrote this query) "ACJRN200909231006.txt" Could someone please suggest a method that would achieve this desired filename each day? Please note, it is critical that I keep the formatting, within the file, that has been achieved by the above code. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save with filename ACJRNyyyymmddhhmm
Jacob, thank you so much. This is excellent!
Thank you for all your help and being so patient with me. Have a great day, Regards, Paul "Jacob Skaria" wrote: OK. Try the below... Dim intFile As Integer, strFile As String, strFolder As String Dim strData As String, rngRow As Range, cell As Range intFile = FreeFile strFolder = "c:\" strFile = "ACJRN" & Format(Now, "YYYYMMDDhhmm") & ".txt" Open strFolder & strFile For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells If IsDate(cell) Then strData = strData & Format(cell.Value, "ddmmyyyy") Else strData = strData & cell End If Next Print #intFile, strData Next Close #intFile If this post helps click Yes --------------- Jacob Skaria "PVANS" wrote: Hi Jacob, sorry for the delay in my response, have been quite busy at work today with several different projects. My further apologies in terms of the information I supplied when I said <Blank. It is not <Blank but instead a Space. I know that in your response you said that they are different entities. Nevertheless, I ran your code, and the date has been correctly formatted without slashes. However, due to my incorrect information, I am still lacking the required space between the tilda in column P and the remaining three ~ in columns R,S, and T. The space of course appearing in the original document in column Q. Is there a method to achieve this? Thanks once again "Jacob Skaria" wrote: When I run your code, and then open the file, the date now has slashes in (eg 22/09/2009), and the <blank has vanished. I need both of these to remain in their original format, ie: the date with no slashes, and the <blank still remaining (eg: ~ ~~~). --Unless dates are formatted before output; the dates will be reflected in the system format. --Blankcell and space are different entities. We will have to handle that in code Try the below with the discussed modifications. Dim intFile As Integer, strFile As String, strFolder As String Dim strData As String, rngRow As Range, cell As Range intFile = FreeFile strFolder = "c:\" strFile = "ACJRN" & Format(Now, "YYYYMMDDhhmm") & ".txt" Open strFolder & strFile For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells If Trim(cell.Text) = "" Then strData = strData & Space(1) ElseIf IsDate(cell) Then strData = strData & Format(cell.Value, "ddmmyyyy") Else strData = strData & Trim(cell) End If Next Print #intFile, strData Next Close #intFile If this post helps click Yes --------------- Jacob Skaria "PVANS" wrote: Hi Jacob, Once again, thank you so much for the assistance. It is saving the file perfectly. If its okay, I would like to ask one further query with relation to the code you have so kindly provided. In the original worksheet, the following columns contain the following: O = the date (with no slashes) eg: 22092009 P = ~ Q = <blank R = ~ S = ~ T = ~ When I run your code, and then open the file, the date now has slashes in (eg 22/09/2009), and the <blank has vanished. I need both of these to remain in their original format, ie: the date with no slashes, and the <blank still remaining (eg: ~ ~~~). I have looked at your code to try and determine why these are both changing, but can not identify the reason. Would you please help me with this? Once again, thanks so much for all your help. Your replies are always friendly and prompt and of great value. Regards "Jacob Skaria" wrote: With the folder name... Dim intFile As Integer, strFile As Stringm, strFolder As String intFile = FreeFile strFolder = "c:\" strFile = "ACJRN" & Format(Now, "YYYYMMDDhhmm") & ".txt" Open strFolder & strFile For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Hi again, please find the modified macro... Dim intFile As Integer, strFile As String intFile = FreeFile strFile = "ACJRN" & Format(Now, "YYYYMMDDhhmm") & ".txt" Open strFile For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile If this post helps click Yes --------------- Jacob Skaria "PVANS" wrote: Good morning I am currently using the following code to save a worksheet as a custom .CSV filetype: intFile = FreeFile Open "c:\test.txt" For Output As #intFile For Each rngRow In ActiveSheet.UsedRange.Rows strData = "" For Each cell In rngRow.Cells strData = strData & Trim(cell) Next Print #intFile, strData Next Close #intFile As you can see, it saves the file as "test.txt". However, as I will need to run this macro daily, I need to be able to save the file with the following filename: "ACJRNyyyymmddhhmm.txt" eg: today would be (assuming I ran the macro at the time I wrote this query) "ACJRN200909231006.txt" Could someone please suggest a method that would achieve this desired filename each day? Please note, it is critical that I keep the formatting, within the file, that has been achieved by the above code. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell("filename") doesn't update to new filename when do save as. | Excel Worksheet Functions | |||
| VBA - Save with New Filename | Excel Programming | |||
| VBA - Save with new filename | Excel Programming | |||
| save as different filename | Excel Programming | |||
| Save Filename | Excel Programming | |||
Linear Mode
