Help: Import text > transpose > append to new worksheet

Help! I've just been given a project with a very short deadline,
because the original folks are no longer involved, and am a newbie at
VBA. Pointers are greatly appreciated.

I have thousands of individual e-mail text files containing survey data
(from an online survey form). I need to pull all of this data into a
spreadsheet so we can do some basic analysis. I can pull the data in
manually, but that will exceed both the timeline and budget for this
project.

I think I need to create a VBA script to import a text file then
transpose the data from a column format to a row format then append
that data to another worksheet containing all the records. Then do that
again with the remaining thousands of files. There may be a cleaner
approach.

Sorry to be a complete idiot at this point -- trying to get my head
wrapped around VBA even as I type.

Thanks! Trish

Assuming the text files are all in a single directory named "C:\MyTextFiles"
and have .txt as an extension (alter to suit):

Sub Processfiles()
Dim sname as String, bk as Workbook
Dim rng1 as Range, rng2 as Range
sname = dir("C:\MyTextfiles\*.txt")
do while sname <> ""
set bk = workbooks.open(sName)
with bk.worksheets(1)
set rng1 = .range(.cells(1,1),.cells(rows.count,1).End(xlup))
end with
set rng2 = Workbooks("MasterList.xls").worksheets(1) _
.Cells(rows.count,1).end(xlup).offset(1,0)
rng1.copy
rng2.pastespecial xlValues, Transpose:=True
bk.close Savechanges:=False
sName = dir
Loop
End Sub

should get you started.

--
Regards,
Tom Ogilvy


"javamom" wrote:

> Help! I've just been given a project with a very short deadline,
> because the original folks are no longer involved, and am a newbie at
> VBA. Pointers are greatly appreciated.
>
> I have thousands of individual e-mail text files containing survey data
> (from an online survey form). I need to pull all of this data into a
> spreadsheet so we can do some basic analysis. I can pull the data in
> manually, but that will exceed both the timeline and budget for this
> project.
>
> I think I need to create a VBA script to import a text file then
> transpose the data from a column format to a row format then append
> that data to another worksheet containing all the records. Then do that
> again with the remaining thousands of files. There may be a cleaner
> approach.
>
> Sorry to be a complete idiot at this point -- trying to get my head
> wrapped around VBA even as I type.
>
> Thanks! Trish
>
>

Thanks Tom! I will try this out and report back. Trish

This seems to be working well. Thanks!

One more question: I need to indicate a semicolon as a delimiter. I've
been looking at examples using sep but am not quite sure how to
incorporate that into the above script.

Thanks again! Trish

My attempt to recognize the semicolon delimiter upon opening the text
files is not going well. I thought I had something close to working,
but apparently not. Here is where I stand now:

Sub Processfiles()
Dim sname As String, bk As Workbook
Dim rng1 As Range, rng2 As Range
sname = Dir("C:\MyTextFiles\*.*")
Do While sname <> ""

Workbooks.OpenText _
Filename:=sname, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:= _
True, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1)

Set bk = ActiveWorkbook

With bk.Worksheets(1)
Set rng1 = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
Set rng2 = Workbooks("MasterList.xls").Worksheets(1) _
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
rng1.Copy
rng2.PasteSpecial xlValues, Transpose:=True
bk.Close Savechanges:=False
sname = Dir
Loop
End Sub

Apologies if I've really butchered this! Trying to understand. Really
fuzzy still on when to use workbooks.open or workbooks.opentext. Any
help is greatly appreciated.

Thanks! Trish