Hi Dan

I will post a example today that loop through all files in the folder and
insert a sheet each time and import the data.




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Dr Dan" wrote in message ...
Hi Ron,

Thanks for the link. That looks like I can use it to insert the txt files
where I want them. Do I need to open a new worksheet manually then start the
macro to specify the txt file to import or is it possible to automate the
process so that one macro creates a new worksheet, then calls up the second
to import the txt file, then opens up a new worksheet and again prompts for a
file name to import?

Cheers,

Dan

"Ron de Bruin" wrote:

Hi Dr Dan

No if you want to have each txt file in a seperate worksheet then this is not working

You want every txt file in a seperate sheet in your workbook.
Am I correct ?

See also this page
http://www.cpearson.com/excel/imptext.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Dr Dan" <Dr wrote in message ...
Hi All,

I'm trying desperately to find a way to automatically import many space
delimited .txt files (all contained in a single folder) so that each .txt
file is put into its own worksheet, named according to the original .txt
filename.

Is this possible?

I found an excellent macro written by Ron de Bruin which I modified as
suggested in his post to work for importing many txt files into a single
worksheet. I have copied this below. From what I can work out (I know nothing
about Visual Basic) it operates by combining all the txt files into a single
txt file and then importing that into excel. I guess that approach is not
adaptable to what I need.

Ideally, I need a macro that will allow me to browse to the correct folder,
then individually import each txt file into a worksheet then move to the next
txt file and create a new worksheet for it to go in to and then keep going
like this until all files have been converted.

As I mentioned, I don't understand the codes but can install them into Excel
OK with a little help as Ron de Bruin included in his post.

Cheers for any assistance,

Dan

Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, _
lpExitCode As Long) As Long

Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103



Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
Dim hProg As Long
Dim hProcess As Long, ExitCode As Long
'fill in the missing parameter and execute the program
If IsMissing(WindowState) Then WindowState = 1
hProg = Shell(PathName, WindowState)
'hProg is a "process ID under Win32. To get the process handle:
hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
Do
'populate Exitcode variable
GetExitCodeProcess hProcess, ExitCode
DoEvents
Loop While ExitCode = STILL_ACTIVE
End Sub


Sub Merge_txt_Files()
Dim BatFileName As String
Dim TXTFileName As String
Dim XLSFileName As String
Dim DefPath As String
Dim Wb As Workbook
Dim oApp As Object
Dim oFolder
Dim foldername

'Create two temporary file names
BatFileName = Environ("Temp") & "\CollecttxtData" & Format(Now,

"dd-mm-yy-h-mm-ss") & ".bat"
TXTFileName = Environ("Temp") & "\Alltxt" & Format(Now,

"dd-mm-yy-h-mm-ss") & ".txt"

' Create path to xls file
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) < "\" Then
DefPath = DefPath & "\"
End If
XLSFileName = DefPath & "Mastertxt " & Format(Now, "dd-mm-yy h-mm-ss")

& ".xls"

'Browse to the folder with txt files
Set oApp = CreateObject("Shell.Application")
Set oFolder = oApp.BrowseForFolder(0, "Select folder with txt files",

512)
If Not oFolder Is Nothing Then
foldername = oFolder.Self.Path
If Right(foldername, 1) < "\" Then
foldername = foldername & "\"
End If

'Create the bat file
Open BatFileName For Output As #1
Print #1, "Copy " & Chr(34) & foldername & "*.txt" & Chr(34) & " "

& TXTFileName
Close #1

'Run the Bat file to collect all data from the txt files into a TXT

file
ShellAndWait BatFileName, 0
If Dir(TXTFileName) = "" Then
MsgBox "There are no txt files in this folder"
Kill BatFileName
Exit Sub
End If

'Open the TXT file in Excel
Application.ScreenUpdating = False
Workbooks.OpenText Filename:=TXTFileName, Origin:=xlWindows,

StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,

Comma:=False, _
Space:=True, Other:=False

'Save text file as a XLS file
Set Wb = ActiveWorkbook
Application.DisplayAlerts = False
Wb.SaveAs Filename:=XLSFileName, FileFormat:=xlWorkbookNormal
Application.DisplayAlerts = True

Wb.Close savechanges:=False
MsgBox "You find the XLS file he " & XLSFileName

'Delete the bat and text file you have create
Kill BatFileName
Kill TXTFileName

Application.ScreenUpdating = True
End If
End Sub