Text file column to create variable with commas using VBA

[jam1531]

Hello,

I am looking for some code that will do the following. When the macro is run it will pull only the first column from a text file (path is C:\Users\jmckibb\Desktop\My Stuff\text.txt) and create a variable (if that is the right terminology) that is the first column with a comma behind every row except the last.

For example:

The test file is like so...[TABLE="width: 500"]
<tbody>[TR]
[TD]Dog[/TD]
[TD]Grey[/TD]
[TD]01/02/19[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Green[/TD]
[TD]01/02/19[/TD]
[/TR]
[TR]
[TD]Bird[/TD]
[TD]Pink[/TD]
[TD]01/02/19[/TD]
[/TR]
[TR]
[TD]Snake[/TD]
[TD]Green[/TD]
[TD]01/02/19[/TD]
[/TR]
</tbody>[/TABLE]

When the macro is run I need it to bring back the following and name it "List"

List = "Dog, Cat, Bird, Snake"

It is important that commas are behind each one except the last as I will be using this List to go into SQL and pull further data.

Any help appreciated.

 

[jam1531]

Sorry I left one important note out. The text file columns are separated by spaces, not tab delimited

 

[Joe4]

The text file columns are separated by spaces, not tab delimited

So, exactly how many spaces can the first field contain (or to put it another way, where does the second field begin)?

 

[MickG]

Try this:-
NB:- Alter Path to suit !!!

[COLOR="Navy"]Sub[/COLOR] MG02Jan01
[COLOR="Navy"]Dim[/COLOR] ray, Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] List [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
   Open "C:\Users\USER1\Desktop\test.txt" [COLOR="Navy"]For[/COLOR] Input [COLOR="Navy"]As[/COLOR] [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
   ray = Split(Input(LOF(1), [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] ), vbLf)
      [COLOR="Navy"]For[/COLOR] Rw = 0 To UBound(ray)
        List = List & IIf(List = "", Split(ray(Rw), Chr(32))(0), ", " & Split(ray(Rw), Chr(32))(0))
      [COLOR="Navy"]Next[/COLOR] Rw
 Range("A1") = List
 Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jam1531]

Apologies. I double checked and it actually is tab delimited.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG03Jan52
[COLOR="Navy"]Dim[/COLOR] ray, Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] List [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Open "C:\Users\USER1\Desktop\test3.txt" [COLOR="Navy"]For[/COLOR] Input [COLOR="Navy"]As[/COLOR] [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
   ray = Split(Input(LOF(1), [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] ), vbLf)
      [COLOR="Navy"]For[/COLOR] Rw = 0 To UBound(ray) - 1
        List = List & IIf(List = "", Split(ray(Rw), Chr(9))(0), ", " & Split(ray(Rw), Chr(9))(0))
      [COLOR="Navy"]Next[/COLOR] Rw
 Range("A1") = List
 Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jam1531]

Works like a charm. Thanks!

 

[MickG]

You're welcome