open a txt file, edit it, then save it as an xlsx, then repeat for 200 additional files in the same folder

[tacoguy]

The below code works great... for ONE file. But the files are from 1800.txt to 2000.txt. How do I insert this into a loop so I don't have to enter a filename? It just opens every txt file in that folder, parses it, then saves it as the same name but as an xlxs file?

Again, C:\temp\ contains 200 txt files. I need to import and delimit each one, then save it as the same name but as an xlsx instead. Ex. 1800.txt should become 1800.xlsx. The code below works for that one file. I'd have to edit it for every single file in that folder and wouldn't end up saving me a minute of time.

Thanks in advance!
--------------------------------------------------


Sub Import01()
'
' Import01 Macro
'
' Keyboard Shortcut: Ctrl+Shift+I
'

Workbooks.OpenText Filename:= _
"C:\temp\1800.txt", _
Origin:=xlWindows, StartRow:=475, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(4, 1), Array(32, 1), Array(44, 1), Array(54, 1)), _
TrailingMinusNumbers:=True
Range("A1").Select
Columns("E:E").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("A:A").EntireColumn.AutoFit
ActiveWorkbook.SaveAs Filename:= _
"C:\temp\1800.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

 

[alansidman]

Look at this link on how to loop through all files in a folder

https://www.thespreadsheetguru.com/...oop-through-all-excel-files-in-a-given-folder

 

[tacoguy]

Yeah. I have no idea how to insert my code into that other code on that page to make it work. I copied the code from the page and pasted it as a macro, which works. It changes the A1-Z1 to blue. I even changed the colors and it does indeed run against all xls files in the chosen folder. But I don't know how to make it open a txt file, import and parse the delimiters in it, and then save it as an xlsx file. The code I posted would need to go in the middle, I would guess, and I would guess it would replace the line that changes A1-Z1 blue. But I can't even get out of the macro without error. In fact, I can't even click off of the code that I paste into the file. As soon as I click off the code and click anywhere else in the macro, I get an error. I'm sure it could be done, I can only spend my free time trying to figure it out but I don't have enough free time. Thanks anyway! Cheers