Export to text file(s)

[djfeldspiff]

Greetings everyone,

I'm submitting a piece of code for your review. Here's what I'm trying to do.

I have a dB set up to export data from a table to a text file used for printing calling cards. One of our clients has multilpe cards that we fulfill. I'd like to be able to export separate text files based on the "Card Type" the customer requested, each file having it's own file name.

Ex: 10 customers, 8 card type A, 2 card type B. One file of all customers who requested card type A (8 records), a separate file of customers who requested card type B (2 records.

When the code runs (behind a command box as an OnClick event) I get two files with individual file names, but each file contains all data (To reference my example, I would end up with two files, each file with 10 records regardless of the card type requested).

Where am I going wrong? Thanks for your assistance!!

Private Sub StartExportButton_Click()

Dim rstCCIN As Recordset
Dim rst As Recordset
Dim intCounter As Integer
Dim CCFilename As String

' Open Calling Card record sets
Set rstCCIN = CurrentDb.OpenRecordset("CCIN&quot
Set rst = CurrentDb.OpenRecordset("CCIN CARDTYPE&quot

' Display message to enter file name if left blank
If IsNull(Me.File1) Then
MsgBox "ERROR: You must enter the filename.", vbExclamation
GoTo StartExportBtn_Exit
Else
' View and export Calling Card data
rst.MoveLast
rst.MoveFirst
Do Until rst.EOF
For intCounter = 1 To rst.RecordCount
CCFilename = "C:\" & rst!TYPE & Me!File1
DoCmd.TransferText acExportFixed, "Export Specification", "CCIN CARDTYPE", CCFilename
rst.MoveNext
Next
Loop
rst.Close

' Display message that export is complete
MsgBox "Export Completed.", vbInformation, "Export Status"

End If

StartExportBtn_Exit:
Exit Sub

 

[Aivars]

Hi!

You try to save more then one file with same names. You can save its different file names by using additional file name index:

.......
......
......
CCFilename= "C:\" & rst!TYPE & Me!File1 & i & ".txt"
if dir(CCFilename)<>&quot;&quot; then
If MsgBox(&quot;File '&quot; & CCFilename& _
&quot;' already exist. Do you want to replace it?&quot;, _
vbQuestion + vbYesNo, &quot;File already exist&quot = _
vbYes Then
kill CCFilename
DoCmd.TransferText acExportFixed, &quot;Export Specification&quot;, &quot;CCIN CARDTYPE&quot;, CCFilename
end if
end if
.......
.......

Aivars

 

[djfeldspiff]

Thanks much. I like the piece of code prompting the user regarding files that exist.

I'm still having a problem with the actual data being exported. Separate files are created, which is good, but all data is being exported to each of the files regardless of the rst!TYPE. Am I missing a filter or something like that?

Here's how I update my code.

Do Until rst.EOF
For intCounter = 1 To rst.RecordCount
CCFilename = &quot;C:\&quot; & rst!TYPE & Me!File1
If Dir(CCFilename) <> &quot;&quot; Then
If MsgBox(rst!TYPE & Me!File1 & &quot; &quot; & &quot;already exists. Do you want to replace it?&quot;, vbQuestion + vbYesNo, &quot;File exists&quot = vbYes Then
Kill CCFilename
DoCmd.TransferText acExportFixed, &quot;Export Specification&quot;, &quot;CCIN CARDTYPE&quot;, CCFilename
Else
MsgBox &quot;Export aborted.&quot;, vbInformation, &quot;Export Status&quot;
GoTo StartExportBtn_Exit
End If
End If
rst.MoveNext
Next
Loop
rst.Close