Opening tsv file via Notepad++ and save it in text format

Question

I'm trying to automate a process that where in a folder there are 50+ tsv files. They have to be opened via notepad++ and save it as .txt file in the same folder.

Using the below code i'm opening the tsv file in notepad++.

MyTxtFile = Shell("C:\Program Files (x86)\Notepad++\notepad++.exe I:\Test\Sample.tsv", 1)

However, i have no idea how to save it as txt file by using VBA. Is it doable? If yes kindly teach me how.

Thank you in advance :)

VBA is not meant to be used as automation for other programs. Why don't you just use a mass file renamer (e.g. [Ant Renamer](http://portableapps.com/apps/utilities/ant_renamer_portable)) to rename the tsv files into txt files? — Pᴇʜ, Mar 29 '17 at 07:19
@Peh if im directly renaming it there are some bug forming which is affecting the data. Hence, we have to open in Notepadd++. — Linga, Mar 29 '17 at 07:24
@0m3r if we are saving it in CSV format alignment of the data get collapsed :( — Linga, Mar 29 '17 at 07:26
@Linga If you need to use Notepad++ then VBA might be the wrong tool, because VBA was made to to handle things in Excel and **not** in Notepad++. You should concentrate on removing the bug you are talking about, because .tsv files are .txt files. tsv only means that this is a text file containing tab separated values. Therefore if renaming doesn't work you got the wrong approach. Please describe the bug you have. — Pᴇʜ, Mar 29 '17 at 08:24
More no of rows has been added into one cell while opening it in excel or converting it to CSV. Not sure why this is happening however the above approach is not having any problem.. — Linga, Mar 29 '17 at 09:46
how do you define "More no of rows have been added" was it a random number of rows? was it a random row position? Was it every second row? You have to be a bit more precise if you expect a good answer. You need to figure out where the error comes from. Solving a problem by treating the symptoms but not finding the error is always a bad idea. — Pᴇʜ, Mar 29 '17 at 13:44
@Peh we are unable predict it, that's really the problem here. Its happening in 39th row where its getting added 250 rows in it from 40th row normally data has been seated as expected... Its keep changing against every file 200th row, 150th row etc... — Linga, Mar 30 '17 at 07:44
I think this has something to do with the charset of the files (which maybe gets changed when you save it in Notepad++). I can say that Excel is definitely not the right tool to solve this in combination with Notepad++. Also the question is a bit too broad to be solved on StackOverflow because it is based on question/answer system and very different from a forum where you can discuss different things and maybe provide sample files. I suggest to try it at a good forum to solve this issue. Try to solve the real issue with the files. Using N++ only fights the symptoms but doesn't cure the cancer. — Pᴇʜ, Mar 30 '17 at 07:58
@Peh Thank you your feedback :) If its not possible then i need to look for something else.. I'm new to this scenario tsv to N++ and excel hence want to know is there way... — Linga, Mar 30 '17 at 08:07

score 1 · Answer 1 · answered Mar 29 '17 at 07:25

1

If you only want to rename the files from *.tsv to *.txt you could use the command line

ren *.tsv *.txt

answered Mar 29 '17 at 07:25

FLeX

444
4
13

Sorry FLex its not only for renaming the file :( – Linga Mar 29 '17 at 07:28

score 1 · Accepted Answer · answered Apr 03 '17 at 13:00

Finally found a remedy by exporting via Data => From Text option the above issue can be tackled..

Below is code for the same..

 Do While fname <> ""
Workbooks.Add
Set wBook = ActiveWorkbook
Set wksht = ActiveSheet
   With wksht.QueryTables.Add(Connection:="TEXT;" & folder_name & fname, Destination:=Range("$A$1"))
    .Name = fname
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 65001
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierNone
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

score 0 · Answer 3 · edited May 23 '17 at 11:46

0

There is another thread which touched upon the general aspect of calling shell commands. Maybe it can be of help to you: Execute a command in command prompt using excel VBA

edited May 23 '17 at 11:46

Community

1
1

answered Mar 29 '17 at 07:31

Andreas HD

25
4

Thank you for sharing this :) – Linga Mar 29 '17 at 07:47

Opening tsv file via Notepad++ and save it in text format

3 Answers3

Linked