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

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

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

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

Thanks for the code, I tried the code. What do I put for the delimiter? I
used "," and it put everything from line one of the text file into cell a1,
next line into b1 and so on. am I doing something wrong?
thanks

"ryguy7272" wrote:

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