Hi Dan

I reply this evening, I see it in my Inbox

--

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


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

You have mail.

"Ron de Bruin" wrote:

Hi Dan

Can you send me a few txt files private then I look at it for you tomorrow

--

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


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

;) That explains why I was struggling to get it to call the 2nd macro! it's
looking good though.

It's almost there. It finds the txt files fine and it will happily create
multiple individual worksheets for each txt file, named correctly. The
problem is there is no data in any of them. Only the starting blank
worksheet, 'sheet 1', contains imported data, but correctly formatted though.
looks like the data from the last txt file (or first if the routine starts
from bottom up)

Any idea why the data is not appearing. I am opening Xcel, new book. Hitting
Alt F11, copying in the macro, Alt Q out to Xcel again, Alt F8 followed by
run.

Cheers,

Dan

"Ron de Bruin" wrote:

Oops

Sorry I forgot to add the path in this line

Use this

' Call Chip Pearson's macro
ImportTextFile MyPath & MyFiles(Fnum), " "


--

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


"Ron de Bruin" wrote in message ...
OK

Test this one Dan

Change the folder to your folder
MyPath = "C:\Users\Ron\test"



Sub Example2()
Dim MyPath As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim Fnum As Long
Dim mysheet As Worksheet
Dim basebook As Workbook

'Fill in the path\folder where the files are
MyPath = "C:\Users\Ron\test"

'Add a slash at the end if the user forget it
If Right(MyPath, 1) < "\" Then
MyPath = MyPath & "\"
End If

'If there are no txt files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.txt")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

On Error GoTo CleanUp
Application.ScreenUpdating = False
Set basebook = ThisWorkbook

'Fill the array(myFiles)with the list of txt files in the folder
Fnum = 0
Do While FilesInPath < ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop

'Loop through all files in the array(myFiles)
If Fnum 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mysheet = Worksheets.Add
mysheet.Name = MyFiles(Fnum)

' Call Chip Pearson's macro
ImportTextFile MyFiles(Fnum), " "

Next Fnum
End If
CleanUp:
Application.ScreenUpdating = True
End Sub


Public Sub ImportTextFile(FName As String, Sep As String)
'http://www.cpearson.com/excel/imptext.htm
Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

--

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


"Dr Dan" wrote in message ...
Thanks Ron, that sounds excellent. I look forward to it.

Cheers,

Dan

"Ron de Bruin" wrote:

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")