Hi!
At end is some code a friend of mine helped me with
He is on vacation and unavailable
What I'm trying to do
1. List of orders is in Excel Order_Nmbrs2! begin at A2
2. Open Order_Archive.txt
(Note: a csv file output from SQL server e-mail'd to me weekly
I append each new file to the archive)
3. Open Output-orders.txt
4. If order number read from xl exists in archive, write to output file
Continue until blank in ColA
5. An order may or may not contain multiple lines
Sample of archive (I import first 9 chars from each line into xl)
"Status"
"O",5997
"O",5997
"O",5997
"O",5997
"O",5997
"O",5997
"O",5997
I read this into xl (Continues for about 6000 rows today)
I then use some text formulas and some macros to clean
up and remove any duplicate items
6. An order # sequence may occur multiple times in the archive file
Each line shown above for order 5997 should be written out to
Output-orders.txt However, if encountered in the archive a second
time, should be skipped
7. Maybe the problem is that after I import from the archive to get
the first9 chars from each line, I strip and clean so I end up with
a 4 digit value I then use the value as the comparison against the archive
At the moment,nothing is being written out to Output-Orders.txt
The file is created, but it is empty.
Appreciate any help
Sub Write_Orders()
Dim datafile$
Dim a1 As String
Dim sRow As Long
Dim outputFile$
datafile$ = "C:\bug_fv\bug_fv-f.txt"
outputFile$ = "C:\bug_fv\output-orders.txt"
On Error GoTo DiskError
Open datafile$ For Input As #1
Open outputFile$ For Output As #2
sRow = 1
Worksheets("Order_Nmbrs2").Activate
With Range("A1")
Do While Not EOF(1)
Line Input #1, a1
x = Left(a1, 4)
If (x = .Offset(sRow, 0).Value) Then
Print #2, a1
Do While ((x = Left(a1, 4)) And Not EOF(1))
Line Input #1, a1
Print #2, a1
Loop
sRow = sRow + 1
End If
Loop
End With
Close #1
Close #2
Exit Sub
DiskError:
a = MsgBox("Disk Error." & Err.Number, vbExclamation)
End Sub
Display More
Thanks
marc
