Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
open text file, get file from directory, update file
I have a text file. Lots of rows. The second field is a date yymmdd. It looks
like: ABC, 091012, 1.47, 1.51, 1.46, 1.495, 586907 XYZ, 091012, .... etc... I want to take the value in the first field, in this case "ABC", open a file called ABC_XXX.csv. I don't care what XXX is, as long as the ABC part matches. Format of the csv file is: ABC, 10-Aug-99, 0.9734, 0.9567, etc. ABC, 11-Aug-99, etc.. etc... I want to append the row from the text file to the .csv file. So that the ABC_XXX.csv file has the row: ABC, 12-Oct-09, 1.47, 1.51, 1.46, 1.495, 586907 added. Date reformatted from 091012 to dd-mmm-yy. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
open text file, get file from directory, update file
Hi Diana
This should do what you want to do. It assumes you are wanting to use the first field in the current file as the criteria for the CSV file to open. Take care Marcus Option Explicit Sub OpenCSVFile() Dim sPath As String Dim sFil As String Dim strName As String Dim myRow As Integer Dim PName As String Rows(1).Copy PName = Left(Cells(1, 1).Value, 3) ' Column 1 for the csv criteria sPath = "C:\" ' Change to suit sFil = Dir(sPath & PName & "*.csv") Do While sFil < "" strName = sPath & sFil Workbooks.Open (strName) sFil = Dir Loop 'Pastes to the last row in the CSV file myRow = Range("A" & Rows.Count).End(xlUp).Row + 1 Cells(myRow, 1).PasteSpecial xlValues Cells(myRow, 2).NumberFormat = "dd-mmm-yy" End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
open text file, get file from directory, update file
If the .csv files are present then you can ignore the second loop...
Sub Macro() Dim strTextFile As String, strCSVFile As String Dim intTextFile As Integer, intCSVFile As Integer Dim strData As String, arrData As Variant Dim strCSVPath As String, intFile As Integer strTextFile = "d:\donna.txt" strCSVPath = "d:" If Right(strCSVPath, 1) < "\" Then strCSVPath = strCSVPath & "\" intTextFile = FreeFile Open strTextFile For Input As #intTextFile Do While Not EOF(intTextFile) Line Input #intTextFile, strData arrData = Split(strData, ",") arrData(1) = Right(Trim(arrData(1)), 2) & "-" & Format("13-" & _ Mid(Trim(arrData(1)), 3, 2) & "-2009", "MMM") & "-20" & Left(Trim(arrData(1)), 2) strCSVFile = Dir(strCSVPath & arrData(0) & "*.csv", vbNormal) intCSVFile = FreeFile Open strCSVPath & strCSVFile For Append As #intCSVFile Print #intCSVFile, Join(arrData, ",") Close #intCSVFile Loop Close #intTextFile End Sub -- Jacob "Jacob Skaria" wrote: Diana Try the below Sub Macro() Dim strTextFile As String, strCSVFile As String Dim intTextFile As Integer, intCSVFile As Integer Dim strData As String, arrData As Variant Dim strCSVPath As String, intFile As Integer strTextFile = "d:\donna.txt" strCSVPath = "d:" If Right(strCSVPath, 1) < "\" Then strCSVPath = strCSVPath & "\" intTextFile = FreeFile Open strTextFile For Input As #intTextFile Do While Not EOF(intTextFile) Line Input #intTextFile, strData arrData = Split(strData, ",") arrData(1) = Right(Trim(arrData(1)), 2) & "-" & Format("13-" & _ Mid(Trim(arrData(1)), 3, 2) & "-2009", "MMM") & "-20" & Left(Trim(arrData(1)), 2) strCSVFile = Dir(strCSVPath & "*.csv", vbNormal) Do While strCSVFile < "" If UCase(strCSVFile) Like UCase(arrData(0)) & "_*.CSV" Then intCSVFile = FreeFile Open strCSVPath & strCSVFile For Append As #intCSVFile Print #intCSVFile, Join(arrData, ",") Close #intCSVFile Exit Do End If strCSVFile = Dir Loop Loop Close #intTextFile End Sub -- Jacob "Diana" wrote: I have a text file. Lots of rows. The second field is a date yymmdd. It looks like: ABC, 091012, 1.47, 1.51, 1.46, 1.495, 586907 XYZ, 091012, .... etc... I want to take the value in the first field, in this case "ABC", open a file called ABC_XXX.csv. I don't care what XXX is, as long as the ABC part matches. Format of the csv file is: ABC, 10-Aug-99, 0.9734, 0.9567, etc. ABC, 11-Aug-99, etc.. etc... I want to append the row from the text file to the .csv file. So that the ABC_XXX.csv file has the row: ABC, 12-Oct-09, 1.47, 1.51, 1.46, 1.495, 586907 added. Date reformatted from 091012 to dd-mmm-yy. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open CSV file, format data and write output to a text file. | Excel Programming | |||
How do I import text file, analyze data, export results, open next file | Excel Programming | |||
Combine all text file in directory into one file. | Excel Programming | |||
Open delimited text file to excel without changing data in that file | Excel Programming | |||
Automate open file, update links, run macro, close and save file | Excel Programming |