Process data when found ">" character and keep parse

[Do Duy]

Hi all, actually I have problem when process data.
I have code process in C# but dont know how to using same function in VBA.
Just like it auto delete comment in data, when found ">" character it auto make a breakdown line and keep parse.

Please look at attached file for more details

 

[Marc L]

Hi !

If you already have a C# process, just use it on the source text file
before to load it in a worksheet : it's more efficient than a VBA procedure !

Otherwise, explain the C# process logic …
​

 

[Marc L]

If you know C# coding, you can use as well a XML library in VBA,
samples and tutorials all over the Web …​

when found ">" character it auto make a breakdown

Sub Demo()
    With Worksheets("Sample")
        SPQ = Split(Replace(.[B2].Value, ">", ">¤"), "¤")
         U& = UBound(SPQ)
        For R& = 2 To U
            If Left(SPQ(R), 1) <> "<" Then
                SPQ(R - 1) = SPQ(R - 1) & SPQ(R)
                         U = U - 1
                For N& = R To U:  SPQ(N) = SPQ(N + 1):  Next
            End If
        Next
        .[E2].Resize(U).Value = Application.Transpose(SPQ)
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Do Duy]

Thank so much Marc L.

But can you make output data have tag like that
<POS>
<Source PseudoCityCode>
<RequestorID>
<CompanyName />
</RequestorID>
</Source>
</POS>
If tag not close yet (</*>), the next tag will have 2 space before "<".
For Example :
<Source PseudoCityCode> this tag not close yet
<Request ID> this tag will have 2 space before <
and if tag close </*> then it remove 2 space before <. Just like when you paste code to notepad++.

Sorry if I make too much trouble to you

 

[Marc L]

Try this :​

Sub Demo1()
With Worksheets("Sample")
        SPQ = Split(Replace(.[B2].Value, ">", ">¤"), "¤")
         U& = UBound(SPQ)
    While R& < U
        If SPQ(R) Like "<*" Then
                     R = R + 1
        Else
            SPQ(R - 1) = SPQ(R - 1) & SPQ(R)
                     U = U - 1
            For N& = R To U:  SPQ(N) = SPQ(N + 1):  Next
        End If
    Wend
    For R = 0 To U - 1
        If SPQ(R) Like "</*" Then
                S$ = Left$(S$, Len(S$) - 2)
            SPQ(R) = S & SPQ(R)
        Else
                ST = Split(SPQ(R), "<")
            SPQ(R) = S & SPQ(R)
            If Not (SPQ(R) Like "*/>" Or ST(UBound(ST)) Like "/*") Then S = S & "  "
        End If
    Next
        .[E2].Resize(U).Value = Application.Transpose(SPQ)
End With
End Sub

You should Like it !

Edit for optimization, I had no much time …
​

 

[Do Duy]

Marc L It amazing, thank so much

 

[Deepak]

New Query!

http://forum.chandoo.org/threads/ta...te-marco-copy-data-and-paste-then-loop.26785/

 

[Marc L]

As I already wrote, a C# program in far much more efficient than VBA !
Why don't you use it ?‼
Who is the fool mixing csv and XML data in same chinese puzzle file ?‼

If source is a true csv text file, when you double click on it from
Windows Explorer, it is opened and well formated in Excel.
The same if you open it manually in Excel.
So just activate Macro recorder and operate manually :
you will generate your own code base !

Read also in VBA help Workbooks.OpenText method …
And tutorials about VBA and XML …
​

 

[Do Duy]

@Marc L
your code fit with what I needed, now I dont know how to make a loop to excute marco
I think the last problem here is how to make loop to process my flow

 

[Marc L]

You already have in my code two kinds of loop as For … Next and
While … Wend ! See also in VBA help Do … Loop …

To begin, generate a code at least for one and, after you post it here
between code tags and some crystal clear explanation of the need,
someone will try to help you …

Otherwise think about commercial services.
​

 

[Do Duy]

Hi Marc L

This is my code to execute my flow job.

Sub process()

    Windows("search_result 8IS6.csv").Activate
    Range("Q2").Select        'I need loop to execute for next cell Q3 untill end of data
    Selection.Copy
    Windows("sample.xlsm").Activate
    Range("B2").Select
    ActiveSheet.Paste
    Application.Run "sample.xlsm!Sheet2.Demo1"
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("process").Select
    ActiveWindow.SmallScroll Down:=-12
    Range("G1").Select
    ActiveSheet.Paste
    Application.Run "sample.xlsm!Macro8"
    Sheets("Summary").Select
    Range("E8").Select       'I need loop to put data into F8 and so on
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  
End Sub

Sorry, but can you help me make a loop for next cell Q4, Q5... and put processed data into F8, G8...

 

[Marc L]

Just see For Each … Next block statement in VBA help as its sample …


​