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:



    e.g.


    Code
    '// 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!