Import multiple text files
 

Hi all,

Working on a macro that will allow me to import 1:M .txt files into a worksheet, one under the other. The macro I have so far allows me to open .txt files. Another macro loops through opening multiple files but they open in separate workbooks.

Here is what I am working with:
// this opens the .txt files
Sub ImportTextFile()

Dim DestBook As Workbook, SourceBook As Workbook
Dim DestCell As Range
Dim RetVal As Boolean

' Turn off screen updating.
Application.ScreenUpdating = False

' Set object variables for the active book and active cell.
Set DestBook = ActiveWorkbook
Set DestCell = ActiveCell

' Show the Open dialog box.
RetVal = Application.Dialogs(xlDialogOpen).Show("*.txt")

' If Retval is false (Open dialog canceled), exit the procedure.
If RetVal = False Then Exit Sub

' Set an object variable for the workbook containing the text file.
Set SourceBook = ActiveWorkbook

' Copy the contents of the entire sheet containing the text file.
Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Copy

' Activate the destination workbook and paste special the values
' from the text file.
DestBook.Activate
DestCell.PasteSpecial Paste:=xlValues

' Close the book containing the text file.
SourceBook.Close False

End Sub


// this loops multiple files
Sub loopyarray()

Dim filenames As Variant

' set the array to a variable and the True is for multi-select
filenames = Application.GetOpenFilename(, , , , True)

counter = 1

' ubound determines how many items in the array
While counter <= UBound(filenames)

'Opens the selected files
Workbooks.Open filenames(counter)

' displays file name in a message box
MsgBox filenames(counter)

'increment counter
counter = counter + 1

Wend
End Sub

Any help is immensely appreciated!
Thanks,

Stanley

Import multiple text files
 

This is, IMO, entirely the wrong approach! I recommend you read each
file into an array and 'dump' the array into the worksheet at the
desired location, using the standard VB[A] file I/O and other built-in
functions.

Of course, you also need to split the file contents into cols based on
the delimiter used when the files were created. (Otherwise, everything
will end up in col A)

You also need to determine if you want to remove trailing blank lines
from the files before dumping their contents into the target sheet.
This will allow better control of spaicing between files on the sheet.

Finally, will you want to put the filename (with or without path) in
the 1st row for each file.

If interested in this approach, please advise your criteria...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion