Without VB skills, this has become a nightmare for me.
I get a txt file with the fields delimited by a "~" symbol.
I need to automate the process with a macro that brings the file into Excel.
Then, fix the "text" in column D. Some of the entries in column D have letters in them which forces those cells to text.
Some of the cells have numeric values that should be text to avoid them showing as numbers or in scientific notation, etc.
Then, insert a new first row that contains column headings for A to L.
Then, sort the data in ascending order based on column L.
Then, remove the top blank rows (there may be none or some that are blank.
Actually, it would be really nice if the macro prompted for the input text file.
I've done this manually a number of times, but just can't get the right macro to work.
I more than welcome any and all help!!!
Thanks in advance.
Subscribe to get a FREE chapter from Windows 7 The Missing Manual
This month, every Windows Secrets subscriber can download a one-chapter excerpt of Windows 7: The Missing Manual.Windows 7: The Missing Manual provides valuable information to help you overcome these difficulties in learning a new operating system. Subscribe today to download your free excerpt.
Once I have the macro bring in the txt file and convert the problem D column, and sort on L,
I don't know what to write to remove the leading (top) blank rows because the number of blank rows will vary.
[quote name='kweaver' post='773687' date='04-May-2009 15:45']Thanks Hans.
What code could I put in the macro that would open a dialog box to prompt for the text file?[/quote]
In a previous sample I had blank rows at the top. I need to remove all blank rows from the file.
Notice that column D shows some of the numbers as scientific notation. Converting the format to text doesn't seem to help.
[quote name='HansV' post='773697' date='04-May-2009 16:05']This appears to be a fixed-width text file. And there are no blank rows at the top, so how would you expect us to do anything with blank rows?[/quote]