Batch importing

L

lazy importer

I have a large number of text files, each with two columns and about 2000
rows. I would like to put these into a spreadsheet without having to open
each individually, select all, copy and paste into the appropriate square. I
would like to be able to select the files I would like imported, and that all
these are put into the same spreadsheet, one next to the other. Any
suggestions?
 
M

Myrna Larson

You'll have to write a macro to do this. Turn on the macro recorder, open the
1st file, and copy the data into the combined workbook. Then you'll have to
generalize that to get a list of file names and copy the data from each one to
the appropriate place. If you have a specific problem with one of the needed
commands, post back.


On Mon, 15 Nov 2004 10:04:04 -0800, "lazy importer" <lazy
 
L

lazy importer

Hi Myrna, thanks for the advice. I have recorded a macro which when it runs
copies exactly what I had done, i.e. it imports the first text file from the
appropriate folder. However, I do not know how to adjust it so that it will
open the second and third files, etc. Also, I do not know how to get it to
increment the column into which it inserts the data. If you could help me
with this I would be extremely grateful. Thanks a lot,

Lazy
 
M

Myrna Larson

Post the code you have now.

These are the steps to be programmed:

1. Use the Application.GetOpenFileName method to allow the user to select the
list of files. There should be examples in Help.

I hope you are using a version of Excel that allows you to select multiple
items from the single dialog box.

If not, you're in for some "heavy" programming...

2. Once you have the list of files, you
a. open each one in turn as a new file, then
b. copy all of the data from there to the sheet that
is to house all of the data, then
c. close the file and
d. go back to step 2 and open the next file.

You use a variable to keep track of the column number, incrementing it with
each file that you open.
 
M

Myrna Larson

I've modified some code I had on hand. You may need to make more changes,
specifically to the value of the ColumnsPerFile constant, and the file filter
argument of the GetOpenFileName command.

It writes the data from the first file starting at the upper left cell of
whatever the selection is when you run the macro. After that, the next file is
x columns to the right, with x determined by the value of the constant
ColumnsPerFile.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Option Explicit

Sub InsertDataFromTextFiles()
Dim ColumnOffset As Long
Dim DestCell As Range
Dim i As Long
Dim SourceData As Range
Dim FileList As Variant

Const ColumnsPerFile As Long = 13

FileList = Application.GetOpenFilename _
(FileFilter:="All Files(*.*), *.*", _
Title:="Select files", MultiSelect:=True)

'returns an array if at least 1 file is selected
'if user cancelled, returns Boolean = False
If TypeName(FileList) <> "Variant()" Then Exit Sub

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set DestCell = Selection.Range("A1")
ColumnOffset = -ColumnsPerFile 'will increment to 0 on 1st pass

For i = LBound(FileList) To UBound(FileList)
Set SourceData = _
Workbooks.Open(FileName:=FileList(i)).Worksheets(1).UsedRange
SourceData.Copy
ColumnOffset = ColumnOffset + ColumnsPerFile
DestCell.Offset(0, ColumnOffset).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
SourceData.Parent.Parent.Close SAVECHANGES:=False
Next i

DestCell.Activate
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub 'InsertDataFromTextFiles
 
Top