inport txt file automatically
 

Account Number Name 1 Property Address 1 wk1 wk2
1000192872 Mrs Sarah McLaren 1 Aitken Lane

I have an excel spreadsheet of above with 5000 account numbers in column A:
each week I have to import a txt file with some of these account numbers and
the amount paid in wk1 wk2 wk3 etc.
Can anyone show me how to do this using excel vba. I've got some knowledge
but not enough to do this by myself.
Thanks all.

inport txt file automatically
 

Hi,

Just to get you started:

Sub Effe()
Dim iFno As Integer
Dim sFName As Variant
Dim sLine As String, sAccntNo As String
Dim vRow As Variant

vFName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If vFName = False Then Exit Sub

iFno = FreeFile()
Open vFName For Input As #iFno

Do While Not EOF(iFno)
Line Input #iFno, sLine
' account number is 1st 10 characters?
sAccntNo = Left$(sLine, 10)
' loop up the account number in the 1st column
vRow = Application.Match(sLine, ActiveSheet.Columns(1), 0)
If IsError(vRow) Then
MsgBox "Account " & sAccntNo & " does not exist", vbExclamation
Else
' week1 in column 4, week1's amount in input line at pos 22-26
ActiveSheet.Cells(vRow, 4) = CDbl(Mid$(sLine, 22, 5))
End If
Loop
Close #iFno
End Sub

With kind regards,
Ton Teun

--
Ton
-----------------------------------------------------------------------
TonT's Profile: http://www.officehelp.in/member.php?userid=4
View this thread: http://www.officehelp.in/showthread.php?t=66412
Visit - http://www.officehelp.in/archive/index.php | http://www.officehelp.in/index/index.ph