Results 1 to 15 of 15
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    With Hans' usual great help, I've been able to do the first leg of a project where I import a text file into Excel with various issues that I needed in a macro.

    Now, it turns out the project is a bit more complicated in that the txt file which is to be imported has a group of different "IDs" in what will become column A of the spreadsheet.

    Based on the IDs in column A, I need the macro to place all of the data from each unique ID into it's own sheet, and name the sheet according to the ID.

    E.G., col A might have several "AAAA" as an ID, several "XYZ" as an ID...there's no specific order to where they appear in the rows, so it could be

    AAAA etc., etc.
    AAAA etc., etc.
    ZZZ, etc., etc.
    1-ABC etc. ,etc.
    AAAA
    XYZ
    XYZ
    1-ABC

    and, in this case I would need a sheet containing the info from AAAA (3 rows of data) rows, ZZZ rows (1 row of data), 1-ABC rows, and XYZ rows (2 rows) ect.?

    I hope this makes sense.

    I'm going to move all of the rows down 1, to insert the same header row as row 1 in each sheet.
    Each sheet will be sorted based on column B which is a date currently entered in mm/dd/yy format from the text file.

    I think I can do the other things I need to do as a result of Hans' help from a previous post. But, need this "macro" to be part of the one that creates the Excel workbook from the txt file.

    Phew.

    Thanks in advance.

    Kevin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See Post 471071. You'll have to adapt the code for your situation, of course.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I just hate feeling this stupid!

    From a previous reply of yours, Hans, I am using the following code to bring the txt file into Excel:

    Sub CreateBucket()
    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))
    Rows("1:1").Insert Shift:=xlDown
    ' Susbstitute your own headings!
    Range("A1") = "Bucket Name"
    Range("B1") = "Assigned Date"
    Range("C1") = "Claim Number"
    Range("D1") = "City"
    Range("E1") = "State"
    Range("F1") = "Last Name"
    Range("G1") = "Brand"
    Range("H1") = "Home Phone"
    Range("I1") = "Work Phone"
    Range("J1") = "Mobile Phone"

    Rows(1).Font.Bold = True
    ActiveSheet.UsedRange.Sort Key1:=Range("B1"), Header:=xlYes
    End Sub

    From the reference you made here, I'm not sure what to do or add to this macro to continue to resolve my problem.

    In the reference, I thought I should change this line: Set oSrc = Worksheets("PC Sales Indirect") to be the Excel name of the created file that now has the imported txt file. But, that bombed on me. So, I can't tell what to do from here.

    When I run the macro written above, the text file is imported and the first sheet now has the columns I needed from the text file, and the first row is the heading I need on ALL of the subsequent sheets. Some of the data looks like the following (I've tried to attach/upload a small file sample, but it isn't uploading for some reason).

    Bucket Name Assigned Date Claim Number City
    101-1 CALL / / 100164 STOCKTON
    135-RESCH / / 102197 GLENDALE
    101-1 CALL / / 104559 MESA
    101-1 CALL / / 104561 PHOENIX
    101-1 CALL / / 105035 HOUSTON
    120-HLDINF / / 105963 VACAVILLE
    100-NEW CL / / 106125 PHOENIX
    120-HLDINF 01/13/09 95419 HENDERSON
    101-1 CALL 01/21/09 96509 GLENDALE
    101-1 CALL 01/30/09 97655 SAN RAFAEL
    101-1 CALL 02/09/09 98514 MODESTO
    103-3 CALL 02/09/09 98587 TEMPE
    101-1 CALL 02/09/09 98701 STOCKTON


    Now I need 1 sheet named <101-1 Call> with all the data from Sheet 1 based on Column A for this "bucket name" from Col. A;
    another sheet named 135-RESCH, etc., etc.

    I keep tinkering w/the macro from the reference you gave Hans, but I manage to mess things up every single time.
    Clearly, I'm not fully understanding what I need to do.

    If you'd be kind enough to lend a bit more info/help, I'd be appreciative.

    Thanks.

    [quote name='HansV' post='774307' date='08-May-2009 09:19']See Post 471071. You'll have to adapt the code for your situation, of course.[/quote]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you want to attach a file, you have to click Browse... first and select the file, then click UPLOAD.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    That's what I did re the attachment. Going to try again. Just got the "uploding file..." on the screen and it never uploaded the very small Excel file.

    [quote name='HansV' post='774382' date='08-May-2009 17:39']If you want to attach a file, you have to click Browse... first and select the file, then click UPLOAD.[/quote]

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    That's what I did re the attachment. Going to try again. Just got the "uploding file..." on the screen and it never uploaded the very small Excel file.
    Can't seem to get it to upload the file.

    [quote name='HansV' post='774382' date='08-May-2009 17:39']If you want to attach a file, you have to click Browse... first and select the file, then click UPLOAD.[/quote]

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It does work, tens of files are uploaded to the Lounge every day, so I don't understand why it doesn't work for you.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Beats me...trying again in another browser.

    [quote name='HansV' post='774395' date='08-May-2009 18:37']It does work, tens of files are uploaded to the Lounge every day, so I don't understand why it doesn't work for you. [/quote]
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Here is a version of the Separate macro that you can use:

    Code:
    Public Sub Separate()
      Dim oSrc As Worksheet, oTgt As Worksheet
      Dim lTgtRow As Long
      Dim oCpyStart As Range, oCpyRange As Range, oNxtCell As Range
      Dim strName As String
      Application.ScreenUpdating = False
      Set oSrc = ActiveSheet
      strName = oSrc.Name
      oSrc.Range("A1").Sort Key1:=Range("A1"), _
      Order1:=xlAscending, Header:=xlYes
      Set oCpyStart = oSrc.Range("A2")
      For Each oTgt In Worksheets
    	If oTgt.Name <> strName Then
    	  oTgt.Cells.Clear
    	End If
      Next oTgt
      Do While oCpyStart.Value <> ""
    	Set oTgt = Nothing
    	Set oNxtCell = oCpyStart.Offset(1, 0)
    	Do While oCpyStart.Value = oNxtCell.Value
    	  Set oNxtCell = oNxtCell.Offset(1, 0)
    	Loop
    	Set oCpyRange = Range(oCpyStart, oNxtCell.Offset(-1, 10))
    	On Error Resume Next
    	Set oTgt = Worksheets(oCpyStart.Value)
    	On Error GoTo 0
    	If oTgt Is Nothing Then
    	  Set oTgt = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    	  oTgt.Name = oCpyStart.Value
    	End If
    	oSrc.Range("A1:J1").Copy Destination:=oTgt.Range("A1")
    	oCpyRange.Copy Destination:=oTgt.Range("A65536").End(xlUp).Offset(1, 0)
    	oTgt.Cells.EntireColumn.AutoFit
    	Set oCpyStart = oNxtCell
      Loop
      oSrc.Activate
      Application.ScreenUpdating = True
    End Sub
    To use it, simply add a line

    Call Separate

    immediately above the End Sub line of your CreateBucket macro.

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Thank you VERY much, Hans.
    I will look through this and apply it and test it...and, hopefully, learn from it. Clearly, it was non-trivial (at least for me).

    Kevin

    [quote name='HansV' post='774403' date='08-May-2009 19:14']Here is a version of the Separate macro that you can use:

    Code:
    Public Sub Separate()
      Dim oSrc As Worksheet, oTgt As Worksheet
      Dim lTgtRow As Long
      Dim oCpyStart As Range, oCpyRange As Range, oNxtCell As Range
      Dim strName As String
      Application.ScreenUpdating = False
      Set oSrc = ActiveSheet
      strName = oSrc.Name
      oSrc.Range("A1").Sort Key1:=Range("A1"), _
      Order1:=xlAscending, Header:=xlYes
      Set oCpyStart = oSrc.Range("A2")
      For Each oTgt In Worksheets
    	If oTgt.Name <> strName Then
    	  oTgt.Cells.Clear
    	End If
      Next oTgt
      Do While oCpyStart.Value <> ""
    	Set oTgt = Nothing
    	Set oNxtCell = oCpyStart.Offset(1, 0)
    	Do While oCpyStart.Value = oNxtCell.Value
    	  Set oNxtCell = oNxtCell.Offset(1, 0)
    	Loop
    	Set oCpyRange = Range(oCpyStart, oNxtCell.Offset(-1, 10))
    	On Error Resume Next
    	Set oTgt = Worksheets(oCpyStart.Value)
    	On Error GoTo 0
    	If oTgt Is Nothing Then
    	  Set oTgt = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    	  oTgt.Name = oCpyStart.Value
    	End If
    	oSrc.Range("A1:J1").Copy Destination:=oTgt.Range("A1")
    	oCpyRange.Copy Destination:=oTgt.Range("A65536").End(xlUp).Offset(1, 0)
    	oTgt.Cells.EntireColumn.AutoFit
    	Set oCpyStart = oNxtCell
      Loop
      oSrc.Activate
      Application.ScreenUpdating = True
    End Sub
    To use it, simply add a line

    Call Separate

    immediately above the End Sub line of your CreateBucket macro.[/quote]

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    For some reason the header name of col A is being created as a new worksheet also. I can't figure out why.

    [quote name='HansV' post='774403' date='08-May-2009 19:14']Here is a version of the Separate macro that you can use:

    Code:
    Public Sub Separate()
      Dim oSrc As Worksheet, oTgt As Worksheet
      Dim lTgtRow As Long
      Dim oCpyStart As Range, oCpyRange As Range, oNxtCell As Range
      Dim strName As String
      Application.ScreenUpdating = False
      Set oSrc = ActiveSheet
      strName = oSrc.Name
      oSrc.Range("A1").Sort Key1:=Range("A1"), _
      Order1:=xlAscending, Header:=xlYes
      Set oCpyStart = oSrc.Range("A2")
      For Each oTgt In Worksheets
    	If oTgt.Name <> strName Then
    	  oTgt.Cells.Clear
    	End If
      Next oTgt
      Do While oCpyStart.Value <> ""
    	Set oTgt = Nothing
    	Set oNxtCell = oCpyStart.Offset(1, 0)
    	Do While oCpyStart.Value = oNxtCell.Value
    	  Set oNxtCell = oNxtCell.Offset(1, 0)
    	Loop
    	Set oCpyRange = Range(oCpyStart, oNxtCell.Offset(-1, 10))
    	On Error Resume Next
    	Set oTgt = Worksheets(oCpyStart.Value)
    	On Error GoTo 0
    	If oTgt Is Nothing Then
    	  Set oTgt = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    	  oTgt.Name = oCpyStart.Value
    	End If
    	oSrc.Range("A1:J1").Copy Destination:=oTgt.Range("A1")
    	oCpyRange.Copy Destination:=oTgt.Range("A65536").End(xlUp).Offset(1, 0)
    	oTgt.Cells.EntireColumn.AutoFit
    	Set oCpyStart = oNxtCell
      Loop
      oSrc.Activate
      Application.ScreenUpdating = True
    End Sub
    To use it, simply add a line

    Call Separate

    immediately above the End Sub line of your CreateBucket macro.[/quote]

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='kweaver' post='774452' date='09-May-2009 19:46']For some reason the header name of col A is being created as a new worksheet also. I can't figure out why.[/quote]
    Does your text file contain column headers in the first line?

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    [quote name='HansV' post='774461' date='09-May-2009 16:14']Does your text file contain column headers in the first line?[/quote]
    It did, yes. Then I tried one without and had the same issue. I put the first row into the excel file before running your macro. I haven't tried it yet without my doing that but will shortly.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In a previous thread, you stated that the text file didn't contain column headers. Therefore, the CreateBucket macro inserts a row with column headers at your explicit request.
    If the text file now does contain column headers, or if you insert column headers yourself, you'll end up with two rows of column headers, and the Separate macro will treat the second row as a data row.

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Got it! Thanks again. Sorry to have been confusing. Your patience is appreciated.

    [quote name='HansV' post='774468' date='09-May-2009 16:11']In a previous thread, you stated that the text file didn't contain column headers. Therefore, the CreateBucket macro inserts a row with column headers at your explicit request.
    If the text file now does contain column headers, or if you insert column headers yourself, you'll end up with two rows of column headers, and the Separate macro will treat the second row as a data row.[/quote]

Posting Permissions

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