Adding Space to Filed on Export

[SteveE]

I am exporting a routing table or query
by the main fileld is a Postal Sector text field of 5 chr length. This field includs some with trailing spaces which must be included i.e. s=space; AAsss or AAAss or AAAAs.

The reason the Space is required is that this output is imported into a progress WMS system and that system will always look for the spaces to match.

These are ok in the table and all that require have them but whatever way I try to export output actable, output acQuery Transfer Spreadsheet etc the spaces are removed. The only way I have veen sucessfull is to open the query or table and copy all the data then paste into Excel but because this table is 30000 lines of 24 fields this copy / paste process is clumbersome to say the least. My data base is in Access 2000 but I have converted it to 2007and still have the same issue.
So looking for some advise here on is there a way to export with the space into excel ?

 

[MarkK]

There is a Space() function you can use in a query, so in the design grid you might set it up like this...

Output: Field & Space(<CharLimit> - Len(Field))

...where you replace <CharLimit> with the final length of chars you need. You can see that the math adds the number of spaces required to pad out the field.

 

[SteveE]

Thanks but that works within access but still is lost on the export. I have been searching around and have found a piece of code which does work even when exporting to Excel2003 or 2007 just in case its any use to anyone else its below.
thank for your responses.
Not 100% sure where I picked it up but I think it was from Bob Larson thanks Bob

Function ExportFilteredData()
On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("OutPutCombinedGazz", dbOpenSnapshot)
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim strFileName As String
strFileName = "C:\Importfiles\CombiniedCarrier" & Format(Date, "MMDD") & ".xls"
Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
Dim i As Integer
Dim iNumCols As Integer
iNumCols = rs.Fields.Count
For i = 1 To iNumCols
oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next 'Add the data starting at cell A2
oSheet.Range("A2").CopyFromRecordset rs 'Format the header row as bold and autofit the columns
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
'.AutoFilterMode = False
.Range("A1:W1").AutoFilter

End With
oApp.Visible = True
ActiveWorkbook.SaveAs (strFileName)
oApp.UserControl = True 'Close the Database and Recordset
rs.Close
db.Close

End Function