importing multiple text files into the same worksheet

M

Mike D

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 worksheet.

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
 
D

Dave Peterson

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 Worksheet
Dim newWks As Worksheet
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 = ActiveSheet
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 said:
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 worksheet.

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
Click to expand...
 
B

Bernie Deitrick

Mike,

Try the sub below.

HTH,
Bernie
MS Excel MVP

Sub ConsolidateMultipleUserSelectedTextFiles()
Dim FileArray As Variant
Dim myBook As Workbook
Set myBook = ThisWorkbook

FileArray = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(FileArray) Then
For i = LBound(FileArray) To UBound(FileArray)
Workbooks.OpenText Filename:=FileArray(i), Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
Range("A1").CurrentRegion.Copy myBook.Worksheets(1).Range("a65536").End(xlUp).Offset(1, 0)
ActiveWorkbook.Close False
Next i
End If

myBook.Save
End Sub
 
M

Mike D

Bernie,

Thanks for the quick response. Only checked it this morning. I had
troubles with the sub. I'm very new to macros and VBA.

I managed to assign a macro to the sub and when I run it things look to work
ok. First I get asked which files then it looks to start doing things (even
looks like the text is imported [a flash of text]), but then when finished
there is no text there.


Any thoughts?
 
M

Mike D

Dave,

I think I got this working. Thank you both for your help. This is the
first time I've used this forum and what a result.

Cheers,

Mike
 
Top