Save with filename ACJRNyyyymmddhhmm

  • Thread starter Thread starter PVANS
  • Start date Start date
P

PVANS

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.
 
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
 
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
 
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
 
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
 
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
 
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, 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
 
Back
Top