Automatic Export of a Access 2007 in a fixed width Delimited format.
I have a program that I need to export a table T_ExportToAccounting that I need to export in a delimited format on to a floppy disk. I have read through the posts and solutions but still don't understand how to do this. The user should be able to put in a floppy and hit down load and thats it. I need some step by step instructions to accomplish this. I have been fighting with the wizard all day and it will just not work.
The file the wizard puts out which is the second block of data below is perfect but when I try to call this specification I get the crap you see here in the first block of code. I have been over and over the wizard and just cant figure out why it is doing this.
Here is my code I am using
DoCmd.TransferText acExportDelim, "IslandTen", "T_ExportToAccounting", "C:\Island9.txt", False

ad
5.00
4.00

-1
 
auto
12.00
2.00

-1
 :mad: :mad:
bike
2.00
2.00

-1
 
chi
2.50
2.00

-1
 
mc
7.00
2.00

-1
 
mop
4.00
2.00

-1
 
mc
7.00
2.00

-1
 
mop
4.00
2.00

-1
 


it should look like this

ad 5.00 4.00 -1
auto 12.00 2.00 -1
bike 2.00 2.00 -1
chi 2.50 2.00 -1
mc 7.00 2.00 -1
mop 4.00 2.00 -1
mc 7.00 2.00 -1
mop 4.00 2.00 -1

its soooo infuriating I have been fighting it for hrs...:mad: :mad: :mad:

Any chance that you had a UNICODE characterset selected?

If you manually export once, save the output specification, and then later export with that specification, the trick is to get it to work manually first. THEN try the automation. If it works manually but fails via VBA, that is a different problem than if you haven't gotten to work manually yet.

Are you exporting carriage returns? That's what will happen. If you right-click a table, query, etc. and select properties, then type in a description with a carriage return in it, you'll get that same character output in your description. Unless you're outputing thousands of rows, it's far simpler to connect to an Excel object and have 100% control over it rather than using the DoCmd version of it all. This will export any table/query you want. It's got some stuff in it that isn't relevant as this is taken directly from an exporter I wrote (all the txt prefixes are variables from a form not shown here and it will handle specific row counts per sheet, etc.), but it should give you the general idea on how to do it.

Sub Exporter()
Dim xlApp As Excel.Application
Dim xlWkbk As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim rsTempRecs As ADODB.Recordset
Dim rLoop As Long
Dim cLoop As Integer
Dim ColCount As Integer
Dim SheetCount As Integer
Dim RecCount As Long
Dim ctr As Integer

Set xlApp = New Excel.Application
xlApp.DisplayAlerts = False
xlApp.AskToUpdateLinks = False
Set xlWkbk = xlApp.Workbooks.Add

Set rsTempRecs = New ADODB.Recordset
rsTempRecs.Open "SELECT * FROM " & cboTableList, CurrentProject.Connection, adOpenKeyset, adLockReadOnly

With rsTempRecs
.MoveLast
.MoveFirst
ctr = 1
RecCount = .RecordCount
ColCount = .Fields.Count
SheetCount = Switch(RecCount < txtRowCount, 1, True, Int(RecCount / txtRowCount) + 1)
While .AbsolutePosition < txtRowCount * ctr
For ctr = 1 To SheetCount
Set xlSht = xlWkbk.Worksheets.Add
xlSht.Name = txtSheetName & ctr
For rLoop = 0 To txtRowCount - 1
If .EOF Then GoTo Exit Sub
For cLoop = 0 To ColCount - 1
xlSht.Cells(rLoop + 1 + Abs(chkIncludeHeader), cLoop + 1).Value = .Fields(cLoop).Value
Next
.MoveNext
Next
Next
Wend
.Close
End With

xlWkbk.SaveAs txtFilePath & "\" & txtFilename
xlWkbk.Close
xlApp.Quit
Set xlSht = Nothing
Set xlWkbk = Nothing
Set xlApp = Nothing

End Sub