Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel to inport data | Excel Worksheet Functions | |||
Export/Inport Macros | Excel Discussion (Misc queries) | |||
Inport Outlook emails into Excel | Excel Discussion (Misc queries) | |||
Inport from a variable file name | Excel Discussion (Misc queries) | |||
Inport of Microsoft Works Spreadsheets | Excel Discussion (Misc queries) |