Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data from old to new (excel2000+)

    Tried on my own but have given up!
    I have 2 very large files(5mega) see small attachment.
    Attachment contains 2 sheets which represent the 2 text files
    I want to search thru file 45after.txt for a cell which starts with <UGOCC_PARTNAME> if
    the cell above "this found cell" starts with <UGOCC_JTFILE> continue search -if the
    above cell does not contain <UGOCC_JTFILE> then then I want to search file

    "45before.txt" for the exact same cell from beginning to end and if a match is found

    and if cell directly above match contains <UGOCC_JTFILE> then I want to copy and insert

    the whole <UGOCC_JTFILE> cell above the cell I used as a search in file "45after.txt".

    Multiple occurrences in both files need to be checked!!
    To put it in a nutshell cell# 3 of file 45before.txt should be inserted in file
    45after.txt before cell# 4.
    cell# 11 of file 45before.txt should be inserted in file 45after.txt before cell# 11
    cell # 27 in 45after.txt shall not be changed!!!!
    Hope I have made myself clear. A third sheet containing all required data is also ok. .
    Thanx
    Smbs

  2. #2
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data from old to new (excel2000+)

    Thanx HansV ---but there appears to be something wrong with the lines I get
    error here -smething is missing due to copying --could u send an attachment??
    wshAfter.Cells(lngRowAfter, 1).Insert _???
    ' Copy from 45before to 45after????
    Shift:=xlShiftDown
    thanx
    Smbs

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Data from old to new (excel2000+)

    Edited by HansV to correct placement of comment

    Here is a macro that uses multiple loops. It could be mad more efficient by using the Find method.
    <code>
    Sub ProcessFiles()
    Const strPartname = "<UGOCC_PARTNAME>"
    Const strFile = "<UGOCC_JTFILE>"

    Dim wshBefore As Worksheet
    Dim wshAfter As Worksheet
    Dim lngRowBefore As Long
    Dim lngRowAfter As Long
    Dim rngCell As Range

    Set wshBefore = Worksheets("45before")
    Set wshAfter = Worksheets("45after")

    ' Loop through cells of 45after
    lngRowAfter = 2
    Do While wshAfter.Cells(lngRowAfter, 1) <> ""
    ' Test if partname in cell
    If Left(wshAfter.Cells(lngRowAfter, 1), _
    Len(strPartname)) = strPartname Then
    ' Test if not filename in cell above
    If Left(wshAfter.Cells(lngRowAfter - 1, 1), _
    Len(strFile)) <> strFile Then
    ' Loop through cells of 45before
    lngRowBefore = 2
    Do While wshBefore.Cells(lngRowBefore, 1) <> ""
    ' Test if partname matches
    If wshBefore.Cells(lngRowBefore, 1) = _
    wshAfter.Cells(lngRowAfter, 1) Then
    ' Test if filename in cell above
    If Left(wshBefore.Cells(lngRowBefore - 1, 1), _
    Len(strFile)) = strFile Then
    ' Insert cell
    wshAfter.Cells(lngRowAfter, 1).Insert _
    Shift:=xlShiftDown
    ' Copy from 45before to 45after
    wshBefore.Cells(lngRowBefore - 1, 1).Copy _
    Destination:=wshAfter.Cells(lngRowAfter, 1)
    ' Adjust row number
    lngRowAfter = lngRowAfter + 1
    Exit Do
    End If
    End If
    lngRowBefore = lngRowBefore + 1
    Loop
    End If
    End If
    lngRowAfter = lngRowAfter + 1
    Loop

    Set wshAfter = Nothing
    Set wshBefore = Nothing
    End Sub</code>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Data from old to new (excel2000+)

    I'm sorry, I inserted a comment in the wrong place afterwards. I have corrected my previous reply.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data from old to new (excel2000+)

    Thanx I figured it out just before your reply--- it works just fine -however I dont have the large files here at home and due to large file size looping may take hours!!
    Thanx will let you know next week
    Smbs

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Data from old to new (excel2000+)

    Here is a version that uses Find.

    <code>Sub ProcessFiles()
    Const strPartname = "<UGOCC_PARTNAME>"
    Const strFile = "<UGOCC_JTFILE>"

    Dim wshBefore As Worksheet
    Dim wshAfter As Worksheet
    Dim lngPrevRow As Long
    Dim lngCurrRow As Long
    Dim rngAfter As Range
    Dim rngBefore As Range

    Set wshBefore = Worksheets("45before")
    Set wshAfter = Worksheets("45after")

    Set rngAfter = wshAfter.Cells(1, 1)
    lngPrevRow = 1
    Set rngAfter = wshAfter.Range("A:A").Find(What:=strPartname, After:=rngAfter)

    Do While Not (rngAfter Is Nothing)
    lngPrevRow = lngCurrRow
    lngCurrRow = rngAfter.Row
    If lngCurrRow < lngPrevRow Then
    Exit Do
    End If
    If Left(rngAfter.Offset(-1, 0), Len(strFile)) <> strFile Then
    Set rngBefore = wshBefore.Range("A:A").Find(What:=rngAfter.Value)
    If Not (rngBefore Is Nothing) Then
    If Left(rngBefore.Offset(-1, 0), Len(strFile)) = strFile Then
    rngAfter.Insert Shift:=xlShiftDown
    rngBefore.Offset(-1, 0).Copy Destination:=rngAfter.Offset(-1, 0)
    End If
    End If
    End If
    Set rngAfter = wshAfter.Range("A:A").Find(What:=strPartname, After:=rngAfter)
    Loop

    Set rngAfter = Nothing
    Set rngBefore = Nothing
    Set wshAfter = Nothing
    Set wshBefore = Nothing
    End Sub</code>

  7. #7
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data from old to new (excel2000+)

    Thanx once again HansV --- but now I have a new problem I want to import both txt files into an new workbook-- sheet 1 being named 45before.txt(nameof chosen file) and sheet 2 being named 45after.txt. I know how to use GetOpenFilename method (multiselect) but when I choose filenames from dir listing each file is opened in a different workbook instead of different sheets in same workbook. How do I do this??

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Data from old to new (excel2000+)

    You will have to open the text files in separate windows. After opening one, you can transfer its worksheet to the workbook running the code:
    <code>
    Workbooks.OpenText ...

    ActiveWorkbook.Sheets(1).Move _
    After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt)</code>

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Data from old to new (excel2000+)

    Before calling the getopenfilename method, create (or set) the workbook to add the textfiles as worksheets
    When looping thru the array of names obtained from the getopenfilename method,
    Open the file (as a new workbook)
    then move the worksheet to the "desired workbook"
    get the next filename

    Moving the only sheet in a workbook will "close" that workbook object. so afterwards you don't have all the open workbooks, they are all part of the other.

    Steve

  10. #10
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data from old to new (excel2000+)

    Thanx again to both of you and A Happy Christmas to all--
    Steve I'am afraid I don't quite understand what you are getting at--maybe a bit of code woud help
    Thanx
    Smbs

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Data from old to new (excel2000+)

    And a Happy Christmas to you.

    My proposal is essentially the same as Hans' proposal. Try this (or modify as needed)
    The first file is opened, copied to a new workbook file and the text file deleted. All the others are opened and moved to the one that was created. The workbook is not saved in the code (those this could be added if desired)

    If you start with an existing file you can bypass the intial code with the first file and just let the loop handle it.

    Steve

    <pre>Option Explicit
    Sub CombineTextFiles()
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Dim FilesToOpen
    Dim x As Integer
    Dim wkbAll As Workbook
    Dim wkbTemp As Workbook

    FilesToOpen = Application.GetOpenFilename _
    (FileFilter:="Text Files (*.txt), *.txt", _
    MultiSelect:=True, Title:="Text Files to Open")

    If TypeName(FilesToOpen) = "Boolean" Then
    MsgBox "No Files were selected"
    GoTo ExitHandler
    End If

    x = 1
    Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
    wkbTemp.Sheets(1).Copy
    Set wkbAll = ActiveWorkbook
    wkbTemp.Close (False)
    x = x + 1
    While x <= UBound(FilesToOpen)
    Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
    With wkbAll
    wkbTemp.Sheets(1).Move _
    After:=.Sheets(.Sheets.Count)
    End With
    x = x + 1
    Wend

    ExitHandler:
    Application.ScreenUpdating = True
    Set wkbAll = Nothing
    Set wkbTemp = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
    End Sub</pre>


  12. #12
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data from old to new (excel2000+)

    Many Thanx once again "Season's Greetings"
    Smbs

Posting Permissions

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