Import text file

Originally Posted by norhaya

Hi

Below is my code, please review and comments, however, I am still not able to call up the part number and description. Any ideas?

Sub ImportText()

Dim i As Long, n As Long

Dim tmpString As String
Dim tmpAUC As String 'AUC = Average Unit Cost
Dim Fname As String
Dim tmpCAT As String 'CAT = Category
Dim tmpPICK As String ' PICK = Picking Sequence
Dim tmpUOM As String ' UOM = Unit of Measure
Dim tmpPN As String ' PN = Part Number
Dim tmpDesc As String ' Desc = Description
Dim tmpQOH As Variant ' QOH = Qty on Hand


Fname = "C:\Import\Accpac.txt"
Open Fname For Input As #1
Cells.Clear

Range("A1:I1").Value = Array("Part No", "Desc", "Date Last Shipment", "Date Last Receipt", "Average Unit Cost", "UOM", "Qty On Hand", "Location", "Remarks")
tmpPN1 = InStr(tmpString, "") & InStr(tmpString, " ") & InStr(tmpString, "category")
i = 2
n = 1
Do While Not EOF(1)
Line Input #1, tmpString
If (InStr(tmpString, "Category")) > 0 Then
tmpCAT = Right$(tmpString, 12)
Sheet1.Range("C" & i).Value = tmpCAT

ElseIf (InStr(tmpString, "Picking")) > 0 Then
tmpPICK = Right$(tmpString, 12)
Sheet1.Range("D" & i).Value = tmpPICK
ElseIf (InStr(tmpString, "Average unit cost")) > 0 Then
tmpUOM = Right$(tmpString, 16)
tmpAUC = Mid(tmpString, 44, 14)
Sheet1.Range("F" & i).Value = Trim(tmpUOM)
Sheet1.Range("E" & i).Value = tmpAUC

i = i + 1

End If

Loop
Close #1
Worksheets("Sheet1").Range("A1:I1").Columns.AutoFit

End Sub

Hi Bigmack

After going thru all the text, if I could join the line before line with "catergory" so it would be 1 string, then I could parse it easily. Can you guide/show me on the code? If this works then I can complete this task.

Appreciate your assistance.

Thanks, norhaya

Norhaya, please follow the rules--simply edit your post and use <code> tags otherwise I cannot even post a hint for you.