Hi, I am having trouble to loop thru a folder of txt and xlsx files; select the txt files (one by one), convert them to xlsx file (and maintaining the original txt file); save it in same path and with same filename (as when it was txt file, but .xlsx); Note:- must maintain the original txt file); In xlsx file convert text to columns (separator is a space)(applies to column 1); I then run some other calculations and data manipulation (macros), save it; then select next txt file in folder and repeat.
Your assistance will be appreciated.
My code is attached below:-
Sub Test24()
' Create a list of all files in the folder (create an excel list)
' 1st create the excel file
Workbooks.Add
ActiveWorkbook.SaveAs ("C:\Users\Trevor\Documents\Testing\1998\Summary File List.xlsx")
' FileList.Title = "Summary_File_List"
' FileList.SaveAs = ("Summary_File_List.xlsx")
' 2nd create the summary list in the new file
F = Dir("C:\Users\Trevor\Documents\Testing\1998\*.txt")
Do While Len(F) > 0
ActiveCell.Formula = F
ActiveCell.Offset(1, 0).Select
F = Dir()
Loop
ActiveWorkbook.Save
' Now use this summary list to loop through
'Loop through the files in the selected folder
Dim FF As String
FF = Dir("C:\Users\Trevor\Documents\Testing\1998\*.txt")
Do While FF <> ""
' Opens txt file in excel
Workbooks.Open ("C:\Users\Trevor\Documents\Testing\1998\" & FF)
' Saves file as xlsx
ActiveWorkbook.SaveAs "C:\Users\Trevor\Documents\Testing\1998\" & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4), FileFormat:=52
TextDataFile = ActiveWorkbook.Name
' Do not display the message about overwriting the existing file.
Application.DisplayAlerts = False
' Save the active workbook with the name of the active workbook.
ActiveWorkbook.SaveAs Filename:="C:\Users\Trevor\Documents\Testing\1998" & TextDataFile
' Close the workbook by using the following.
ActiveWorkbook.Close
Loop
End Sub
Your assistance will be appreciated.
My code is attached below:-
Sub Test24()
' Create a list of all files in the folder (create an excel list)
' 1st create the excel file
Workbooks.Add
ActiveWorkbook.SaveAs ("C:\Users\Trevor\Documents\Testing\1998\Summary File List.xlsx")
' FileList.Title = "Summary_File_List"
' FileList.SaveAs = ("Summary_File_List.xlsx")
' 2nd create the summary list in the new file
F = Dir("C:\Users\Trevor\Documents\Testing\1998\*.txt")
Do While Len(F) > 0
ActiveCell.Formula = F
ActiveCell.Offset(1, 0).Select
F = Dir()
Loop
ActiveWorkbook.Save
' Now use this summary list to loop through
'Loop through the files in the selected folder
Dim FF As String
FF = Dir("C:\Users\Trevor\Documents\Testing\1998\*.txt")
Do While FF <> ""
' Opens txt file in excel
Workbooks.Open ("C:\Users\Trevor\Documents\Testing\1998\" & FF)
' Saves file as xlsx
ActiveWorkbook.SaveAs "C:\Users\Trevor\Documents\Testing\1998\" & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4), FileFormat:=52
TextDataFile = ActiveWorkbook.Name
' Do not display the message about overwriting the existing file.
Application.DisplayAlerts = False
' Save the active workbook with the name of the active workbook.
ActiveWorkbook.SaveAs Filename:="C:\Users\Trevor\Documents\Testing\1998" & TextDataFile
' Close the workbook by using the following.
ActiveWorkbook.Close
Loop
End Sub