Section Sorting with condition | VBA & Macros | Excel Forum

sure.. can't see a button for attachement.(I'm new registered today)

click to download the sample in CSV format https://www.sendspace.com/file/pej6if

Do You want the result in CSV format like above?

Thanks for you reply. For any question I'm here.

Ki$$

I've forget to write.....

PS: for your help to find a solution, IF YOU WANT you can add below the last row of 1st column : SELECT with no more section after the last. (that's not important for me, you can remove last row if this is better for you because i can add it myself after)

PS2: the sections in the sample are 5, but could be more o less of 5.

PS3: I've found the attachment link 🙂

PS4: in the results link you see the 2nd column with number, but at the end must be only the 1st column.

ok, there are some problem. 

I will attach now 6 files

1. automaticTry: automatize all  (please go to ths the script and change your parameter if any) in a rar file because can't upload a .vbs file

2 your file: SortAndExport.xlsm (I've modified import/export in your script from csv to txt )

3 mySample.txt (some sections from MY ORIGINAL FILE)

4 your result (results generated with automaticTry+SortAndExport )

...and the 2 files named:

----

5. RealResult.txt all section sorted by number (with number in B column for better compare with generated youResult file)

Now, if you compare YourResult file and RealResult file you will find some difference...    " ... wrong sorting etc... .

PS: As told above, RealResult file now show B column (to better compare yourself the 2 file)  but in the Final File must be ONLY ONE column (A)

----

PS2: is possible to have a condition, inner the script, to remove row above specified number (Variable) in the final file (example "cut row" above 1.50 value)?

6. RealFinal.txt result (with B column to better compare) and row (number condition) cutted ( FinalResult.txt have only the (A) column in the file.)

-----

...oh yes now you have 6 files, but reallly at the end will be only 2 files (mySample file and SortAndExport) and I must click only automaticTry to have the result.

PS 3: is possible to remove any request to save the file.... (writing or overwriting it directly without "want to save etc...")?

no, you was right... only A column.

and then...

1. I've removed [ (&";"& (if [Column2]=null then "" else Text.From([Column2]))=  ] in last istance on Power QUERY to remove ";" (It's ok?)

2. and I've removed the filter from custom.1 [in Sort Sheet] because don't show data in any field to export. (It's Ok?)

Now seem is ALL perfect in the [import and sort] but in the result [export] file there are ["] in every field. is possible to remove all?

PS: note that [in this example and in attachment]:

your resut is:

"<option value=""MVIS"">MVIS 51.49 0 GM</option>"

but result in every field MUST BE  (MVIS have single "):

<option value="MVIS">MVIS 51.49 0 GM</option>

-------------------------------------------------------------------

After resolving the ["] above, I have last question: is there a way to remove the "request to save" the result in a file (save directly without any OK input from myself)?

Thanks for all your help.. Now I know "Power Query".. it's very useful 🙂

after "FileFormat:=36" (to resolve export problem), apparently... I have resolved last question of the previous post putting "Application.DisplayAlerts = False" in the 2nd row of the script.

Final script is below. It's all ok for you?

Thanks in advance for any reply.

Sub ImpExp()
Application.DisplayAlerts = False
Dim FilePath As String
    ThisWorkbook.RefreshAll
    Application.Wait Now() + TimeSerial(0, 0, 5)
    Sheets("Result").UsedRange.ClearContents
    Range(Range("B4"), Range("B4").End(xlDown)).Copy
   
    Sheets("Result").Cells(1).PasteSpecial Paste:=xlPasteValues
    
    Application.CutCopyMode = False
    FilePath = Sheet1.Range("SourceFile[File Name]")
    FilePath = Left(FilePath, InStrRev(FilePath, "\"))
    Sheets("Result").Copy
    ActiveWorkbook.SaveAs Filename:=FilePath & Format(Now(), "yyyy-mm-dd-hh-mm-ss") & "-Result.txt", _
        FileFormat:=36, CreateBackup:=False
    ActiveWindow.Close
End Sub

I tried to use xlCurrentPlatformText (not 36) but result file show again " everywhere.

anyway , your latest script work very well and now I use it.

PS: in the latest script I see a "Print" at the end to add the row </select>. For every row to add I must put Print?

Example: if I want to add 3 row after </select>

Hi

hello

bye

...

Next
    Print #FileNo, "</select>"

    Print #FileNo, "Hi"

    Print #FileNo, "hello"

    Print #FileNo, "bye"

Close #FileNo

End Sub

If it's right, is there a simple way to add multiple (many) row without write Print for "ANY" row (or maybe to call an external file txt to add these multiple row)?

------------------------

PS2: in the some way as PS1, If I want to add (one or many) row "BEFORE" the first row in a Result file? I can use Print like above? If yes, where I can write print in the script?

That's only a question regarding PS1 and PS2 because your latest script is perfect and I'm very happy to have it. If is too much difficult to rewrite or change the script or spent too much time, tell me friendly because now is all OK (I can simply write myself a batch file to append text (before and after Result.txt to create new Html finished file)

Thanks again for all your help 🙂

Wonderful.. Now I read the link above carefully make some try.

Thanks a lot again. 🙂