Import and Rename Files

[rickey24]

Hi all

I have 2 main questions. I am basically repeating over and over agai
the below code for a bunch of different files. I am essentiall
creating a new workbook, and then opening up a bunch of files, copyin
them, and pasting each one onto a new sheet, so they are all in the ne
workbook. The names always stay the same but new files can be added.

Worksheets.Add(After:=ActiveSheet).Name = "East"
Workbooks.OpenText Filename:= _
"C:\Pen\Data\Worlds\zone_east.lst", _
Origin:=437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= xlDoubleQuote, ConsecutiveDelimiter:=True,
Tab:=True, Space:=True
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows(WorkAct).Activate
Selection.PasteSpecial Paste:=xlPasteValues
Cells.Select
Cells.EntireColumn.AutoFit
Windows("zone_east.lst").Close
Worksheets.Add(After:=ActiveSheet).Name = "West"
etc.

Now my questions, if all the .lst files are in the directory "Worlds
or subfolders of "Worlds" is there a way to find them withou
hardcoding all the names into the macro? Also, is there a way to nam
the sheet based on the file Name (ie. whatever is after "...zone_").
Essentially, so I don't have to go in and manually add file names an
sheets but the macro will automatically detect and name the sheet.
Thanks in advance.

Ricke

 

[MSP77079]

Try this. It's not EXACTLY what you are looking for, but should stee
you in the right direction.

With Application.FileSearch
.LookIn = ThisPath
If .LookIn <> ThisPath Then
'if the directory named in cell F1 does not exist, we wil
print
'an error message, then give user an opportunity to navigate t
the
'correct directory
MsgBox "Could not find the directory indicated"
ErrorCode = "bad file"
GoTo GetDirectoryPath
Else:
GoTo LookForFiles
End If
End With

LookForFiles:
'the next few lines of code will ensure that the workbook exists i
that directory

With Application.FileSearch
.LookIn = ThisPath 'directory path
.Filename = "*.xls"
.SearchSubFolders = False

If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
If BlankFormBook = .FoundFiles(i) Then GoT
CheckWorkSheets
Next i
Else
MsgBox "There were no files found in " & ThisFilePath
vbCritical
ErrorCode = "bad file"
GoTo GetDirectoryPath
End If
NumberOfFilesFound = .FoundFiles.Count
End Wit