software

Dear all,

I need to import 100 TXT files into a single excel file for my Ph. D.
Dissertation.
All the files have the same structu only 1 column with 60 rows each,
like this:

01 - I
02 - C
03 - C
60 - I

I'd need to end up with an excel file that included 250 columns, one
for each file, and the name of the corresponding file on top of each
column, like this:

File1 File2 File100
01 - I 01 - C 01 - C
02 - C 02 - I 02 - I
03 - C 03 - C 03 - I
60 - I 60 - C 60 - I

I have little Excel experience, but I know how to paste code into a
module in the Visual Basic Editor. Please find below the code I get if
I import 1 single file into my Excel Workbook, in case it helps.

YOUR HELP WILL BE GREATLY APPRECIATED!!!

THANK YOU!!!!!!!!!!!!!!!!!!!!! ;-D

Luis

*** CODE AFTER IMPORTING 1 TXT FILE ***

Sub importTextFile()
'
' importTextFile Macro
' Macro recorded 08/04/2006 by luis cerezo ceballos
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
quot;TEXT;C:\aaqpresults\cerezo-int2-dah38-exp-rel.txtquot;,
Destination:=Range(quot;A1quot;) _
)
.Name = quot;cerezo-int2-dah38-exp-relquot;
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End SubYou could use File|open to import the text file and then copy to its new
postion. The plop that code into a loop.

If all the text files were in one dedicated folder (so no extra files are
processed), it might even work ok:

Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim DestCell As Range
Dim NewWks As Worksheet
Dim wks As Worksheet

'change to point at the folder to check
myPath = quot;c:\my documents\excel\textfilesquot;
If Right(myPath, 1) lt;gt; quot;\quot; Then
myPath = myPath amp; quot;\quot;
End If

myFile = quot;quot;
On Error Resume Next
myFile = Dir(myPath amp; quot;*.txtquot;)
On Error GoTo 0
If myFile = quot;quot; Then
MsgBox quot;no files foundquot;
Exit Sub
End If

Application.ScreenUpdating = False

'get the list of files
fCtr = 0
Do While myFile lt;gt; quot;quot;
fCtr = fCtr 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
myFile = Dir()
Loop

If fCtr gt; 0 Then

Set NewWks = Workbooks.Add(1).Worksheets(1)
Set DestCell = NewWks.Range(quot;a1quot;)

For fCtr = LBound(myNames) To UBound(myNames)

Application.StatusBar _
= quot;Processing: quot; amp; myNames(fCtr) amp; quot; at: quot; amp; Now

Workbooks.OpenText Filename:=myPath amp; myNames(fCtr), _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(1, 2)

Set wks = ActiveSheet
DestCell.Value = quot;'quot; amp; myNames(fCtr)
wks.Columns(1).Copy _
Destination:=DestCell.Offset(0, 1)
wks.Parent.Close savechanges:=False

Set DestCell = DestCell.Offset(0, 1)

Next fCtr
End If

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

