Loop through range and open files

[Guest]

Hello
I have a list of *.txt files in A1:F1. I have already written code to
define the path where the file is located (myPath). I need to open each file
and perform some formatting and then copy to another file. How do I loop
through the files using the content of the range?
Thanks!

 

[Bernie Deitrick]

Sandy,

Sub TryNow()
Dim myCell As Range
Dim myPath As String
myPath = "C:\Excel\"
For Each myCell In Range("A1:F1")
Workbooks.OpenText Filename:=myPath & myCell.Value
'Perform formatting
'Copy to other file
'If you want to save the file as a workbook, uncomment this
'ActiveWorkbook.SaveAs myPath & "NewFileName.xls", xlDefault
ActiveWorkbook.Close False
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

dim myCell as range
dim myRng as range

set myrng = worksheets("sheet999").range("a1:F1")

for each mycell in myrng.cells
'do your work
msgbox mycell.value
next mycell