MS Excel / VBA help thread

It's not that big a deal to change the region settings but inevitably I forget to change them back and so on and it's annoying if it's saving via VBA

There are nearly always random commas in the data which is why I want to save as | delimited and find/replace won't work.

I found this macro which seems to do the job and isn't too slow (about 3 minutes for saving a 1m row, 13 column spreadsheet)

Sub DelimFile()

Open "C:\pipe.csv" For Output As 1 'Change this path

rowno = 1
colCount = Application.CountA(ActiveSheet.Rows(1))
While ActiveSheet.Cells(rowno, 1) <> ""
    dataout = ""
    For c = 1 To colCount
        If c <> colCount Then
            dataout = dataout & Trim(ActiveSheet.Cells(rowno, c)) & "|"
        Else
            dataout = dataout & Trim(ActiveSheet.Cells(rowno, c))
        End If
    Next c
    Print #1, dataout
    rowno = rowno + 1
Wend
Close #1
End Sub