Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import data from multiple txt files into one xls file
Hi all. I have to import about 40 text files into excel. And I have
to drop the data into a single worksheet, one entry under the other. Is there a way to select the txt files from a single folder and import into excel dropping one under the other, so I have one giant data column? The data in the txt file is a simple string...no comma or tab delimited. So the data can simply be dropped in column A. Thanks in advance!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import data from multiple txt files into one xls file
Certainly,
http://somethinglikeant.110mb.com//e...ile_Import.xls Just tried this and it worked a treat. I had 20 text files setup in test. ------------------------------------------------------------- Sub Import_Multiple_Text_Files() Dim FileS As FileSearch Dim F As Variant Dim x As Integer 'switch calculation off to speed up macro Application.Calculation = xlManual 'Pick up file path information qfolder = [B5] Set FileS = Application.FileSearch With FileS .NewSearch .Filename = "*" .LookIn = qfolder .SearchSubFolders = True .Execute End With x = 1 Sheets("Data").Select For Each F In Application.FileSearch.FoundFiles Open F For Input Access Read As #1 Do Until EOF(1) Line Input #1, qdata If qdata < "" Then Cells(x, 1) = qdata x = x + 1 End If Loop Close #1 Next F End Sub -------------------------------------------------------------------------------- You will have to setup your folder by typing it out int the Macro tab. Let me know if you need any further tweaks or assistance. http://www.excel-ant.co.uk |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import data from multiple txt files into one xls file
Here is code that doesn't use filesearch.
Sub Import_Multiple_Text_Files() Dim F As Variant Dim x As Integer Const MyPath = "c:\temp\" first = True RowCount = 1 Do If first = True Then Filename = Dir(MyPath & "*.txt") first = False Else Filename = Dir() End If If Filename < "" Then Open (MyPath & Filename) For Input Access Read As #1 Do Until EOF(1) Line Input #1, qdata If qdata < "" Then Cells(RowCount, 1) = qdata RowCount = RowCount + 1 End If Loop Close #1 End If Loop While Filename < "" End Sub "excel-ant" wrote: Certainly, http://somethinglikeant.110mb.com//e...ile_Import.xls Just tried this and it worked a treat. I had 20 text files setup in test. ------------------------------------------------------------- Sub Import_Multiple_Text_Files() Dim FileS As FileSearch Dim F As Variant Dim x As Integer 'switch calculation off to speed up macro Application.Calculation = xlManual 'Pick up file path information qfolder = [B5] Set FileS = Application.FileSearch With FileS .NewSearch .Filename = "*" .LookIn = qfolder .SearchSubFolders = True .Execute End With x = 1 Sheets("Data").Select For Each F In Application.FileSearch.FoundFiles Open F For Input Access Read As #1 Do Until EOF(1) Line Input #1, qdata If qdata < "" Then Cells(x, 1) = qdata x = x + 1 End If Loop Close #1 Next F End Sub -------------------------------------------------------------------------------- You will have to setup your folder by typing it out int the Macro tab. Let me know if you need any further tweaks or assistance. http://www.excel-ant.co.uk |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import data from multiple txt files into one xls file
See also
http://www.rondebruin.nl/csv.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Steve" wrote in message ups.com... Hi all. I have to import about 40 text files into excel. And I have to drop the data into a single worksheet, one entry under the other. Is there a way to select the txt files from a single folder and import into excel dropping one under the other, so I have one giant data column? The data in the txt file is a simple string...no comma or tab delimited. So the data can simply be dropped in column A. Thanks in advance!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import data from several excel files into one file | Excel Discussion (Misc queries) | |||
Extracting data from 1 file to multiple files | Excel Programming | |||
Import data from multiple excel files | Excel Programming | |||
Excel VBA - Import Data for manipulation from multiple text files | Excel Programming | |||
Import multiple files macro can't find files | Excel Programming |