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

tacoguy

New Member
Joined
Oct 12, 2018
Messages
2
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top