CODEWITHSUNDEEP
vbaexcelformatting
Eoin2211
Eoin2211

Reputation: 911

Excel Custom Formatting Adding " Before and After Records

I want to add " before my records in excel.
I also want to add "| after each record.

I am using custom formatting in VBA.

My format is "\""@\""|"

My Result is

"""1111""|" """Noel Gallagher""|"   |   """Individual""|"   """Employee""|" """111""|"  """Main Road""|"    """Town""|" """""|" """County"|"    """City""|" |   |   |   |   |   |   |   |   |       |   |   |   |   |   |   """IE""|"   """AAA""|"  """Value""|"

I need 

"1111"| "Noel Gallagher"|""|"Individual"|"Employee"|"111"|"Main Road"|"Town"|""|"County"|"City"|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|"IE"|"AAA"|"Value"|

I don't seem to be able to get the correct result through custom formatting. Is there any alternative solution?

Upvotes: 1

Views: 87

Answers (3)

CLR
CLR

Reputation: 12279

Now I have a better understanding of your problem, the code below should give you what you're after.

This will take the selected range and export it (using your text qualifier and delimiter requirements) to a file called outputfile.txt on the x-drive. Change this as required. This means you don't need to save out the file using Excel, it's all done in the macro.

I've made an assumption that you don't want/need the last pipe on each line of the file. If that's not the case and it is required, remove the line that strips it out (it starts If Right(outputline, 1)...)

Sub export_range_with_quotes_and_pipe()
    vbQt = """"
    delim = "|"
    exportfilename = "x:\outputfile.txt"
    Dim rng As Range
    Set rng = Selection ' or this could be a range such as = range("A2:X50")
    ff = FreeFile
    'create text file for output
    Open exportfilename For Output As ff
    'read each row from range
    For Each r In rng.Rows
        outputline = ""
        'read each cell in row R
        For Each c In r.Cells
            'build outputline from each cell in row R
            outputline = outputline & vbQt & c.Cells(1, 1).Text & vbQt & delim
        Next
        'strip last pipe delimiter as not required
        If Right(outputline, 1) = delim Then outputline = Left(outputline, Len(outputline) - 1)
        'send outputline to file
        Print #ff, outputline
    Next
    ' close file
    Close ff
    Set rng = Nothing
End Sub

Upvotes: 1

CLR
CLR

Reputation: 12279

Warning The code below will alter the cells contents, rather than apply formatting to the cells. For this reason, cells with a formula will be converted to text.

Currently it would run on all currently selected cells, but it's a simple matter to alter this to run on a specific range if required.

Sub add_quotes_and_pipe_to_selected_cells()
    vbQt = """"
    Dim rng As Range
    Set rng = Selection ' or this could be a range such as = range("A2:X50")
    For Each c In rng.Cells
        c.Cells(1, 1) = vbQt & c.Cells(1, 1).Text & vbQt & "|"
    Next
    Set rng = Nothing
End Sub

Upvotes: 1

CLR
CLR

Reputation: 12279

Custom format of \"@\"| should do it?

So in vba.. that would be:

Range("xxx:yyy").NumberFormat = "\""@\""|"

Upvotes: 1

Related Questions