How can I KEEP blank characters when exporting from Access to Excel?

[Steven Seagal]

I made a query in Access where each record has to have exactly 150 characters. I configured it so that I have a ton of spaces at the end and just do something like left([fieldname] & " ", 150)



Now this works in the query - each record does indeed have 150 characters, but when I export this query to excel either manually or vba, each record stops where the last non-blank character was, so everything is like 70 characters long or something.

I don't think it's an issue with my code, but surely there's a way to keep those blank characters in a specific cell ,correct?

If so, how?

Code: So the query QPCFALL contains the right number of characters, 150, when I view in Access, but when exporting to Excel, it stops at the last non-blank.

Private Sub Command2_Click()
On Error Resume Next
Dim exlApp As Object 'Excel.Application
Dim exlBook As Object 'Excel.Workbook
Dim exlSheet As Object 'Excel.Worksheet
Dim strProjPath As String
Dim stDocName As String
DoCmd.SetWarnings False
Kill "U:\path.xls"
strProjPath = "U:\path"
 
DoCmd.TransferSpreadsheet acExport, , "QPCFALL", "U:path", False, "PCF"
Set exlApp = CreateObject("Excel.Application")
Set exlBook = exlApp.Workbooks.Open("U:\path.xls")

Set exlSheet = exlBook.Worksheets("PCF")
exlSheet.Activate
Call exlSheet.Range("A1:a1").EntireRow.Delete
 
exlApp.Visible = True
DoCmd.SetWarnings True
End Sub

[June7]

Need custom function to create a string value padded with spaces
Review http://www.developerbarn.com/communi...oft-access.24/