Improve existing VBA loop to clean SAP output for MS Access


New Member
Aug 14, 2013
So, I get an SAP output from a SPOOL. It comes out as a terrible "|" delimitated .CSV file in multiple "pages" which are actually blank rows with "---------" in between and some other text. I have to clean it before importing it in to Access which I have a VBA script for.

I have this code to do text-to-columns and it works great!
[FONT=arial]Sub TextToColumns()[/FONT]
[FONT=arial]    Dim ws As Worksheet[/FONT]

[FONT=arial]    For Each ws In Worksheets[/FONT]
[FONT=arial]        ws.Columns("A:A").<wbr>TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _[/FONT]
[FONT=arial]        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _[/FONT]
[FONT=arial]        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|"[/FONT]
[FONT=arial]    Next ws[/FONT]
[FONT=arial]End Sub[/FONT]
These are the steps I take to clean from the raw output from SAP:

1) Concatenate all cells in the 1st row like "=A1&B1&C1.....&Z1" ..... because SAP will continue delimitation overflow in to column B,C and some others... its pretty variable depending on the amount of columns I'm exporting from excel.

I do that in column AA then fill it down till there are no more rows. Sometimes is 3,000 rows and sometimes its 500,000 rows.

2) Once that is all in AA, I copy it to a new sheet in A1

3) Text-to-columns (now I can use that VBA code)

4) TRIM all column headings (I usually copy paste in a new sheet, transpose, trim, copy/paste values, transpose back)

5) Filter and delete all things in column "B" that either BLANK or begin with "----"

If someone could help me, I'd freak out. Kind of looking like this at first :eek: then :LOL:


New Member
Aug 14, 2013
This looks like its working to combine the column headings. Now I need it to get it to copy to another spreadsheet then perform the text to columns.

Sub ConCat()    With Range("A1", Range("A" & Rows.Count).End(xlUp))
        .Offset(, 10).Value = Evaluate(.Address & "& """" & " & .Offset(, 2).Address)
    End With
End Sub

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...