Hi Nimrod.
I am trying to open text files automatically.
Then modify the data and have it save as a text file in the name of the text in column A, row 2.
Then save it, and open the next file, e.t.c. There are over 1300 files.
The text files are actually Stockmarket ticker files in which i am trying to fill in the holes of non trading days ( most likely due to trading halts).
I can then import these files into my stockmarket share program and create more accurate artificial composites.
I have been working on the code that finds these holes by using dates. Then fills the holes with the previous rows data.
But i am having problems in trying to open a text file without knowing the stock ticker data file names.
The code below will open files without knowing the file names, by selecting the files from the source folder, when running this code, and then saves it in a new folder automatically.
I can not open text files, which are separated by commas, properly into the sheet.
Sub Open_Multiple_Files()
Const MyPath2 = "C:\Testing5\"
Dim Rng As Range
Dim fFName1 As String
Dim File_Names As Variant
Dim File_count As Integer
Dim Counter As Integer
Application.ScreenUpdating = False
File_Names = Application.GetOpenFilename _
("txt (*.txt*), *.txt*", , , , True)
File_count = UBound(File_Names)
Counter = 1
Do Until Counter > File_count
Workbooks.Open Filename:=File_Names(Counter)
Set Rng = Range("A1").CurrentRegion
FName2 = MyPath2 & Rng.Cells(2, 1).Text & "D.txt"
Rows("1:1").Select
Selection.Delete Shift:=xlUp
ActiveWorkbook.SaveAs Filename:=FName2, FileFormat:=xlText 'WorkbookNormal
ActiveWorkbook.Close Savechanges:=True
Counter = Counter + 1
Loop
Application.ScreenUpdating = True
End Sub
This other code below would probably be able to open a text file, by adding the code Kevin Philips supplied into the appropriate area of the code.
At the moment it only opens and saves excel files.
To get this macro to open and save files i need to have a list of the file names in colA sheet1.
I would prefer not to use a list of file names to perform this task, but if this is not possible the code below would do.
At the moment these codes only open a file, deletes the first row (as a test only) and saves the data in a new folder.
Another question i have is when these new data files are opening, it opens a new workbook, in the name of the data file it opened e.g. AAID,
If my original workbook where i run my macro, needs to copy a column of dates and paste it into the newly opened workbook, e.g.AAID, do i need to select and activate workbook 1 copy the column of data , then select and activate the newly opened workbook by its correct name, or as workbook 2 to paste this data into a column.
Sub SaveRows2()
Const MyPath = "C:\sourcefile\"
Const MyPath2 = "C:\destinationfile\"
Dim Rng As Range
Dim x As Long
Dim FName As String
Set Rng = Range("A1").CurrentRegion
Application.ScreenUpdating = False
For x = 2 To Rng.Rows.Count Step 1
FName = MyPath & Rng.Cells(x + 1, 1).Text & "D.xls"
FName2 = MyPath2 & Rng.Cells(x + 1, 1).Text & "D.xls"
Workbooks.Open FName
Rows("1:1").Select
Selection.Delete Shift:=xlUp
ActiveWorkbook.SaveAs Filename:=FName2, FileFormat:=xlWorkbookNormal
ActiveWorkbook.Close Savechanges:=True
Next x
Application.ScreenUpdating = True
End Sub
If the text data could actually be imported, by importing external data into worksheet1, then use the macro to fix the holes in the data, and save it as the name of column A row 2 would be much easier than working between workbooks.
Thanks
Mike.
