Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulate textfile with VBA
Hello
I need help for the following VBA-problem: I have to insert a Value in each Line of one Textfile (file.txt). This certain Value depends on a other Value in this Line of the Textfile. The depending Value is always at the same position, The textfile is delimited with ";" A remark, it is possible, that the Textfile has more than 65thousend rows. Example, Textfile: depends on FIN_PERIOD "Time Dimension";"FIN_PERIOD";"VIEW";"REFERENCE_PERIOD"; "Close Year 2006";"Year 2006";"FSA_MV";"20060630" "Close Year 2004";"Year 2004";"FSA_MV";"20040331" "Close Year 2005";"Year 2005";"FSA_MV";"20050930" Depending Table, Mapping Table, this is a Worksheet in the Excel File: FIN_PERIOD ACC Year 2004 100 Year 2005 500 Year 2006 1000 === Result: [this should be done with VBA] Modified Textfile: "ACC"; "Time Dimension";"FIN_PERIOD";"VIEW";"REFERENCE_PERIOD"; "1000"; "Close Year 2006";"Year 2006";"FSA_MV";"20060630" "100"; "Close Year 2004";"Year 2004";"FSA_MV";"20040331" "500"; "Close Year 2005";"Year 2005";"FSA_MV";"20050930" The Inserted New Attribut is "ACC" an the Value is 1000, 100 & 500 Can anyone help me ? I tried, but no success.... i had big problems in replacing the lines in the textfile... How can you programming an "vlookup" in VBA code between the certain position in the textfile and the depending table / mapping table ? Kind regards, Roman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulate textfile with VBA
It may be simpler to just parse the file in VBA and not use a worksheet at
all. Try the following subroutine on your text file. I wrote it specifially to your requirements, so it retains all the quotes (") in your samples. This is just ad-hoc, but you can fairly easily modify it to be more dynamic if you plan on doing this on a regular basis, or if the requirements change frequently. Private Sub ImportParseText() Dim fileNum1 As Integer 'file number for reading file Dim fileNum2 As Integer 'file number for writing file Dim inFile As String 'filename to parse Dim outFile As String 'filename to write output to Dim readLine As String 'holding variable to import lines from file inFile = Application.GetOpenFilename _ ("Text File to parse (*.txt), *.txt, all files (*.*),*.*", , "Import Text File") If inFile = "False" Then MsgBox "Text File Import Cancelled" Exit Sub End If outFile = Application.GetSaveAsFilename( _ InitialFileName:="", _ FileFilter:="Text Files (*.txt), *.txt", _ Title:="Output File Name") If outFile = "False" Then MsgBox "Text File Import Cancelled" Exit Sub End If fileNum1 = FreeFile() Open inFile For Input As #fileNum1 fileNum2 = FreeFile() Open outFile For Output As #fileNum2 Print #fileNum2, """ACC"";""Time Dimension"";""FIN_PERIOD"";""VIEW"";""REFERENCE_PE RIOD"";" Do While Not EOF(fileNum1) Line Input #fileNum1, readLine If readLine < "" Then If InStr(1, readLine, "Year 2004") Then readLine = """100"";" & readLine Print #fileNum2, readLine End If If InStr(1, readLine, "Year 2005") Then readLine = """500"";" & readLine Print #fileNum2, readLine End If If InStr(1, readLine, "Year 2006") Then readLine = """1000"";" & readLine Print #fileNum2, readLine End If End If Loop Close #fileNum1 Close #fileNum2 End Sub -- Les Torchia-Wells "Romanoff" wrote: Hello I need help for the following VBA-problem: I have to insert a Value in each Line of one Textfile (file.txt). This certain Value depends on a other Value in this Line of the Textfile. The depending Value is always at the same position, The textfile is delimited with ";" A remark, it is possible, that the Textfile has more than 65thousend rows. Example, Textfile: depends on FIN_PERIOD "Time Dimension";"FIN_PERIOD";"VIEW";"REFERENCE_PERIOD"; "Close Year 2006";"Year 2006";"FSA_MV";"20060630" "Close Year 2004";"Year 2004";"FSA_MV";"20040331" "Close Year 2005";"Year 2005";"FSA_MV";"20050930" Depending Table, Mapping Table, this is a Worksheet in the Excel File: FIN_PERIOD ACC Year 2004 100 Year 2005 500 Year 2006 1000 === Result: [this should be done with VBA] Modified Textfile: "ACC"; "Time Dimension";"FIN_PERIOD";"VIEW";"REFERENCE_PERIOD"; "1000"; "Close Year 2006";"Year 2006";"FSA_MV";"20060630" "100"; "Close Year 2004";"Year 2004";"FSA_MV";"20040331" "500"; "Close Year 2005";"Year 2005";"FSA_MV";"20050930" The Inserted New Attribut is "ACC" an the Value is 1000, 100 & 500 Can anyone help me ? I tried, but no success.... i had big problems in replacing the lines in the textfile... How can you programming an "vlookup" in VBA code between the certain position in the textfile and the depending table / mapping table ? Kind regards, Roman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulate textfile with VBA
There's various ways of doing this, with the emphasis either on Excel or
VBA. Your mention of 65K rows tips my solution towards VBA (assuming you are not targetting XL2007 only). With a file of ~ 100K rows, this completes in about 10secs. You can play with the way the file read/write (although disk access is the slow part) and the manner in which the look up and reassignment is done if you need to improve performance, but you get the idea. Your should add error handling etc. Assuming you have a table like this in A1:B4: FIN_PERIOD ACC "Year 2004" "100"; "Year 2005" "500"; "Year 2006" "1000"; You can call the function Private Sub CommandButton1_Click() Dim RetVal As Long RetVal = UpdateFile("C:\Test2.txt", "C:\Test2-Update.txt", Range("A2:B4")) End Sub Public Function UpdateFile(InFilePathName As String, OutFilePathName As String, LookupRange As Range) As Long Dim FinPeriodLookUp As Variant Dim FileNum As Long Dim Temp() As String Dim i As Long Set FinPeriodLookUp = LookupRange FileNum = FreeFile Open InFilePathName For Input As #FileNum Temp = Split(Input(LOF(FileNum), #FileNum), vbNewLine) Close #FileNum For i = LBound(Temp) To UBound(Temp) Temp(i) = Application.WorksheetFunction.VLookup(Split(Temp(i ), ";")(1), FinPeriodLookUp, 2, False) & Temp(i) Next FileNum = FreeFile Open OutFilePathName For Output As #FileNum Print #FileNum, Join(Temp, vbNewLine) Close #FileNum UpdateFile = i End Function NickHK "Romanoff" wrote in message oups.com... Hello I need help for the following VBA-problem: I have to insert a Value in each Line of one Textfile (file.txt). This certain Value depends on a other Value in this Line of the Textfile. The depending Value is always at the same position, The textfile is delimited with ";" A remark, it is possible, that the Textfile has more than 65thousend rows. Example, Textfile: depends on FIN_PERIOD "Time Dimension";"FIN_PERIOD";"VIEW";"REFERENCE_PERIOD"; "Close Year 2006";"Year 2006";"FSA_MV";"20060630" "Close Year 2004";"Year 2004";"FSA_MV";"20040331" "Close Year 2005";"Year 2005";"FSA_MV";"20050930" Depending Table, Mapping Table, this is a Worksheet in the Excel File: FIN_PERIOD ACC Year 2004 100 Year 2005 500 Year 2006 1000 === Result: [this should be done with VBA] Modified Textfile: "ACC"; "Time Dimension";"FIN_PERIOD";"VIEW";"REFERENCE_PERIOD"; "1000"; "Close Year 2006";"Year 2006";"FSA_MV";"20060630" "100"; "Close Year 2004";"Year 2004";"FSA_MV";"20040331" "500"; "Close Year 2005";"Year 2005";"FSA_MV";"20050930" The Inserted New Attribut is "ACC" an the Value is 1000, 100 & 500 Can anyone help me ? I tried, but no success.... i had big problems in replacing the lines in the textfile... How can you programming an "vlookup" in VBA code between the certain position in the textfile and the depending table / mapping table ? Kind regards, Roman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export as Textfile with VBA | Excel Programming | |||
Move to end of textfile | Excel Programming | |||
How do i save as a textfile in a macro? | Excel Programming | |||
SQL-Query from a textfile? | Excel Programming | |||
Textfile-problem | Excel Programming |