Hello vbadumb.
_1) To copy contents of a text file to a column in an Excel worksheet
There are many ways to do that. I am not familiar with them all. Some ways may be better different text files.
I can show one way that I am familiar with.
In Brief how it works:
It gets an entire text file as a single long text string. ( '2a)(i) )
This string is placed in the windows clipboard ( Rem 3 )
That string is pasted into a worksheet. ( Rem 4 )
See attached text file, " vbadumb.txt"
Inside that text file is 3 lines of data. There is one piece of data in each line, 1 in first line, 2 in second line, and 3 in the third and last line :
1
2
3
vbatext.JPG : https://imgur.com/WKNmChc
vbatext.jpg
Coding description
The macro here, http://www.excelfox.com/forum/showth...ll=1#post11810 ,gets the entire text file as a single long text string. '2a)(i)
If we were to examine that string, strtxtFile , then we would see something like
"1" & vbCr & vbLf & "2" & vbCr & vbLf & "3"
Rem 3 We can put the string, strtxtFile , into the clipboard. It is somewhat a mystery to most people how all the Microsoft clipboards ( Office Clipboard, Excel Clipboard, Windows Clipboard ) work, but with some practice , we can usually find a way to put a string into the ( Windows ) clipboard, such that the Worksheets .Paste Method used within Excel will recognise that text as a text data range. In our simple example it is relatively easy, since the vbCr & vbLf is recognised by Excel as the row separator. Hence our data would be .Pasted across 3 lines.
Rem 4 Paste out to the worksheet
If we have done no other interactions with clipboards from Excel, then it appears that a default link will look for data in the Windows Clipboard.
If we paste the entire string in / at B1 , then the vbCr & vbLf in the string will be interpreted by Excel as the row separator, so the entire string will be split over 3 lines.
_____ Workbook: vbadumb.xls ( Using Excel 2007 32 bit )
Worksheet: FILENAME
Row\Col
The macro here http://www.excelfox.com/forum/showth...ll=1#post11810 , is also in the attached Excel file, " vbadumb.xls "
To run a demo , Do this
Save both files, " vbadumb.txt" and " vbadumb.xls" , in the same Folder
Run macro,
Sub txtfilesinworksheetwithFILENAMEColumnB()
You should then see this in Column B of vbadumb.xls
vbadumb xls AFTER.JPG : https://imgur.com/6T951BP
vbadumb xls AFTER.JPG
_2) I still do not understand what else you want.
Your explanations are too brief for me to understand. I need full and detailed explanation of all what you want. I am unable to help further without a more detailed explanation of what you want.
I am away for a few days. If you are able to give a more full explanation, possibly with examples then I will look in again in a few days , if you still then require help
Otherwise, good luck
Alan.
Reply With Quote
Bookmarks