Home |
Search |
Today's Posts |
#1
|
|||
|
|||
importing multiple text files into the same worksheet
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 |
#2
|
|||
|
|||
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 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 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 -- Dave Peterson |
#3
|
|||
|
|||
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).Off set(1, 0) ActiveWorkbook.Close False Next i End If myBook.Save End Sub "Mike D" <Mike wrote in message ... 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 |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I import several text files into excel or access ? | Excel Discussion (Misc queries) | |||
Help importing text files into individual cells | Excel Discussion (Misc queries) | |||
extract text from html files | Excel Discussion (Misc queries) | |||
Importing Text Files | Excel Discussion (Misc queries) | |||
Importing text files into Excel | Excel Discussion (Misc queries) |