Fixed Width Delim File problems

[me1258]

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
 
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...

 

[The_Doc_Man]

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.

 

[Moniker]

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