Reading line by line data from Text file

**wyldencl** · 05-13-2015, 11:52 PM

Dear all,

Thank you very much for having this forums and help.

I am trying to create a spreadsheet which starts with a user form of which i can input a number into a textbox. On clicking a button in the user form, this number will correspond to a certain row number within a separate text file, select and copy that entire row and print it on excel.

I am very new to VBA and would appreciate if someone could give me a hand with this, i have attached the excel file for reference. thanks in advance!

My current code (which doesn't work) to copy off the spreadsheet is something like this:

Please Login or Register  to view this content.

**Leith Ross** · 05-14-2015, 12:29 AM

Hello wyldencl,

Welcome to the Forum!

You should also post a sample text file that can be used for testing.

**wyldencl** · 05-14-2015, 03:11 AM

Hi Leith,

Thx for your reply. I have uploaded the txt file.

**Leith Ross** · 05-14-2015, 04:16 PM

Hello wyldencl,

Thanks for attaching the text file. I have a few questions though.

1) How are the fields (columns) separated?
Generally a text file uses either commas or tabs to separate the columns, or sometimes each field is a specific number of spaces wide. In your file it appears to be tabs, but I'm not certain.
Rows are terminated with either a Line Feed, Carriage Return, or both. I don't see either in your file.

2) Is this text file download from the web?
I ask because it is stock data and most sites have a text file download button. The files I have seen in the US have the values separated by commas.

3) How are the lines numbered in the text file?
There appear to be no line numbers in the file.

Knowing how the file is formatted is key to coding a macro to read, find, and write data to and from the file. Attached is a copy of your text file that I have separated with tabs. A header "Empty" has been added to show where the empty column is. Each field is separated by a single tab. That is why the data looks misaligned with the headers. The macro can read it and place the proper value in the proper column of the worksheet.

**wyldencl** · 05-14-2015, 11:14 PM

Hi Leith,

Thanks so much again for your help!

I was oblivious of the spacing rule before this. Yes the data can be formatted to look like that in your text file!

Is there a way for vba to read the lines without proper numbering on the right hand side? If so won't be a problem as well, it can be added.

How will we write a basic code to reference the text file outside of excel. Thanks again in advance!

Originally Posted by Leith Ross

Hello wyldencl,

Thanks for attaching the text file. I have a few questions though.

1) How are the fields (columns) separated?
Generally a text file uses either commas or tabs to separate the columns, or sometimes each field is a specific number of spaces wide. In your file it appears to be tabs, but I'm not certain.
Rows are terminated with either a Line Feed, Carriage Return, or both. I don't see either in your file.

2) Is this text file download from the web?
I ask because it is stock data and most sites have a text file download button. The files I have seen in the US have the values separated by commas.

3) How are the lines numbered in the text file?
There appear to be no line numbers in the file.

Knowing how the file is formatted is key to coding a macro to read, find, and write data to and from the file. Attached is a copy of your text file that I have separated with tabs. A header "Empty" has been added to show where the empty column is. Each field is separated by a single tab. That is why the data looks misaligned with the headers. The macro can read it and place the proper value in the proper column of the worksheet.

**wyldencl** · 05-14-2015, 11:46 PM

Hi Leith,

Thanks so much again for your help!

I was oblivious of the spacing rule before this. Yes the data can be formatted to look like that in your text file!

Is there a way for vba to read the lines without proper numbering on the right hand side? If so won't be a problem as well, it can be added.

How will we write a basic code to reference the text file outside of excel. Thanks again in advance appreciate it!

Originally Posted by Leith Ross

Hello wyldencl,

Thanks for attaching the text file. I have a few questions though.

1) How are the fields (columns) separated?
Generally a text file uses either commas or tabs to separate the columns, or sometimes each field is a specific number of spaces wide. In your file it appears to be tabs, but I'm not certain.
Rows are terminated with either a Line Feed, Carriage Return, or both. I don't see either in your file.

2) Is this text file download from the web?
I ask because it is stock data and most sites have a text file download button. The files I have seen in the US have the values separated by commas.

3) How are the lines numbered in the text file?
There appear to be no line numbers in the file.

