Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

Create a macro to compile data in multiple .txt files into one .xls file - Part 2 (extraction)

This question is related to
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27256398.html

[Reason: I have already allocated the points to what has been requested and this is a follow up-add-ons to what i initially requested.]


To extract the following text line that starts with "Z" and add this as a comment at the end of the column.

For example,  see attached.
Example-10.03.11--more-than-1--A.txt

Result: This text should be placed in the final column.
 Example-2.7.10--1--Add-ons-.xls

There will always be a comment.
The comment is in text format. Usually starts with "Z" else, can be any alphabet.
The text length can vary but will always be in 1 line.
0
ceneiqe
Asked:
ceneiqe
  • 2
  • 2
1 Solution
 
Michael FowlerSolutions ConsultantCommented:
I have added the requested functionality

Michael
Option Explicit

Sub getData()
   Dim FSO As New FileSystemObject
   Dim textFile As TextStream
   Dim textLine As String, folder As String, currentFile As String
   Dim currentRow As Long, count As Long
   Dim dateValue As Date
   Dim commentLine As Boolean

   ' Get file name from the user
   folder = getFolder
   If folder = "-1" Then Exit Sub
   
   'Set first file
   currentFile = Dir(folder & "\*.txt")
   
   Do While currentFile <> ""
   
      ' Get first empty row
      currentRow = Range("A" & Rows.count).End(xlUp).Row + 1
      
      Set textFile = FSO.OpenTextFile(currentFile)
      
      count = 0
      commentLine = False
      
      ' Loop through each line of text
      Do Until textFile.AtEndOfStream
         textLine = textFile.ReadLine
         If textLine <> "" Then
            If commentLine Then
               Range(COMMENT & currentRow).Value = Trim(textLine)
               currentRow = currentRow + 1
               commentLine = False
            ElseIf Left(textLine, 2) = "of" Then dateValue = CDate(Mid(textLine, 10, 11))
            ElseIf count = 1 And Left(textLine, 3) <> "---" Then
               Range(SA & currentRow).Value = Trim(Left(textLine, 3))
               Range(PACK & currentRow).Value = Trim(Mid(textLine, 4, 9))
               Range(ORDER & currentRow).Value = Trim(Mid(textLine, 14, 10))
               Range(LINE & currentRow).Value = Trim(Mid(textLine, 25, 5))
               Range(ITEM & currentRow).Value = Trim(Mid(textLine, 31, 25))
               Range(COL1 & currentRow).Value = Trim(Mid(textLine, 57, 2))
               Range(COL2 & currentRow).Value = Trim(Mid(textLine, 60, 3))
               Range(SASS & currentRow).Value = Trim(Mid(textLine, 64, 11))
               Range(REQ & currentRow).Value = Trim(Mid(textLine, 76, 3))
               Range(DEL & currentRow).Value = Trim(Mid(textLine, 80, 3))
               Range(DTE & currentRow).Value = dateValue
               commentLine = True
            ElseIf Left(textLine, 3) = "---" Then
               count = count + 1
               If count > 1 Then Exit Do
            End If
         End If
      Loop
      
      textFile.Close
      
      ' Get next file
      currentFile = Dir
   Loop
   
End Sub


Function getFolder() As String
   Dim fd As FileDialog
   Dim selection As Variant
   Dim fileName As String

   Set fd = Application.FileDialog(msoFileDialogFolderPicker)
   
   With fd
      .AllowMultiSelect = False
      If .Show = -1 Then
         getFolder = .SelectedItems(1)
      Else
         getFolder = "-1"
      End If
   End With

End Function

Open in new window

Example-2.7.10--1--Add-ons-.xls
0
 
ceneiqeAuthor Commented:
Why is it that there is an error when  i copy the revised macro code into a new module sheet in the old workbook "Example-2.7.10--1-(1).xls" ?

I rename as"Sub getData1()" instead of "Sub getData()"

The word Comment is highlighted in  :
"Range(Comment & currentRow).Value = Trim(textLine)"

and the error message

Compile Error: Variable Not defined

0
 
ceneiqeAuthor Commented:
Did i do anything wrong ?

Other than that, if i use the worksheet "Example-2.7.10--1--Add-ons-.xls " alone and run, it works ok.
0
 
Michael FowlerSolutions ConsultantCommented:
I have used constant values for my code. This means instead of refering to column "L" in the code eg
   Range("L"  & currentRow).Value = Trim(textLine)

I have declared that COMMENT is equal to "L" in the constants module ie

   Dim Const COMMENT as String = "L"

The value of doing this is that at a later date you can rearrage the output columns and instead of having to find every place in the code which refers to the columns you can just change the constant values and you are done.

Given this, to fix your problem please copy across the values in mdl_Constants into the old workbook

Michael
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now