VBA to open, rename, close files in directory

Guess I might be asking too much.
Let me be more specific.
Once I've defined my 'folder', how do I read the underlying files to a variable where they have the following in common: *.txt
This is so I can 'loop' through them.

Also - is there an easy way to capture the 'root' of the filename (ie without the .txt) so I can later save them as .xlsx files? (once I've converted to the right format, etc).

Any help would be much appreciated - I've searched around historic questions but can't find anything on the money.

Thanks

You could do something like this:-

Great post and I think i can use that in future. For what I'm working on now, I've used a different tact. It works well - but problem is that when it loops it goes back to the first file again.
I'm sure there's something very obvious here, but can't seem to spot it. Any ideas?

Code:
Sub Convert_txt_to_xlsx()
'
' Convert_txt_to_xlsx Macro

Dim strFolder As String
Dim strFile As String

strFolder = "H:\temp\txt_Files\"
strFile = strFolder & "*.txt"

Do While strFile <> ""
ChDir strFolder
Workbooks.OpenText Filename:= _
strFile, Origin:=xlMSDOS, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), _
Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array( _
29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), _
Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array( _
42, 1), Array(43, 1), Array(44, 1)), TrailingMinusNumbers:=True

Range(Selection, Selection.End(xlToRight)).Select

Selection.AutoFilter
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Cells.Select
Cells.EntireColumn.AutoFit

Range("A1").Select

ActiveWorkbook.SaveAs FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close

Loop

End Sub

If you're using Dir() to loop through the files in a folder you have to be careful not to modify the date/timestamp on the file otherwise Dir() gets fooled into thinking a new file has appeared and it will pick it up again.

Are you changing the date/timestamp of your 'original' files and picking them up again?

Or is Dir() picking up your newly-created files?

One solution would be to place the new files in a separate folder. You could always whizz though it at the end of the run and move the contents back to the original folder.

Is that any help?

I appreciate your help.
I've never really understood how Excel 'knows' to move onto the next item when using a 'Do While / Loop' and not sure why in one set of code it moves on, and in the other it keeps reverting to the starting point.

I'm not a natural coder and the For x= 1 to y, next x approach is more intuitive.

However, I combined your code with mine and hey presto, it works !!

Thanks again.
D

Good grief!

Thank You Ruddles!

I was looking for a loop to open files located in a directory and this worked perfectly for my needs. Went from a 40 minute process of clicking next a bunch of times to 5 minutes and one click.

Thanks again. The path I was on was much more complicated, unnecessarily so.