Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Automate Text Import for Multiple Files | Excel Programming | |||
Import multiple text files (Macro) | Excel Discussion (Misc queries) | |||
Import of Multiple Text Files | Excel Programming | |||
Import multiple text files into a single worksheet | Excel Discussion (Misc queries) | |||
Import multiple text files into excel | Excel Programming |