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

Help: Import text transpose append to new worksheet
 

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

Help: Import text transpose append to new worksheet
 

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

Help: Import text transpose append to new worksheet
 

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

Help: Import text transpose append to new worksheet
 

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