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