Opening a txt file
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 |
Opening a txt file
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 |
Opening a txt file
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 |
Opening a txt file
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 |
Opening a txt file
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 |
All times are GMT +1. The time now is 03:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com