macro help! 255 character limit
Posted by Jon on January 17, 2002 4:30 PM
Can anyone point me in the right direction with this problem? Basically I'm trying to setup four columns in Excel that will take text from (let's say notepad), put 254 characters in the first column, 254 in the 2nd, 3rd, etc. Could I create a macro to do this?
Any help would be great.
Posted by Damon Ostrander on January 17, 2002 11:29 PM
Hi Jon,
Yes, this can be done fairly easily. You mention reading the text from Notepad. Does this mean you want to do a select and copy of the text from Notepad, or you want to read the text from a Notepad .txt file?
If you want to read the data from the clipboard, this is doable, but you have to create a userform because the GetFromClipboard Method is only available from a userform.
If you want to read from a file, this can be easily done using VBA I/O statements (Open, Input #, Line Input #, or Get #, EOF and Close) to read the data directly from the file. If you use the Get # statement you can read 254 characters at a time. If you use Line Input #, it will read to a carriage return character, so if the file contains these you will need to use the Get. But if not, you can read the data into a single string variable, and parse the data out 254 characters at a time using the Mid function. For example, if your long string is named Rec, cell 1 (i.e., column 1) would contain:
Mid(Rec,1,254)
cell 2: Mid(Rec,255,254)
cell 3: Mid(Rec,509,254)
etc.
I hope this helps.
Damon
