VBA, Delimit Excel File before performing task.

  • Hi All,

    I have a code that goes through workbooks and extracts data. The problem is some files are not formatted in a way I can extract (i.e. all data stuffed into column A and must be 'delimited' or 'text-to-columns' to see data properly)

    Would anyone know how I can delimit each workbook prior to opening and performing my task?
    The issue is some need to be delimited and some don't.

    Any suggestions/ideas would be greatly appreciated.

    Here is my current code:

  • Re: VBA, Delimit Excel File before performing task.

    Just de-limit the data after the workbook has opened using text to columns or the Split() method:


    '// Let's assume A1 contains "123|456|789|ABC|DEF|GHI"
    Dim splitText As Variant
    With Range("A1")
        splitText = Split(.Value, "|")
        .Resize(1, UBound(splitText) + 1).Value = splitText
    End With

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!