End Subluis wrote:
gt;
gt; Dear all,
gt;
gt; I need to import 100 TXT files into a single excel file for my Ph. D.
gt; Dissertation.
gt; All the files have the same structu only 1 column with 60 rows each,
gt; like this:
gt;
gt; 01 - I
gt; 02 - C
gt; 03 - C
gt; 60 - I
gt;
gt; I'd need to end up with an excel file that included 250 columns, one
gt; for each file, and the name of the corresponding file on top of each
gt; column, like this:
gt;
gt; File1 File2 File100
gt; 01 - I 01 - C 01 - C
gt; 02 - C 02 - I 02 - I
gt; 03 - C 03 - C 03 - I
gt; 60 - I 60 - C 60 - I
gt;
gt; I have little Excel experience, but I know how to paste code into a
gt; module in the Visual Basic Editor. Please find below the code I get if
gt; I import 1 single file into my Excel Workbook, in case it helps.
gt;
gt; YOUR HELP WILL BE GREATLY APPRECIATED!!!
gt;
gt; THANK YOU!!!!!!!!!!!!!!!!!!!!! ;-D
gt;
gt; Luis
gt;
gt; *** CODE AFTER IMPORTING 1 TXT FILE ***
gt;
gt; Sub importTextFile()
gt; '
gt; ' importTextFile Macro
gt; ' Macro recorded 08/04/2006 by luis cerezo ceballos
gt; '
gt;
gt; '
gt; With ActiveSheet.QueryTables.Add(Connection:= _
gt; quot;TEXT;C:\aaqpresults\cerezo-int2-dah38-exp-rel.txtquot;,
gt; Destination:=Range(quot;A1quot;) _
gt; )
gt; .Name = quot;cerezo-int2-dah38-exp-relquot;
gt; .FieldNames = True
gt; .RowNumbers = False
gt; .FillAdjacentFormulas = False
gt; .PreserveFormatting = True
gt; .RefreshOnFileOpen = False
gt; .RefreshStyle = xlInsertDeleteCells
gt; .SavePassword = False
gt; .SaveData = True
gt; .AdjustColumnWidth = True
gt; .RefreshPeriod = 0
gt; .TextFilePromptOnRefresh = False
gt; .TextFilePlatform = 437
gt; .TextFileStartRow = 1
gt; .TextFileParseType = xlDelimited
gt; .TextFileTextQualifier = xlTextQualifierDoubleQuote
gt; .TextFileConsecutiveDelimiter = False
gt; .TextFileTabDelimiter = True
gt; .TextFileSemicolonDelimiter = False
gt; .TextFileCommaDelimiter = False
gt; .TextFileSpaceDelimiter = False
gt; .TextFileColumnDataTypes = Array(1)
gt; .TextFileTrailingMinusNumbers = True
gt; .Refresh BackgroundQuery:=False
gt; End With
gt; End Sub

--

Dave Peterson

Hi Dave,

Thank you very much for your reply. I copied your code into a module in
VisualBasic and changed the folder to check. This is what happens when
I run the macro:

1) It copies the data of the first file of the folder appropriately
into the first column of the active worksheet.
2) It appropriately labels the tab with the name of that file.
3) It opens a new worksheet with the name of the file only.

But it stops there. Any ideas?

Thank you very much,

Luis#1. How many files with the extension of .txt are in that folder?

#2. I don't see how the code I posted names the worksheet.

#3. I don't understand what this means.

luis wrote:
gt;
gt; Hi Dave,
gt;
gt; Thank you very much for your reply. I copied your code into a module in
gt; VisualBasic and changed the folder to check. This is what happens when
gt; I run the macro:
gt;
gt; 1) It copies the data of the first file of the folder appropriately
gt; into the first column of the active worksheet.
gt; 2) It appropriately labels the tab with the name of that file.
gt; 3) It opens a new worksheet with the name of the file only.
gt;
gt; But it stops there. Any ideas?
gt;
gt; Thank you very much,
gt;
gt; Luis

--

Dave Peterson

I see you have 3 threads asking the same question.

I'll bow out of the conversation.
luis wrote:
gt;
gt; Hi Dave,
gt;
gt; Thank you very much for your reply. I copied your code into a module in
gt; VisualBasic and changed the folder to check. This is what happens when
gt; I run the macro:
gt;
gt; 1) It copies the data of the first file of the folder appropriately
gt; into the first column of the active worksheet.
gt; 2) It appropriately labels the tab with the name of that file.
gt; 3) It opens a new worksheet with the name of the file only.
gt;
gt; But it stops there. Any ideas?
gt;
gt; Thank you very much,
gt;
gt; Luis

--

Dave Peterson

Hi Dave,

I'm pretty new to forums and I re-posted my initial message because I
had I typo in my message (250 instead of 100 files). I appreciate your
help very much.

Have a nice day,

Luis.Hi again Dave,

I just wanted to thank you again for your interest, and let you know
that someone already sent me a macro that does exactly what I wanted,
so I am really happy with this new forum experience.

Best,

Luis