Knowing how the file is formatted is key to coding a macro to read, find, and write data to and from the file. Attached is a copy of your text file that I have separated with tabs. A header "Empty" has been added to show where the empty column is. Each field is separated by a single tab. That is why the data looks misaligned with the headers. The macro can read it and place the proper value in the proper column of the worksheet.

**Leith Ross** · 05-15-2015, 12:36 AM

Hello wyldencl,

Yes, VBA can read the file without proper number. However, it is better if line numbers or some other unique record identifier is present.

From your last post, it sounds like VBA will create this text file from the information on the worksheet and the UserForm will edit the worksheet and update the text file as needed.

Am I correct about that?

**wyldencl** · 05-16-2015, 02:00 AM

Hi Leith,

VBA does not create the text file. It will be generated from another system.

Excel just has to reference that particular file and read off it.

**Leith Ross** · 05-16-2015, 02:48 PM

Hello wyldencl,

Is the text file you posted one of these generated files?

**wyldencl** · 05-16-2015, 11:40 PM

Hi Leith,

Yes it is.

Will need to read off that file.

**wyldencl** · 05-17-2015, 08:03 AM

Hi Leith,

If possible i can use input on reading off that text file you separated also. Probably could use that as a base to work on.

I have googled out some codes to output the entire data on excel, but what i need is to specifically use a userform to input a line number and have excel print that particular line on the spreadsheet when i click a button

Please Login or Register  to view this content.

**Leith Ross** · 05-17-2015, 07:36 PM

Hello wyldencl,

I have made some changes to the workbook you posted. Hopefully this now does what you want.

There is a button named "Run" on the "Userform" worksheet to display the UserForm. I also included two macros.One to read the raw data from the text file and copy it to Sheet1 (Blad1). This allows the user to select the file to be opened and copied onto Blad1. The other macro uses the input line number from the UserForm and returns the row data associated with it to Blad2 Range("A6").

New Macros Added

Please Login or Register  to view this content.

**wyldencl** · 05-18-2015, 11:43 AM

Hi Leith!

I cannot be more grateful for your help in this! Works exactly like the way i need it to be. Really fortunate to have people like you around. I hope in time to come i can build up enough skills to recontribute to this forum!

**wyldencl** · 05-18-2015, 11:43 AM

Hi Leith!

I cannot be more grateful for your help in this! Works exactly like the way i need it to be. Really fortunate to have people like you around. I hope in time to come i can build up enough skills to recontribute to this forum!

**Leith Ross** · 05-18-2015, 12:50 PM

Hello wyldencl,

You're welcome. It is good to hear it works like you wanted.

**wyldencl** · 05-18-2015, 10:03 PM

Hi Leith,

I am just wondering, is there any way we can skip copying the raw data from text file into "BLAD1" and read straight off it from excel?

**Leith Ross** · 05-18-2015, 10:09 PM

Hello wyldencl,

It is much more efficient and faster to work with to copy the text to worksheet. If you are concerned about the users altering the data once it is copied, you can always hide the worksheet from view.

Reading line by line data from Text file

LinkBack

Thread Tools

Rate This Thread

Display

Reading line by line data from Text file

Re: Reading line by line data from Text file

Re: Reading line by line data from Text file

Re: Reading line by line data from Text file

Re: Reading line by line data from Text file

Re: Reading line by line data from Text file

Re: Reading line by line data from Text file

Re: Reading line by line data from Text file

Re: Reading line by line data from Text file

Re: Reading line by line data from Text file

Re: Reading line by line data from Text file

Re: Reading line by line data from Text file

Re: Reading line by line data from Text file

Re: Reading line by line data from Text file

Re: Reading line by line data from Text file

Re: Reading line by line data from Text file

Re: Reading line by line data from Text file

Thread Information

Users Browsing this Thread

Similar Threads

Reading array of data line by line (stock tick data)

Reading in text file into array. Each element is a line of file. Why last element a space

[SOLVED] STart reading text file at line 2

Reading a text file line by line-How do I open the text file with VBA

Reading a text file line by line

Bookmarks

Bookmarks

Posting Permissions