Results 1 to 12 of 12
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd record a macro of opening the text file, then edit it.
    If that doesn't work, please attach a sample text file.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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]

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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]

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It would really help if you attached a sample file.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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]
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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?

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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]

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this:

    Code:
    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

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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:

    Code:
    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]

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •