Benji Taylor Benji Taylor - 1 year ago 36
SQL Question

Excel script into excel & split by ;

Hey guys i have a folder with a long list of scripts with around 10 insert statements in each,

Just wondering if there is anyway i can easily import all these scripts into an excel spreadsheet (not the script names but the actual contents) and have them separated by the delimiter ; so one insert statement per cell?

I have a half decent understanding of VBA, if no one wishes to provide an answer a point in the right direction would be much appreciated :)

Thanks in advance

Tom Tom
Answer Source

This should get you started. Read your string into the split statement (I've just mocked this up using the values in cell A1 of Sheet1 of a new work book. This then prints all the values split to the second row.

Combine this with Dave's comment above and you should have your solution

Sub SplitStatements()
    Dim NoOfStatements

    With Sheet1
        NoOfStatements = Len(.Cells(1, 1)) - Len(Replace(.Cells(1, 1), ";", ""))
        .Range(.Cells(2, 1), .Cells(2, NoOfStatements + 1)) = Split(.Cells(1, 1), ";")
    End With
End Sub

enter image description here