Exporting Text File - Append Sequence Character on File Name

[krista.m.chase]

I am building a text file export and I want the file to export with
the following format:

ID + MM + DD + Sequence Character (example 12341107A.txt for ID 1234
on 11/7/2007)

What I want to occur is for the first export the sequence character is
"A", but for any additional exports on that same day the character
needs to be a "B" (on the second export) or a "C" (on the third
export) and so on. Typically the export will only occur once unless
there are issues but I need to be able to reference any existing
exports and make the next file in sequence.

Below is my current code and it is repeating the same file name with a
"B" as the sequence character and deleting the existing file. Any help
is greatly appreciated!

Dim counter As Integer

For counter = 65 To 90 Step 1

retval = Dir$("X:\3500" & Format(Date, "mmdd") & Chr(counter) &
".txt")

If retval = "" Then Exit For
Next counter

counter = counter + 1

DoCmd.TransferText acExportFixed, "3500 Import Specification",
"FileFormatExport", "X:\3500" & Format(Date, "mmdd") & Chr(counter) &
".txt", False, ""

 

[Guest]

Try this after the TransferText action:

Name "C:\Pathtofile\filename.txt" As _
"C:\Pathtofile\FileName" & Format(Date,"ymmdd") & ".txt"

I got this code frm Ken Snell in this group . I used it for the same purpose
and also to change the file extension to .dat.

 

[krista.m.chase]

Try this after the TransferText action:

Name "C:\Pathtofile\filename.txt" As _
"C:\Pathtofile\FileName" & Format(Date,"ymmdd") & ".txt"

I got this code frm Ken Snell in this group . I used it for the same purpose
and also to change the file extension to .dat.














- Show quoted text -

I'm not sure I follow. I am pretty new to writing this kind of
code...

Where do I need to put the code you suggested and does it account for
the counter? The file name changes each day, so I can't hard code it
in. Does that make sense?

Thanks for helping!!

 

[Ken Snell \(MVP\)]

Why do you increment the counter variable before you do the TransferText
action? You should use it without incrementing it. This code should work as
you wish.

Dim counter As Integer
For counter = 65 To 90 Step 1
If Dir("X:\3500" & Format(Date, "mmdd") & _
Chr(counter) & ".txt") = "" Then Exit For
Next counter
If counter > 90 Then
MsgBox "Error. 26 files have already been exported today."
Else
DoCmd.TransferText acExportFixed, "3500 Import Specification", _
"FileFormatExport", "X:\3500" & Format(Date, "mmdd") & _
Chr(counter) & ".txt", False
End If

 

[krista.m.chase]

Why do you increment the counter variable before you do the TransferText
action? You should use it without incrementing it. This code should work as
you wish.

Dim counter As Integer
For counter = 65 To 90 Step 1
If Dir("X:\3500" & Format(Date, "mmdd") & _
Chr(counter) & ".txt") = "" Then Exit For
Next counter
If counter > 90 Then
MsgBox "Error. 26 files have already been exported today."
Else
DoCmd.TransferText acExportFixed, "3500 Import Specification", _
"FileFormatExport", "X:\3500" & Format(Date, "mmdd") & _
Chr(counter) & ".txt", False
End If

--

Ken Snell
<MS ACCESS MVP>















- Show quoted text -

Yeah that worked...thanks so much!! Sorry for not checking in sooner,
I had some other items pop up that took my focus.

Thanks again!!
Krista

 

[netys49]

I am building a text file export and I want the file to export with
the following format:

ID + MM + DD + Sequence Character (example 12341107A.txt for ID 1234
on 11/7/2007)

What I want to occur is for the first export the sequence character is
"A", but for any additional exports on that same day the character
needs to be a "B" (on the second export) or a "C" (on the third
export) and so on. Typically the export will only occur once unless
there are issues but I need to be able to reference any existing
exports and make the next file in sequence.

Below is my current code and it is repeating the same file name with a
"B" as the sequence character and deleting the existing file. Any help
is greatly appreciated!

Dim counter As Integer

For counter = 65 To 90 Step 1

retval = Dir$("X:\3500" & Format(Date, "mmdd") & Chr(counter) &
".txt")

If retval = "" Then Exit For
Next counter

counter = counter + 1

DoCmd.TransferText acExportFixed, "3500 Import Specification",
"FileFormatExport", "X:\3500" & Format(Date, "mmdd") & Chr(counter) &
".txt", False, ""