CODEWITHSUNDEEP
excelms-accessvba
turezky
turezky

Reputation: 856

New line within cell when exporting from Access to Excel

Is there a way to export "new line" within cell properly when exporting from Access to Excel.

The data is entered in Access and new line in Access is showing OK.

However, when I export it to Excel it doesn't look well.

Tried to clean the text by removing all possible new line characters before inserting the value into Excel as follows (note that new line is required after the "comma" sign):

    FText = Replace(rs_Quotation!Text, vbCrLf, "")
    FText = Replace(rs_Quotation!Text, vbLf, "")
    FText = Replace(rs_Quotation!Text, vbNewLine, "")
    FText = Replace(rs_Quotation!Text, vbCr, "")
    FText = Replace(rs_Quotation!Text, ", ", "," & vbCr)

It seems good but when opened in Excel it adds some extra space above the text and some spaces between the lines:

Example of blank text above cell contents

However, after double clicking and exiting cell it gets to normal.

UPDATE: "AutoFit" method for EntireRow didn't produce any effect too.

Upvotes: 0

Views: 1014

Answers (1)

DiegoAndresJAY
DiegoAndresJAY

Reputation: 706

It looks like you are going back to the source with every replace so only the last one is doing anything to your output. Try this:

FText = Replace(rs_Quotation!Text, vbCrLf, "")
FText = Replace(FText, vbLf, "")
FText = Replace(FText, vbNewLine, "")
FText = Replace(FText, vbCr, "")
FText = Replace(FText, ", ", "," & vbCr)

Add the following function to your code.

Public Function String2ASCIIString(ByVal strText As String) As String
    Dim i as integer
    Dim s as string

    For i = 1 To Len(strText)
        s = s & asc(Mid(strText, i, 1)) & ","
    Next
    String2ASCIIString = Left(s, Len(s) - 1)
End Function

Add this next line just after the first block of code above.

Debug.Print String2ASCIIString(FText)

Run the code, press Ctrl+G, copy and paste the string of numbers as a comment.

Upvotes: 1

Related Questions