importing text files
 

I have text files located on my I: drive, I would like to bring up an
input box where I could type in up to 10 filenames.
example:
O0168.txt
O0021.txt
O0081.txt

and input the text in a single column, I will then do some sorting and
removing data.

What is the best method of doing this?

Regards
Mark



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

importing text files
 

I've never been a very good typist, but I can point and click with the best of
them (er, maybe).

When you do File|Open in excel, you can click on one file and ctrl-click on
subsequent files. Each will open.

You can do that in a macro, too.

Option Explicit
Sub testme03()

Dim InFileNames As Variant
Dim wks As Worksheet
Dim newWks As Worksheet
Dim destCell As Range

Dim fCtr As Long

Set newWks = Workbooks.Add(1).Worksheets(1)
Set destCell = newWks.Range("A1")

InFileNames = Application.GetOpenFilename _
(FileFilter:="Text Files, *.txt", MultiSelect:=True)

If IsArray(InFileNames) Then

Application.ScreenUpdating = False
For fCtr = LBound(InFileNames) To UBound(InFileNames)

Application.StatusBar = "Processing: " _
& InFileNames(fCtr) & "--" & Now

Workbooks.OpenText Filename:=InFileNames(fCtr), Origin:=437, _
StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=False

Set wks = ActiveSheet
With wks
.Range("a1", .Cells(.Rows.Count, "A").End(xlUp)).Copy _
Destination:=destCell
End With

With newWks
Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

wks.Parent.Close savechanges:=False

Next fCtr

With Application
.StatusBar = False
.ScreenUpdating = True
End With
End If

End Sub


msweeney wrote:

I have text files located on my I: drive, I would like to bring up an
input box where I could type in up to 10 filenames.
example:
O0168.txt
O0021.txt
O0081.txt

and input the text in a single column, I will then do some sorting and
removing data.

What is the best method of doing this?

Regards
Mark

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

--

Dave Peterson

importing text files
 

I tried your macro and it crashed, I'll try to debug it.

I manually selected the files as you noted and the issue there is they
are all in seperate files, that would work if I could append one file
to the next.

Also I would like to select the files by entering in the file
number/name into a list that comes up as a text box for input.

Thanks for the information, if you can help futher that would be
great.

Regards,
Mark



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

importing text files
 

You could use inputbox to get all the filenames. But then you'd have to parse
them out and verify that they existed.

Where did the macro crash?

And do you mean that the files are all in separate folders? If yes, then I
would think that would make getting the names via a textbox or an inputbox even
more troublesome.



msweeney wrote:

I tried your macro and it crashed, I'll try to debug it.

I manually selected the files as you noted and the issue there is they
are all in seperate files, that would work if I could append one file
to the next.

Also I would like to select the files by entering in the file
number/name into a list that comes up as a text box for input.

Thanks for the information, if you can help futher that would be
great.

Regards,
Mark

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

--

Dave Peterson