copying text from a file (ie for notepad) and copying the text into seperate col

Question

copying text from a file (ie for notepad) and copying the text into seperate columns using tab and space = True to an active excel worksheet using VBA

I need to copy the text from a text file "prt." which is in the form of four columns of numbers. I need to separate the columns and remove the first row of the "prt." file when it is imported intp the active excel workbook but I am having trouble writing the correct "workbook.opentextfilename" command line in my code. I cannot even make it work when the "prt." file is first manually made into a separate excel file/workbook.

The "prt." file has no actual file format after the fullstop as it is opened using notepad though it is a textfile of some sorts. I think is the part where that needs to change. Anyway...

Could someone please give me a simple code that I can put into my macro to make this work. The simpler the better please...

Explanation / Answer

.Can you open the file in the Excel Interactive mode?/ If so, then with a blank @@workbook displayed turn on the macro recorder and use file Open to open the text file. You will get a dialog box to allow you to set Fixed width of columns or delimiters so go through the dialog and complete the required sections and it will code most of it for you. Note that in the preview of the columns you can insert, move or delete the column lines as required.

If you have date fields you will get a page in the dialog where you can select the required column and then tell Excel the Date format. This is the part many get wrong. The date format to select is the format used in the text file; NOT the finished format that you want displayed because this will display in your default date format.

Stop the recorder after opening the text file. The recorded code will be in the blank workbook that you had open; not in the text file that you opened../

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.