PDA

View Full Version : Import 2 different text files and compare



jolivanes
12-01-2007, 12:29 PM
I am trying to import two different comma delimited text files into one sheet so I can compare the numbers in these files and find the mistakes made when these numbers were entered. These files are from a proprietory DOS file with a .ut file extension and are 3 columns wide. The entering was done by different people on different computers, that's why the comparison.
I found the code below but it won't let me choose files. It does not show the files with the .ut extension but it does import the file if there is only one file with this extension in the root directory and I click on OK.
The first file should be imported into columns A:C and the second file into columns E:F by choosing the respective file in the FileDialogPicker.
In columns I, J and K I want to compare columns A, B and C with the formula =A7-E7, =B7-F7 and =C7-G7. These formulas have to be copied down to the end of the entries in the opposing columns (A to G). See attached .xls file for what the result should be.
How would I go about this?



Function GetFolder() As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function


and



Sub test()
Dim myDir As String, fn As String, ff As Integer, txt As String, a()
Dim x, i As Long, n As Long, b(), t As Long
myDir = GetFolder()
fn = Dir(myDir & "\*.ut")
Do While fn <> ""
ff = FreeFile
Open myDir & "\" & fn For Input As #ff
Do While Not EOF(ff)
Line Input #ff, txt
x = Split(txt, ",")
n = n + 1
ReDim Preserve a(1 To n)
a(n) = x
Loop
Close #ff
With ThisWorkbook.Sheets(1)
.Cells(t + 1, 1).Value = fn
For i = 1 To n
.Cells(i + t + 1, 1).Resize(, UBound(a(i)) + 1).Value = a(i)
Next
End With
Erase a: t = t + n + 0: n = 0
fn = Dir()
Loop
End Sub

Bob Phillips
12-02-2007, 04:41 AM
Function CompareData() As String
Dim LAstRow As Long

With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.InitialFileName = "*.ut"
.Show

Workbooks.Open .SelectedItems(1)
ActiveWorkbook.Worksheets(1).Columns("A:C").Copy _
ThisWorkbook.Worksheets(1).Range("A1")
ActiveWorkbook.Close savechanges:=False
Columns("A:A").TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
Semicolon:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1))

Workbooks.Open .SelectedItems(2)
ActiveWorkbook.Worksheets(1).Columns("A:C").Copy _
ThisWorkbook.Worksheets(1).Range("E1")
ActiveWorkbook.Close savechanges:=False
Columns("E:E").TextToColumns _
Destination:=Range("E1"), _
DataType:=xlDelimited, _
Semicolon:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1))
End With

With ActiveSheet
LAstRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("I7").Formula = "=IF(A7-E7=0,"""",A7-E7)"
.Range("I7").AutoFill .Range("I7").Resize(1, 3)
.Range("I7:K7").AutoFill .Range("I7").Resize(LAstRow - 6, 3)
End With

NextCode:
End Function

jolivanes
12-02-2007, 05:10 PM
Thanks Bob.
Works like a charm

John