I'm trying to find a method that would enable me to import the data from
several TXT files into an existing workbook. I'd like for the data from each
file to be imported onto a new worksheet. I'd also like to automate the
naming of the worksheet to the name of the file that the data came from. Does
anyone have any ideas on how this could be accomplished easily?

Test this one tpmax

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 MyPath & 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


"tpmax" wrote in message ...
I'm trying to find a method that would enable me to import the data from
several TXT files into an existing workbook. I'd like for the data from each
file to be imported onto a new worksheet. I'd also like to automate the
naming of the worksheet to the name of the file that the data came from. Does
anyone have any ideas on how this could be accomplished easily?

Brilliant! The first macro works wonderfully - many thanks.

The text import isn't quite as clean though. The data isn't parsing
correctly with a text import. If I save the file as a CSV, the data opens
better. Could the second macro be swapped out easily for a comma-delimited
import?

Thanks!

"Ron de Bruin" wrote:

Test this one tpmax

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 MyPath & 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


"tpmax" wrote in message ...
I'm trying to find a method that would enable me to import the data from
several TXT files into an existing workbook. I'd like for the data from each
file to be imported onto a new worksheet. I'd also like to automate the
naming of the worksheet to the name of the file that the data came from. Does
anyone have any ideas on how this could be accomplished easily?

Brilliant! The first macro works wonderfully
The first macro call the second macro so you always run the first


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

It use a space now " "

You can change that

See Cip's site for more information
http://www.cpearson.com/excel/imptext.htm



--

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


"tpmax" wrote in message ...
Brilliant! The first macro works wonderfully - many thanks.

The text import isn't quite as clean though. The data isn't parsing
correctly with a text import. If I save the file as a CSV, the data opens
better. Could the second macro be swapped out easily for a comma-delimited
import?

Thanks!

"Ron de Bruin" wrote:

Test this one tpmax

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 MyPath & 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


"tpmax" wrote in message ...
I'm trying to find a method that would enable me to import the data from
several TXT files into an existing workbook. I'd like for the data from each
file to be imported onto a new worksheet. I'd also like to automate the
naming of the worksheet to the name of the file that the data came from. Does
anyone have any ideas on how this could be accomplished easily?

I have a similar question.

Can this be done for excel files instead of txt?

I need to import an excel file from a different directory and to paste to an
existing workbook sheet. Overiding all the old data.

So data to be copied would be in C:\reviveip\AS400 Downloads\orders.xls

There will be other excell files in this folder (AS Downloads) which I do
not want to use which are excel so needs to be file specific.

Then the destination file would be I:\NCR\Summary\Data.xl\sheet1

I want to specify which sheet within this file is the data copied to

Any help would be appreciated as always.

Thanks


"Ron de Bruin" wrote:

Test this one tpmax

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 MyPath & 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


"tpmax" wrote in message ...
I'm trying to find a method that would enable me to import the data from
several TXT files into an existing workbook. I'd like for the data from each
file to be imported onto a new worksheet. I'd also like to automate the
naming of the worksheet to the name of the file that the data came from. Does
anyone have any ideas on how this could be accomplished easily?

Sorry just to be clearer.

I want to run this macro within the file that the data is being copied to.

Thanks

"winnie123" wrote:

I have a similar question.

Can this be done for excel files instead of txt?

I need to import an excel file from a different directory and to paste to an
existing workbook sheet. Overiding all the old data.

So data to be copied would be in C:\reviveip\AS400 Downloads\orders.xls

There will be other excell files in this folder (AS Downloads) which I do
not want to use which are excel so needs to be file specific.

Then the destination file would be I:\NCR\Summary\Data.xl\sheet1

I want to specify which sheet within this file is the data copied to

Any help would be appreciated as always.

Thanks


"Ron de Bruin" wrote:

Test this one tpmax

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 MyPath & 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


"tpmax" wrote in message ...
I'm trying to find a method that would enable me to import the data from
several TXT files into an existing workbook. I'd like for the data from each
file to be imported onto a new worksheet. I'd also like to automate the
naming of the worksheet to the name of the file that the data came from. Does
anyone have any ideas on how this could be accomplished easily?

I think that if I were doing this, I'd want the user to select the worksheet
that gets the data.

In fact, if the range that the data is pasted is a user choice, I'd want the
user to select the topleft cell of that range.

I could even add a check at the top of the code that allows the user to cancel
if he or she is not in the correct location.

dim Resp as long
resp = msgbox(Prompt:="Data will be pasted into the activecell. Continue?", _
buttons:=xlyesno)

if resp = xlno then
exit sub
end if

=========
But for the rest of the code, I'd start by recording a macro when I did it
manually.

winnie123 wrote:

Sorry just to be clearer.

I want to run this macro within the file that the data is being copied to.

Thanks

"winnie123" wrote:

I have a similar question.

Can this be done for excel files instead of txt?

I need to import an excel file from a different directory and to paste to an
existing workbook sheet. Overiding all the old data.

So data to be copied would be in C:\reviveip\AS400 Downloads\orders.xls

There will be other excell files in this folder (AS Downloads) which I do
not want to use which are excel so needs to be file specific.

Then the destination file would be I:\NCR\Summary\Data.xl\sheet1

I want to specify which sheet within this file is the data copied to

Any help would be appreciated as always.

Thanks


"Ron de Bruin" wrote:

Test this one tpmax

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 MyPath & 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


"tpmax" wrote in message ...
I'm trying to find a method that would enable me to import the data from
several TXT files into an existing workbook. I'd like for the data from each
file to be imported onto a new worksheet. I'd also like to automate the
naming of the worksheet to the name of the file that the data came from. Does
anyone have any ideas on how this could be accomplished easily?


--

Dave Peterson

Thanks Dave,

I will try your sugestion.



"Dave Peterson" wrote:

I think that if I were doing this, I'd want the user to select the worksheet
that gets the data.

In fact, if the range that the data is pasted is a user choice, I'd want the
user to select the topleft cell of that range.

I could even add a check at the top of the code that allows the user to cancel
if he or she is not in the correct location.

dim Resp as long
resp = msgbox(Prompt:="Data will be pasted into the activecell. Continue?", _
buttons:=xlyesno)

if resp = xlno then
exit sub
end if

=========
But for the rest of the code, I'd start by recording a macro when I did it
manually.

winnie123 wrote:

Sorry just to be clearer.

I want to run this macro within the file that the data is being copied to.

Thanks

"winnie123" wrote:

I have a similar question.

Can this be done for excel files instead of txt?

I need to import an excel file from a different directory and to paste to an
existing workbook sheet. Overiding all the old data.

So data to be copied would be in C:\reviveip\AS400 Downloads\orders.xls

There will be other excell files in this folder (AS Downloads) which I do
not want to use which are excel so needs to be file specific.

Then the destination file would be I:\NCR\Summary\Data.xl\sheet1

I want to specify which sheet within this file is the data copied to

Any help would be appreciated as always.

Thanks


"Ron de Bruin" wrote:

Test this one tpmax

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 MyPath & 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


"tpmax" wrote in message ...
I'm trying to find a method that would enable me to import the data from
several TXT files into an existing workbook. I'd like for the data from each
file to be imported onto a new worksheet. I'd also like to automate the
naming of the worksheet to the name of the file that the data came from. Does
anyone have any ideas on how this could be accomplished easily?


--

Dave Peterson