Re: importing multiple text files into the same worksheet

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I recorded a macro and tweaked it just a bit to ask for multiple files (click on
the first and ctrl-click on subsequent).

It looks like this when I'm done.

Option Explicit
Sub testme()

Dim myFileNames As Variant
Dim iCtr As Long
Dim wks As Work***
Dim newWks As Work***
Dim DestCell As Range

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

If IsArray(myFileNames) Then

Set newWks = Workbooks.Add(1).Worksheets(1)
Set DestCell = newWks.Range("a1")

For iCtr = LBound(myFileNames) To UBound(myFileNames)
Workbooks.OpenText Filename:=myFileNames(iCtr), _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _
Tab:=False, Semicolon:=False, Comma:=False, Space:=True, _
Other:=False, FieldInfo:=Array(1, 1)

Set wks = Active***
wks.UsedRange.Copy _
Destination:=DestCell

wks.Parent.Close savechanges:=False

With newWks
Set DestCell _
= .Cells(.Cells.SpecialCells(xlCellTypeLastCell).Row + 1, "A")
End With

Next iCtr
End If

End Sub

So you'll want to record a macro one time to get this portion correct:
Workbooks.OpenText Filename:=myFileNames(iCtr), _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _
Tab:=False, Semicolon:=False, Comma:=False, Space:=True, _
Other:=False, FieldInfo:=Array(1, 1)

Keep the:
Workbooks.OpenText Filename:=myFileNames(iCtr),
portion and use your recorded code for everything else:

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Mike D wrote:
>
> Hello,
>
> I would like to run a macro which will firstly open a file selection box
> (like the one that appears afte [file], [open]), let me choose a number of
> files, then each one would be appended to the same work***.
>
> The files are text and delimited by a space.
>
> The standard import wizards does well, but there are tons of files I need to
> import.
>
> Any help would be much appreciated.
>
> Regards,
>
> Mike

--

Dave Peterson
.

Quantcast