delimited txt file needs to be automated

[kweaver]

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.

[HansV]

I'd record a macro of opening the text file, then edit it.
If that doesn't work, please attach a sample text file.

[kweaver]

Thanks Hans.

What code could I put in the macro that would open a dialog box to prompt for the text file?

[quote name='HansV' post='773684' date='04-May-2009 15:43']I'd record a macro of opening the text file, then edit it.
If that doesn't work, please attach a sample text file.[/quote]

[kweaver]

Hans,

A few other issues/questions.

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.

Thanks again

[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]

[HansV]

[quote name='kweaver' post='773687' date='04-May-2009 21:45']Thanks Hans.

What code could I put in the macro that would open a dialog box to prompt for the text file?[/quote]
See GetOpenFilename Method

[HansV]

It would really help if you attached a sample file.

[kweaver]

Here's a sample.



[quote name='HansV' post='773693' date='04-May-2009 15:57']It would really help if you attached a sample file.[/quote]

[HansV]

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?

[kweaver]

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]

[HansV]

Try this:

Sub ImportText()
  Dim varName
  varName = Application.GetOpenFilename("Text files (*.txt),*.txt")
  If VarType(varName) = vbBoolean Then
	Beep
	Exit Sub
  End If
  Workbooks.OpenText Filename:=varName, _
	Origin:=xlMSDOS, DataType:=xlDelimited, Other:=True, OtherChar:="~", _
	FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), _
	Array(4, 2), Array(5, 1), Array(6, 1), Array(7, 1), _
	Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), _
	Array(12, 1))
  Rows("1:1").Insert Shift:=xlDown
  ' Susbstitute your own headings!
  Range("A1") = "A"
  Range("B1") = "B"
  Range("C1") = "C"
  Range("D1") = "D"
  Range("E1") = "E"
  Range("F1") = "F"
  Range("G1") = "G"
  Range("H1") = "H"
  Range("I1") = "I"
  Range("J1") = "J"
  Range("K1") = "K"
  Range("L1") = "L"
  ActiveSheet.UsedRange.Sort Key1:=Range("L1"), Header:=xlYes
End Sub

[kweaver]

Fantastic, Hans. Thanks.

One more question. Where and what code would I place in this macro to make row1 bold?

[quote name='HansV' post='773700' date='04-May-2009 16:20']Try this:

Sub ImportText()
  Dim varName
  varName = Application.GetOpenFilename("Text files (*.txt),*.txt")
  If VarType(varName) = vbBoolean Then
	Beep
	Exit Sub
  End If
  Workbooks.OpenText Filename:=varName, _
	Origin:=xlMSDOS, DataType:=xlDelimited, Other:=True, OtherChar:="~", _
	FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), _
	Array(4, 2), Array(5, 1), Array(6, 1), Array(7, 1), _
	Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), _
	Array(12, 1))
  Rows("1:1").Insert Shift:=xlDown
  ' Susbstitute your own headings!
  Range("A1") = "A"
  Range("B1") = "B"
  Range("C1") = "C"
  Range("D1") = "D"
  Range("E1") = "E"
  Range("F1") = "F"
  Range("G1") = "G"
  Range("H1") = "H"
  Range("I1") = "I"
  Range("J1") = "J"
  Range("K1") = "K"
  Range("L1") = "L"
  ActiveSheet.UsedRange.Sort Key1:=Range("L1"), Header:=xlYes
End Sub

[/quote]

[HansV]

[quote name='kweaver' post='773703' date='04-May-2009 22:28']One more question. Where and what code would I place in this macro to make row1 bold?[/quote]
Anywhere below the line that inserts a row:

Rows(1).Font.Bold = True