Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import multiple text files into excel
I have a folder located at -- c:\precedence
Inside the folder I have about 10 files named as follows: NC60.txt NC61.txt NC62.txt NC63.txt NC90.txt NC91.txt NC5801.txt NC01.txt NC02.txt L64.txt Each of the files is sent to me by different individuals, hence the different names. Each files contains the same formatting of information. I have created a macro that will let me import NC60.txt into a new sheet in the open workbook. I want to automate the process so that it will go to the folder, import the NC60.txt to a new worksheet, then it will loop through and add NC61.txt to a new worksheet and so on and so forth until it has imported all files. I have a setup file in the worksheet where I list each file name so it might be used as a reference. Any assistance would be appreciated. If it is easier to discuss this or have me send you the text files, you can email me at Walter.(REMOVE THIS) Thanks for a busy soldier in Iraq. Wally Steadman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import multiple text files into excel
Wally,
I used the OpenText method for a tab delimited file. You need to record a macro bringing in one of the files and going through the Text import wizard, then replace my opentext code with that code. If you add more files or change the names, just modify the array. Or if you prefer to pick up the list from a worksheet you can do With Worksheets("List1") varr = .Range(.Range("A1"),.Range("A1").End(xldown)).Valu e End With instead of the varr = Array( . . . ) which I have. Sub AAATest() Dim varr As Variant Dim wkbk1 As Workbook Dim wkbk As Workbook Dim i As Long Dim sh1 As Worksheet Dim sName As String varr = Array("NC60.txt", _ "NC61.txt", _ "NC62.txt", _ "NC63.txt", _ "NC90.txt", _ "NC91.txt", _ "NC5801.txt", _ "NC01.txt", _ "NC02.txt", _ "L64.txt") Set wkbk = ActiveWorkbook For i = LBound(varr) To UBound(varr) Workbooks.OpenText FileName:="C:\precedence\" & varr(i), _ Origin:=xlWindows, _ StartRow:=1, DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, _ Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, _ Other:=False, FieldInfo:= _ Array(Array(1, 1), Array(2, 1), _ Array(3, 1), Array(4, 1)) Set wkbk1 = ActiveWorkbook Set sh1 = wkbk.Worksheets.Add(after:= _ wkbk.Worksheets(wkbk.Worksheets.Count)) wkbk1.Worksheets(1).UsedRange.Copy _ Destination:=sh1.Range("A1") sName = wkbk1.Name sName = Left(sName, Len(sName) - 4) wkbk1.Close SaveChanges:=False sh1.Name = sName Next End Sub -- Regards, Tom Ogilvy Wally Steadman wrote in message ... I have a folder located at -- c:\precedence Inside the folder I have about 10 files named as follows: NC60.txt NC61.txt NC62.txt NC63.txt NC90.txt NC91.txt NC5801.txt NC01.txt NC02.txt L64.txt Each of the files is sent to me by different individuals, hence the different names. Each files contains the same formatting of information. I have created a macro that will let me import NC60.txt into a new sheet in the open workbook. I want to automate the process so that it will go to the folder, import the NC60.txt to a new worksheet, then it will loop through and add NC61.txt to a new worksheet and so on and so forth until it has imported all files. I have a setup file in the worksheet where I list each file name so it might be used as a reference. Any assistance would be appreciated. If it is easier to discuss this or have me send you the text files, you can email me at Walter.(REMOVE THIS) Thanks for a busy soldier in Iraq. Wally Steadman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import text files into EXCEL | Excel Discussion (Misc queries) | |||
Import Multiple XML Files into Excel | Excel Discussion (Misc queries) | |||
import multiple csv files into excel 2007 | Excel Discussion (Misc queries) | |||
Import multiple text files (Macro) | Excel Discussion (Misc queries) | |||
Import multiple text files into a single worksheet | Excel Discussion (Misc queries) |