I'm trying to get together a macro that will allow me to browse a certain folder for a .txt file, and import the selected file while changing columns to text so as to keep the leading zero's in the imported text. The text file will have 12 columns, "|" as the delimiter, and if possible just columns 2 and 8 to be changed to text formatting.
Call Application.GetOpenFileName for the browse part. With the macro recorder, do File - Open - to run Text Import Wizard on the .txt file, specifying text format for the appropriate columns. Edit the code generated to combine the browse part and copy data from the opened .txt file to your workbook. Hopefully that should be enough to get you started.
Post responsibly. Search for excelforum.com
Give this a try
Please Login or Register to view this content.
Thanks,
Mike
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
Alternate method, just change the "00000" to be a number of 0's equal to the the total length including leading 0's that you need. As is, all numbers will become 5 digits with leading 0's, so 123 will become 00123:
Please Login or Register to view this content.
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Mike - For some reason your code is giving me a Compile Error: Expected Array message regarding the, with the (Lines) highlighted.Please Login or Register to view this content.
Tigeravatar - The problem with this method is that the numbers may change depending on the .txt file imported, the only way I've actually been able to get them to come in correctly is changing the columns to text during the text import process.
I attempted to combine my macro recorder code along with the dialogue box to browse for the file, but I'm having trouble having it import the text to the current Workbook and it's formatting, and trouble having the two columns import with text formatting. I'll post the code I'm currently messing with, if anybody has an idea.Please Login or Register to view this content.
Alright, here's code to change columns B and H to text format and then perform the TextToColumns, let me know if that works for you:
Please Login or Register to view this content.
Alternately, this will tell the TextToColumns function to put column 2 and 8 as datatype 2 (Text) during that function:
Please Login or Register to view this content.
Thanks very much, it worked great.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks