Can you use this?
Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String
FName = Application.GetOpenFilename _
(filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If
Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(FName), Sep
End Sub
Public Sub ImportTextFile(FName As String, Sep As String)
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
Is it dynamic, not static. I will give you a lot more control of your
import process (you search for the file that you want to import, you select
the method of delimitation, etc.)
Regards,
Ryan---
--
RyGuy
"Novice Lee" wrote:
I guess you can call it plain text ( I can open it in Notepad). When I select
the file the text import wizard pops up, I hit next twice then finish. and
there the file is. Each piece of data in it's own cell.
I was also thinking about get rid of < in alot of the cells, but that for
another day
Thanks
"Rick Rothstein (MVP - VB)" wrote:
What kind of text file are we talking about... plain text, CSV file,
something else?
How or where did you want to open it... in a TextBox, MessageBox, single
cell, multiple cells, etc.?
Rick
"Novice Lee" wrote in message
...
Is there a way to create a button that open a text file with the same name
all the time and bypass the additional dialog boxes
Thanks