Hi,

Can you upload a sample csv? It looks like an xml file to me, but you also have values outside the field tags.

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.

Hi ,

Try the attached file. First, Make sure you install the Power Query add-in from Microsoft: https://www.microsoft.com/en-us/download/details.aspx?id=39379

Before pressing the button to Import-Export, change the file path to the source data in the File Address sheet.

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...")?

Hi,

You mentioned that you want the result to contain only column A, now I guess you meant column A and column B in a single column, is that right?

Try the attached file, the limit is set in a table, next to the table with source file path.

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 🙂

For ["] problem,

apparently I have fixed it changing FileFormat:=36 (from previous FileFormat:=xlCSV) in the script.

Is a good solution for you?

----------

if yes, now remain the latest question:  is there a way to remove the "request to save" the result in a file (save directly without any input from myself)?

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

See if the attached file is working as expected.

Setting DisplayAlerts to false is redundant, the file name contains a unique time stamp, it's impossible to have the same file name twice, there will never be a message displayed.

FileFormat can be 36, of course, and -4158 as well (xlCurrentPlatformText)

The attached version is not using the SaveAs method, it is simply creating and writing a text file directly.

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 🙂

Yes, you can write before or after the loop from cells:

And yes, you can write text from other text files, you have to open them for reading, copy content and Print #FileNo MyData:

http://stackoverflow.com/questions/20128115/

Dim MyData As String

Open "C:MyFile" For Binary As #1

MyData = Space$(LOF(1))

Get #1, , MyData

Close #1

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

Thanks a lot again. 🙂